account.importRelationalTableData

Description

Use this method to upload records into the relational tables that have been created using the account.addRelationalTable method. You can also specify an email address that will receive the system notification either when the data file has been loaded into the relational table or when the upload is aborted due to error.

This method references an ASCII text file that contains the actual records to be loaded into the relational table. The only allowable file extensions include .txt., .csv., .tab., .zip, and .gzip.

The data file must have a header row and must use the same delimiter as the one specified in <delimiter> element of this method call. Each column name specified in the header row must be identical to the one passed in the <column_name> element of the account.addRelationalTable method call when the table was initially created. If a column name in the header row is not recognized, then the system will ignore that field when loading the data into the relational table.

Note also that regardless of what display format you choose for the date field in your relational table, the actual date value included in the uploaded data file must be formatted as YYYY-MM-DD.

Some additional itEmpower to note include:

1. When data is loaded, all existing data in the relational table will be deleted prior to loading in the new data.
2. Up to a maximum of 500,000 records can be inserted into a single relational table.
3. When a record does not have a value for a String column, two double quotes should be entered.
4. When a record does not have a value for a Number column, a zero should be entered.

Arguments

Field	Description	Field Type	Required	Example
table_name	The programmatic name of the table that the records from the data file will be loaded into. The table name must be same one used when the relational table was initially created using the account.addRelationalTable method call.	String	Yes	concert_info
delimiter	An ASCII text character indicating the delimiter used by the data file containing the records to be loaded into the relational table. Two additional special values are permitted: csv – indicates that the file will be comma-delimited (although passing a comma character is also valid for CSV-formatted files tab – indicates that the file is tab-delimited.	String	Yes	,
compression	Only include this element if the data file uses compression. The valid compression types are: zip gzip	String	No	zip
data_url	The fully qualified URL of the data source. Include this element only if you are using HTTP or HTTPS as the transfer protocol. The only allowable file extensions of the data file include .txt., .csv., .tab, .zip, and .gzip.	String	Conditional; Required only if you are using http(s) as the transfer protocol.	https://www.example.com/data/myFileName.txt
ignore_error_rows	This boolean value allows the entire file to process, even if there are rows that generate errors (e.g. improperly formatted data, invalid data type, etc.) where: 0 – (default) Do not load the data file into the relational table and send error response message. 1 – Ignore the row and proceed to load the data file into the relational table. In the response message, include the number of skipped rows.	Number	No	1
ftp_server	IP address or host name of the FTP server hosting the data file.	String	Conditional. Required only if you are using FTP as the transfer protocol.	ftp.example.com
ftp_user_name	User name of the FTP site	String	Conditional. Required only if you are using FTP as the transfer protocol.	ftp_user
ftp_user_pass	Password of the FTP site	String	Conditional. Required only if you are using FTP as the transfer protocol.	ftp_pass
sftp	Indicator of whether connection is via the sFTP protocol where: 0 – (default) do not connect via sFTP 1 – connect via sFTP	boolean	No	1
ftp_ssl	Indicator of whether FTP with a valid SSL certificate should be applied where: 0 – default) do not apply SSL certificate 1 – apply SSL certificate NOTE: if both <sftp> and <ftp_ssl> are set to "1", then the connection will be via sFTP protocol.	boolean	No	1
filename	The name of the data file on the FTP server. The file name can also include the FTP directory path to the file. The only allowable file extensions of the data file include .txt., .csv., .tab, .zip, and .gzip.	String	Conditional. Required only if you are using FTP as the transfer protocol.	datafile.txt or /path/to_file/datafile.txt
reply_email	An email address that will receive the system notification when the data load has either completed or aborted due to error.	String	Yes	jsmith@example.com

Response

Field	Description	Example
table_name	The name of the relational table just updated.	concert_info
reply_email	The email address receiving the notification email.	jsmith@example.com
error_rows	The number of records skipped in the data file due to error. This element is only included in the response message when the <ignore_error_rows> element is set to '1'.	13

Response Codes

Error Condition	responseCode	responseText
Successful data file upload	641	Table data successfully imported
One or more records in the data file contained fields where the value did not match the data type of the column or that the value was improperly formatted	642	Action not taken, improperly formatted data
The <delimiter> element specified an invalid delimiter or the delimiter of the data file does not match the one specified.	643	Action not taken, invalid delimiter
The <compression> element specified an invalid compression or the one used on the data file does not match the one specified.	644	Action not taken, invalid compression
If HTTP(s) is the transfer protocol, then the fully qualified URL is missing. If FTP is the transfer protocol, then the host name or IP address of the FTP server is missing.	645	Action not taken, data_url or ftp_server is required
Both elements were included in the parameters. To eliminate ambiguity, the parameters needs to include one or the other.	646	Action not taken, cannot pass both data_url and ftp_server
FTP is the transfer protocol and the user name and/or password are missing	647	Action not taken, FTP user name and password is required
FTP is the transfer protocol and the filename is missing	648	Action not taken, filename is required
The file extension of the data file is not supported.	649	Action not taken, invalid file type
The name of the relational table is missing or the name provided in the parameters does not match with the one in the database.	651	Action not taken, table_name does not exist
One or more column headers provided in the data file does not match the column name of the relational table in the database.	652	Action not taken, invalid column specified in import file
A generic error returned in the extremely rare case that a system-level error exists at the time of the data load attempt.	653	Action not taken, database exception
There was an error in retrieving the data file from either the web or the ftp server. This error could arise for any number of reasons including invalid URL or FTP server, invalid FTP username and/or password, the host web server or FTP server are unavailable, etc.	654	Action not taken, data file was not copied
The data file is missing records	655	Action not taken, data file was empty
The data file contains more than the maximum 500,000 records.	656	Action not taken, number of records exceeds 500,000
This error message is returned only when the <sftp> option is set to "1". If the sFTP connection fails, the system automatically checks to see whether the <ftp_server> value has a valid FTP connection. This error code occurs if the FTP (non-SFTP) connection succeeds.	657	ftp_server provided has a valid FTP connection, but no sFTP connection
This error message is returned only when the <sftp> option is set to "1". If the sFTP connection fails, the system automatically checks to see whether the <ftp_server> value has a valid FTP connection. This error code occurs if the FTP (non-SFTP) connection also fails.	658	login attempt to sFTP failed

Example Post

Example Response

Example Error

---