Overview

Skill Level: Any

Prerequisites

This interface is used for programmatically creating or updating a Relational Table in Watson Campaign Automation. This operation requires a mapping file and source file stored on the FTP server related to the Watson Campaign Automation account used to Login. Once you upload the Relational Table source and mapping files to the FTP server, you can make an ImportTable API call to launch the job.

Note:¬†This operation does not associate the relational table to a database. See “Associate Relational Data to Contacts in a Database” for creating the association.

Step-by-step

  1. Syntax of the request and response SOAP for ImportTable

    Operation  <ImportTable>   
    Elements  MAP_FILE  The name of the Mapping file in the upload directory on the FTP server to use for the
    import. 
      SOURCE_FILE The name of the file containing the Relational Table data to use in the import. This file must reside in the upload directory on the FTP server. 
    Example¬† <soapenv:Envelope xmlns:soapenv=”http://schemas.xmlsoap.org/soap/envelope/”
    xmlns:ses=”SilverpopApi:EngageService.SessionHeader”
    xmlns:user=”SilverpopApi:EngageService.ListMgmt.UserActions”>
    <soapenv:Header>
    <ses:sessionHeader>
    <ses:sessionid>50F4F411A048A4DDA159ABE2954E4610</ses:sessionid>
    </ses:sessionHeader>
    </soapenv:Header>
    <soapenv:Body>
    <user:ImportTable>
    <user:MAP_FILE>Pets_Table.xml</user:MAP_FILE>
    <user:SOURCE_FILE>Pets_Table.csv</user:SOURCE_FILE>
    </user:ImportTable>
    </soapenv:Body>
    </soapenv:Envelope> 
     
    Response  <RESULT>   
    Elements  SUCCESS  True if successful. 
      JOB_ID  Identifies the Watson Campaign Automation Background Job created and scheduled for this import. 
    Example¬† <envelope:Envelope xmlns=”SilverpopApi:EngageService.ListMgmt.UserActions”xmlns:envelope=”http://schemas.xmlsoap.org/soap/envelope/”>
    <envelope:Header/>
    <envelope:Body>
    <RESULT>
    <SUCCESS>TRUE</SUCCESS>
    <JOB_ID>273287</JOB_ID>
    </RESULT>
    </envelope:Body>
    </envelope:Envelope> 
     

    Relational Table Definition and Column Mapping Format

    Mapping is the process of associating columns in the import file with the corresponding fields in the Relational Table. For example, Column 1 in the file corresponds to Purchase Date in the Purchases Relational Table.

    The Relational Table Mapping XML file is broken into three main sections:

    1. TABLE_INFO ‚Äď defines the information about the table you are creating or updating, as well as the action to perform on this table.
    2. COLUMNS ‚Äď consists of one or more COLUMN elements. Each COLUMN element contains child elements to define the attributes for that column (for example, name, type, and key column).
    3. MAPPING ‚Äď also consists of COLUMN elements. Each COLUMN element contains child elements to define the attributes for mapping that column (for example, name or index).

    Note:¬†All column names are case‚Äźsensitive. The Mapping file and Relational Data file field names must exactly match the case and content in the existing Relational Table.

    The table below describes each section to include with a LIST_IMPORT element:

    Section  <TABLE_INFO> The TABLE_INFO section defines information about the table you are creating or updating, as well as the action to perform on this table.   
    Elements  ACTION

    Defines the type of Relational Table import you are executing. Valid values (and the
    actions they perform) are:

    CREATE ‚Äď create a new Relational Table.

    ADD_ONLY ‚Äď only add new records to the table. Watson Campaign Automation ignores existing (duplicate) records in the source file.

    UPDATE_ONLY ‚Äď only update existing records in the table. Watson Campaign Automation ignores records found
    in the source file but not in the Relational Table.

    ADD_AND_UPDATE ‚Äď process all records in the source file. If the item already exists in
    the Relational Table, update specified field values. If the item does not exist, create a new record in the Relational Table.

    REPLACE ‚Äď deletes all records from the Relational Table and adds new records to the
    table.

     

     
      TABLE_NAME  Defines the name of the new Relational Table if the ACTION is CREATE (table names must be unique).   
      TABLE_VISIBILITY 

    Defines the visibility of the table. If not included, Watson Campaign Automation will set to Private.

    0 ‚Äď Private

    1 ‚Äď Shared

     

     
      PARENT_FOLDER_PATH 

    Used with the CREATE ACTION to specify the folder within which to place the new table.

    Use the VISIBILITY element to specify Private or Shared. 

     
      FILE_TYPE 

    Defines the formatting of the source file. Supported values are:

    0 ‚Äď CSV file

    1 ‚Äď Tab‚Äźseparated file

    2 ‚Äď Pipe‚Äźseparated file

     

     
      HASHEADERS  The HASHEADERS element is set to True if the first line in the source file contains column definitions.   
    Section  <COLUMNS>  The HASHEADERS element is set to True if the first line in the source file contains column definitions.   
    Elements  COLUMN     
      Child Elements  NAME  Defines the name of the column. 
        TYPE 

    Defines what type of column to create. The valid values are:

    0 ‚Äď Text column

    1 ‚Äď Yes/No column

    2 ‚Äď Numeric column

    3 ‚Äď Date column

    4 ‚Äď Time column

    5 ‚Äď Country column

    6 ‚Äď Select One

    7 ‚Äď Email

    17 ‚Äď Date/Time

    19 ‚Äď Sync ID

     

        IS_REQUIRED 

    Defines whether to create the column as Required when contacts are providing information through a form. Valid values are True and False.

    Note: The import process does use this to validate the data. 

        KEY_COLUMN  Added to field definition and set to True to define a field as a unique
    key for the Relational Table. You can define more than one unique field for the table to create a composite key. 
        DEFAULT_VALUE  Defines the default value of the column used by the Send process if the record does not contain a value for this column. 
    Section  <MAPPING>  The MAPPING section tells the Relational Table Import process which columns in the source file map to which columns in the Watson Campaign Automation Relational Table. Define each table column in its own COLUMN element.   
    Elements  COLUMN     
      Child Elements  INDEX  The INDEX element defines the order within which to define this
    column in the source file. 
        NAME  The name of the TABLE column to which to map the column in the source file. 
        INCLUDE  Defines if this element is included in the table import. You do not need to import all columns from the source file. This element allows you to direct the Relational Table Import process to skip the columns
    you don’t want to include. Valid values are True and False.¬†

    Example Mapping File

    Example  This example shows a mapping file for creating a new Relational Table related to an existing list. Customer ID and Purchase Date make up the key for the new table: 
      <TABLE_IMPORT>
    <TABLE_INFO>
    <ACTION>CREATE</ACTION>
    <TABLE_NAME>Purchases</TABLE_NAME>
    <FILE_TYPE>0</FILE_TYPE>
    <HASHEADERS>true</HASHEADERS>
    </TABLE_INFO>
    <COLUMNS>
    <COLUMN>
    <NAME>Customer ID</NAME>
    <TYPE>0</TYPE>
    <IS_REQUIRED>true</IS_REQUIRED>
    <KEY_COLUMN>true</KEY_COLUMN>
    </COLUMN>
    <COLUMN>
    <NAME>Purchase Date</NAME>
    <TYPE>3</TYPE>
    <IS_REQUIRED>true</IS_REQUIRED>
    <KEY_COLUMN>true</KEY_COLUMN>
    </COLUMN>
    <COLUMN>
    <NAME>Product ID</NAME>
    <TYPE>0</TYPE>
    <IS_REQUIRED>true</IS_REQUIRED>
    <DEFAULT_VALUE/>
    </COLUMN>
    </COLUMNS>
    <MAPPING>
    <COLUMN>
    <INDEX>1</INDEX>
    <NAME>Customer ID</NAME>
    <INCLUDE>true</INCLUDE>
    </COLUMN>
    <COLUMN>
    <INDEX>2</INDEX>
    <NAME>Purchase Date</NAME>
    <INCLUDE>true</INCLUDE>
    </COLUMN>
    <COLUMN>
    <INDEX>3</INDEX>
    <NAME>Product ID</NAME>
    <INCLUDE>true</INCLUDE>
    </COLUMN>
    </MAPPING>
    </TABLE_IMPORT> 

Join The Discussion

Your email address will not be published. Required fields are marked *