Contents x
      account.importRelationalTableData
      • 6 Minutes to read
      • Print
      • Dark
        Light
      Contents

      account.importRelationalTableData

      • 6 Minutes to read
      • Print
      • Dark
        Light
      Article summary

      General Information

      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 an 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 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 the 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 an 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 hostname 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

      The password of the FTP site

      String

      Conditional. Required only if you are using FTP as the transfer protocol.

      ftp_pass

      sftp

      Indicator of whether the 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 been completed or aborted due to an 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 an 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 where 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 hostname 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 need 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 username and password is required

      FTP is the transfer protocol, and the filename is missing

      648

      Action not taken, the 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 the one in the database.

      651

      Action not taken, table_name does not exist

      One or more column headers provided in the data file do not match the column name of the relational table in the database.

      652

      Action not taken, an invalid column specified in the import file

      A generic error is 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 an invalid URL or FTP server, an invalid FTP username and/or password, the host web server or FTP server being unavailable, etc.

      654

      The action was not taken, and the data file was not copied

      The data file is missing records

      655

      The action was not taken, and the 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[jsmith@example.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>

      Was this article helpful?
      What's Next