account.importRelationalTableData

‹ BACK TO API PAGE


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 items 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

<methodCall>

<methodName>account.importrelationaltabledata</methodName>

<table_name>full_contacts</table_name>

<delimiter>,</delimiter>

<filename>side_table_test_data.txt</filename>

<ftp_server>ftp.example.com</ftp_server>

<ftp_user_name>ftp_user</ftp_user_name>

<ftp_user_pass>ftp_pass</ftp_user_pass>

<reply_email>jsmith@example.com</reply_email>

</methodCall>

Example Response

<methodResponse>

<item>

<methodName><![CDATA[account.importrelationaltabledata]]></methodName>

<responseCode><![CDATA[641]]></responseCode>

<responseText><![CDATA[Table data successfully imported]]></responseText>

<responseData>

<table_name><![CDATA[full_contacts_test]]></table_name>

<reply_email><![CDATA[arajguru@digitalriver.com]]></reply_email>

<error_rows><![CDATA[0]]></error_rows>

</responseData>

<responseNum><![CDATA[1]]></responseNum>

</item>

</methodResponse>

Example Error

<methodResponse>

<item>

<methodName><![CDATA[account.importrelationaltabledata]]></methodName>

<responseCode><![CDATA[651]]></responseCode>

<responseText><![CDATA[Action not taken, table_name does not exist]]></responseText>

<responseData>

<table_name><![CDATA[full_contacts]]></table_name>

<reply_email><![CDATA[jsmith@example.com]]></reply_email>

<error_rows></error_rows>

</responseData>

<responseNum><![CDATA[1]]></responseNum>

</item>

</methodResponse>


Back to API Reference Guide