Skill Level: Any
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:
- TABLE_INFO – defines the information about the table you are creating or updating, including the action to take on this table.
- 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).
- 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).
The following table shows each section to include with a TABLE_IMPORT element:
Syntax of the request XML for the TABLE_IMPORT element
Section <TABLE_INFO> The TABLE_INFO section defines information about the table you are
creating or updating, including the action to take on this table.
Defines the type of Relational Table import you are running. Valid values and the actions they
- CREATE – create a new Relational Table.
- ADD_ONLY – Add only new records to the table. The Acoustic Campaign ignores existing (duplicate) records in the source file.
- UPDATE_ONLY – Only update existing records in the table. The Acoustic Campaign ignores records that are found in the source file but not in the Relational Table.
- ADD_AND_UPDATE – process all records in the source file. If the item 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_ID Defines the ID of the Relational Table for actions other than CREATE. ¬† TABLE_VISIBILITY
Defines the visibility of the table. If not included, the Acoustic Campaign sets to Private.
0 – Private
1 – Shared
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 COLUMNS section defines 0 or more columns to write to within the Relational Table. Define each table column in its own COLUMN element. 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 – YESNO column
- 2 – Numeric column
- 3 – Date column
- 4 – Time column
- 5 – Country column
- 6 – Select One
- 7 – Email
- 16 – Phone Number
- 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 uses this to validate the
¬† ¬† 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 that is 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 Acoustic Campaign 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 whether this element is included in the table import. You do not need to import all columns from the source file. Using this element, you can direct the Relational Table Import process to skip the columns you don’t want to include. Valid values are True and False. Example This example shows a mapping file for creating a new Relational table that is related to an existing list. Customer ID and Purchase Date make up the key for the new
<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>CustomerID</NAME> <TYPE>0</TYPE> <IS_REQUIRED>true</IS_REQUIRED> <KEY_COLUMN>true</KEY_COLUMN> </COLUMN> <COLUMN> <NAME>PurchaseDate</NAME> <TYPE>3</TYPE> <IS_REQUIRED>true</IS_REQUIRED> <KEY_COLUMN>true</KEY_COLUMN> </COLUMN> <COLUMN> <NAME>ProductID</NAME> <TYPE>0</TYPE> <IS_REQUIRED>true</IS_REQUIRED> <DEFAULT_VALUE/> </COLUMN> </COLUMNS> <MAPPING> <COLUMN> <INDEX>1</INDEX> <NAME>CustomerID</NAME> <INCLUDE>true</INCLUDE> </COLUMN> <COLUMN> <INDEX>2</INDEX> <NAME>PurchaseDate</NAME> <INCLUDE>true</INCLUDE> </COLUMN> <COLUMN> <INDEX>3</INDEX> <NAME>ProductID</NAME> <INCLUDE>true</INCLUDE> </COLUMN> </MAPPING> </TABLE_IMPORT>