Import to a database

📘
Complete an import with the APi

Upload the source file to the Acoustic Campaign FTP server.

Upload the Definition and Column Mapping file to the Acoustic Campaign FTP server.

Authenticate the user with a Login API call.

Initiate the database import job with the ImportList API call. (Syntax and more information found below).

(Optional) Determine the status of the background import job with the GetJobStatus API call.

Log off from the Acoustic Campaign API with a Logout API call.

You must place all files in the upload directory on the FTP server or the database import service will not find them.

Elements (Required)

MAP_FILE - The name of the mapping file in the upload directory of the FTP server to use for the import.

SOURCE_FILE - The name of the file containing the contact information to use in the import. This file must reside in the upload directory of the FTP server.

Elements (Optional)

EMAIL - The email address notified when the job is complete.

FILE_ENCODING - Defines the encoding source file: UTF-8 or ISO-8859-1. If not specified, Acoustic Campaign uses the org default encoding.

<Envelope>
   <Body>
      <ImportList>
         <MAP_FILE>list_import_map.xml</MAP_FILE>
         <SOURCE_FILE>list_create.csv</SOURCE_FILE>
      </ImportList>
   </Body>
</Envelope>

<Envelope>
   <Body>
      <RESULT>
         <SUCCESS>TRUE</SUCCESS>
         <JOB_ID>108518</JOB_ID>
      </RESULT>
   </Body>
</Envelope>

Elements (Results)

SUCCESS - True if successful; False if not.

JOB_ID - Identifies the job created and scheduled for this import.

Database definitions and column mapping for your import files

Mapping is the process of associating columns in the import file with the corresponding fields in the database. For example, Column 1 in the mapping file corresponds to Name in the database. You must describe the details of the database, including its columns, and the format of the source file (which contains the actual contact records). You place this information in an XML mapping file and upload it to a specified directory on an FTP server.

Add the <KEY_COLUMN> element (set to True) in a field declaration in the mapping file to set a field as a unique key for the database. You can also define more than one unique key field for a database. You must define the EMAIL field in the mapping file and cannot leave it out for a NEK database. The column TYPE must be set to 9 and the IS_REQUIRED element must be set to True. However, you must specify KEY_COLUMN as True to list it as a unique key. If not specified, it is a regular field in the database.

Acoustic Campaign returns errors in the XML submission as part of the XML response.
However, Acoustic Campaign does not detect errors in the mapping file or the source file until it runs the data job. You can view Data Job errors on the Data Jobs screen of the Acoustic Campaign user interface.

LIST_INFO: Defines information about the database you are creating (or updating). Includes the actions to perform on this database. COLUMNS: When creating a new database, this section 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). If the action is not CREATE (and you are not adding new columns to the database), you can exclude the COLUMNS section from the mapping file.
MAPPING: Consists of COLUMN elements. Each COLUMN element contains child elements to define the attributes for mapping that column (for example, name and index).
CONTACT_LISTS: An optional section that is used to specify one or more Contact Lists that all contacts are added to in addition to the database. Can be used with all actions except OPT_OUT.

The Import Data Job handles imports of contacts to a database that has No Unique Identifier as follows:

If the ACTION is CREATE or ADD_ONLY and SYNC_FIELDS are NOT specified, each row in the source file results in a new contact.
If the ACTION is CREATE and SYNC_FIELDS are specified, rows that have identical SYNC_FIELDS within the source file are rejected as duplicates.
If the ACTION is ADD_ONLY and SYNC_FIELDS are specified, rows that have identical SYNC_FIELDS within the source file or in the database are rejected as duplicates.
If the ACTION is UPDATE_ONLY, contacts matching the SYNC_FIELDS are updated; all other contacts are ignored. If more than one contact is found matching the lookup columns, all contacts are updated.
If the ACTION is ADD_AND_UPDATE, contacts matching the SYNC_FIELDS are updated; all other contacts are added. If more than one contact is found matching the lookup columns, all contacts are updated.
If the ACTION is OPT_OUT, contacts matching the SYNC_FIELDS are opted out; all other contacts are ignored. If more than one contact is found matching the lookup columns, the all matching contacts are opted out.

📘
Formatting note
All column names are case-sensitive, meaning you must ensure the names in the mapping file and source file exactly match the field names in the Acoustic Campaign database. Each section must be contained within the LIST_IMPORT element.

Section Elements

<LIST_INFO> - Defines the structure of the database you are creating or updating, including the action to perform on this database.

ACTION - Defines the type of import you are performing:

CREATE - Create a new database
ADD_ONLY - Add only new contacts to the database. Ignore existing contacts when found in the source file.
UPDATE_ONLY - Update only the existing contacts in the database. Ignore contacts who exist in the source file but not in the database.
ADD_AND_UPDATE - Process all contacts in the source file. If they exist in the database, updates the values. If they do not exist, creates a new record in the database for the contact.
OPT_OUT - Opt-out any contact in the source file who is already in the database. Ignore contacts who exist in the source file but not the database.
All contacts that match the provided email address are opted-out unless the database has no unique identifier and SYNC_FIELDS are provided.
Note: If the database is a flexible database, SYNC_FIELDS must be added. If the ACTION is OPT_OUT, contacts matching the SYNC_FIELDS are opted out; all others are ignored. If more than one contact is found matching the lookup columns, then all matching contacts are opted out.

LIST_TYPE - Defines the type of database. Only specified if the ACTION is CREATE. Supported values are 0 (Database), 6 (Seed List), or 13 (Suppression List).

LIST_NAME - Defines the name of the new database if the ACTION is CREATE (names must be unique)

LIST_ID - Unique ID of the database in the Acoustic Campaign system. Must be provided for all ACTION values except CREATE

CONSENT - Defines the consent information applied during the import process and is only supported for flexible databases.

CHANNEL - Required A child of CONSENT. Specifies the channel to apply the consent. Only valid value SMS.

TEST_TO_JOIN_PROGRAM_ID - Required A child of CONSENT. Specifies the Text to Join program ID that can be achieved from the SMS Campaign Manager.

📘
Note:
Mgage Text To Join Program ID appears in the URL when you open the Text to Join program.

HONOR_OPT_OUT_STATUS - Optional A child of CONSENT. Defines updating the Acoustic Campaign SMS consent status of the contact being updated by the import list. Applies when the Import ACTION is UPDATE_ONLY or ADD_AND_UPDATE.

True - Leave consent for an existing contact as opted out
False - Update consent with the consent status in import file
Blank - Defaults to true
Invalid: Error - If the mapping file doesn’t include this flag, Acoustic Campaign treats it as false.

OVERRIDE_ON_NO_CHANGE - Required Defines whether to update the source and consent date on the consent record if the status on the contact is the same as in the import file. Applies when the Import ACTION is UPDATE_ONLY or ADD_AND_UPDATE.

True - Replace the consent date and consent source for existing record.
False - Do not change consent Date and consent source.
Blank - Defaults to False.

LIST_VISIBILITY - Required Defines the visibility of the newly created database: 0 (private) or 1 (shared)

USE_RECIPIENT_ID - Required only if the database is a flexible and ACTION is UPDATE_ONLY and records should be matched using the unique Recipient Id value for each database contact

PARENT_FOLDER_PATH - Used with the CREATE action to specify the folder to place the new database in. Use the VISIBILITY element to specify Private/Shared

FILE_TYPE - Defines the formatting of the source file: 0 (CSV), 1 (tab separated file), or 2 (pipe separated file)

HASHEADERS - Set to True if the first line in the source file contains column definitions

LIST_DATE_FORMAT - Optional Specifies the date format and fields in your file if your date format differs "mm/dd/yyyy" (the month, day, and year can be in any order you choose).

Valid values for Month:

MM (for example, 01)
M (for example, 1)
MMM (for example, Jan)
MMMM (for example, January)

Valid values for Day:

dd (for example, 02)
d (for example, 2)

Valid values for Year:

yyyy (for example, 1999)
yy (for example, 99)
Separators can be up to two characters in length and consist of periods, commas, question marks, spaces, and forward slashes (/).

DOUBLE_OPT_IN - Optional When accompanying the CREATE action, Acoustic Campaign creates a Double Opt-In database. If you do not specify the element, the organization default is used.
Note: The database security settings that allow you to use the database import operation are disabled by default for all Double Opt-In databases.

ENCODED_AS_MD5 - Optional When set to True, email addresses in the Email column are MD5 encoded

SYNC_FIELDS - Required only if the database is a flexible database and action is UPDATE_ONLY, ADD_AND_UPDATE, or OPT_OUT. Specifies what columns are used to look up a contact in a flexible database. Include a SYNC_FIELD element for each database column that defines a unique contact.
Note: If more than one contact is found matching the lookup columns, all matching contacts are updated. If the ACTION is opt-out, all matching contacts are opted out.

SYNC_FIELD - A child element of SYNC_FIELDS. XML nodes that define the column Name of the unique identifier fields.

NAME - A child element of SYNC_FIELDS. Defines the field name.

> COLUMN - Defines how many columns to write to the database (0 or more). Use a separate COLUMN element to define each database column. If the ACTION is not CREATE and you are not adding new columns to the database, you can exclude the COLUMNS section from the mapping file.

NAME - A child element of COLUMN. Defines the name of the column.

TYPE - A child element of COLUMN. Defines what type of column to create:

0 - Text column
1 - Yes/No column
2 - Numeric column
3 - Date column
4 - Time column
5 - Country column
6 - Select one
8 - Segmenting
9 - System (for defining the EMAIL field only)
15 - SMS Phone Number
16 - Phone Number
17 - Timestamp
20 - Multi-Select
Note: The import process does not validate the data in the source file against these column types.

IS_REQUIRED - A child element of COLUMN. Defines whether to create the column as "required" when contacts are providing information through a web form. The import process does not use this information to validate the data. Valid values are True and False.

KEY_COLUMN - A child element of COLUMN. Added to field definition and defines a field as a unique key for the database when set to True. You can define more than one unique field for each database.
Note: When creating a flexible database and not indicating any key fields, RECIPIENT_ID column must be defined as follows:

<COLUMN>
   <NAME>RECIPIENT_ID</NAME>
   <TYPE>9</TYPE>
   <IS_REQUIRED>True</IS_REQUIRED>
   <KEY_COLUMN>True</KEY_COLUMN>
   <DEFAULT_VALUE/>
</COLUMN>

DEFAULT_VALUE - A child element of COLUMN. Defines the default value of the column that is used by the Send process if the contact does not contain a value for this column.
Note: When using a Date as the Default Value, the format must be mm/dd/yyyy.

SELECTION_VALUES - A child element of COLUMN. Used with Type 6 to define values in a drop-down list. You must provide at least one element for each value.

> COLUMN - Defines the columns in the source file map to the columns in the database. Define each column in its own COLUMN element.

INDEX - A child element of MAPPING>COLUMN. Defines the order that the source file defines this column.

NAME - A child element of MAPPING>COLUMN. The name of the database column that Acoustic Campaign maps the source file column. You can use the following system-defined names in the mapping file:

EMAIL – The email address.
EMAIL_TYPE – The type of contact body to send.
CREATED_FROM – An integer that defines where Acoustic Campaign collected the contact’s information.
OPTED_IN – The date and time the contact was opted into the database. Acoustic Campaign assumes all times to be GMT. If no opt-in date is specified, the system inserts the current date and time (in GMT).
OPT_IN_DETAILS – Any information (up to 250 characters) that defines where Acoustic Campaign obtained the contact’s information.
OPTED_OUT – The date and time the contact opted out of the database (for OPT_OUT actions only).
The following system defined names are only applicable when CONSENT element is used in a mapping file.
CONSENT_DATE – Date of the SMS Consent. If not specified, the Acoustic Campaign uses System Date when Import Job occurred.
CONSENT_SOURCE – User-defined Consent Source. If not specified, the Acoustic Campaign inserts ‘List Import’ as a value.
CONSENT_STATUS_CODE – Required Valid values are OPTED-IN and OPTED-OUT.
Note: All system-type field names must be in uppercase.

INCLUDE - A child element of MAPPING>COLUMN. Defines whether to include an element in the database import. You do not need to include all source file columns in the import. This element specifies what columns to skip during the Import process. Valid values are True or False.

IS_CONSENT - A child element of MAPPING>COLUMN. The required system defined name when CONSENT element is used in a mapping file. Valid value is True.

<CONTACT_LISTS> - Use the CONTACT_LISTS section to specify one or more Contact Lists that all contacts are added to in addition to the database. Can be used with all actions except OPT_OUT.

CONTACTLISTID - A child element of CONTACT_LISTS. Zero to many elements defined the ID of an existing Contact List.

<LIST_IMPORT>
   <LIST_INFO>
      <ACTION>UPDATE_ONLY</ACTION>
      <LIST_NAME>Premier Accts</LIST_NAME>
      <LIST_VISIBILITY>0</LIST_VISIBILITY>
      <FILE_TYPE>0</FILE_TYPE>
      <HASHEADERS>true</HASHEADERS>
   </LIST_INFO>
   <SYNC_FIELDS>
      <SYNC_FIELD>
         <NAME>EMAIL</NAME>
      </SYNC_FIELD>
   <SYNC_FIELD>
      <NAME>Customer Id</NAME>
    </SYNC_FIELD>
   </SYNC_FIELDS>
   <MAPPING>
      <COLUMN>
         <INDEX>1</INDEX>
         <NAME>EMAIL</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>2</INDEX>
         <NAME>Customer Id</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>3</INDEX>
         <NAME>First_Name</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
   </MAPPING>
</LIST_IMPORT>

<LIST_IMPORT>
   <LIST_INFO>
      <ACTION>ADD_AND_UPDATE</ACTION>
      <FILE_TYPE>0</FILE_TYPE>
      <HASHEADERS>true</HASHEADERS>
      <LIST_ID>8680941234</LIST_ID>
   </LIST_INFO>
   <CONSENT>
      <CHANNEL>SMS</CHANNEL>
      <TEXT_TO_JOIN_PROGRAM_ID>108063</TEXT_TO_JOIN_PROGRAM_ID>
      <HONOR_OPT_OUT_STATUS>true</HONOR_OPT_OUT_STATUS>
      <OVERRIDE_ON_NO_CHANGE>false</OVERRIDE_ON_NO_CHANGE>
   </CONSENT>
   <SYNC_FIELDS>
      <SYNC_FIELD>
         <NAME>Sms Phone</NAME>
      </SYNC_FIELD>
   </SYNC_FIELDS>
   <MAPPING>
      <COLUMN>
         <INDEX>1</INDEX>
         <IS_CONSENT>true</IS_CONSENT>
         <NAME>CONSENT_DATE</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>2</INDEX>
         <NAME>Account id</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>3</INDEX>
         <IS_CONSENT>true</IS_CONSENT>
         <NAME>CONSENT_SOURCE</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>4</INDEX>
         <NAME>First name</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>5</INDEX>
         <IS_CONSENT>true</IS_CONSENT>
         <NAME>CONSENT_STATUS_CODE</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>6</INDEX>
         <NAME>Sms Phone</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
    </MAPPING>
</LIST_IMPORT>

<LIST_IMPORT>
   <LIST_INFO>
      <ACTION>ADD_AND_UPDATE</ACTION>
      <LIST_ID>50194</LIST_ID>
      <FILE_TYPE>0</FILE_TYPE>
      <HASHEADERS>true</HASHEADERS>
   </LIST_INFO>
   <COLUMNS>
      <COLUMN>
         <NAME>EMAIL</NAME>
         <TYPE>9</TYPE>
         <IS_REQUIRED>true</IS_REQUIRED>
         <KEY_COLUMN>true</KEY_COLUMN>
      </COLUMN>
      <COLUMN>
         <NAME>CustID</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>true</IS_REQUIRED>
         <KEY_COLUMN>true</KEY_COLUMN>
      </COLUMN>
      <COLUMN>
         <NAME>Att1</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE />
      </COLUMN>
      <COLUMN>
         <NAME>Att2</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE />
      </COLUMN>
      <COLUMN>
         <NAME>CountryField1</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE />
      </COLUMN>
      <COLUMN>
         <NAME>CountryField2</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE />
      </COLUMN>
      <COLUMN>
         <NAME>DateField1</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE />
      </COLUMN>
   </COLUMNS>
   <MAPPING>
      <COLUMN>
         <INDEX>1</INDEX>
         <NAME>EMAIL</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>2</INDEX>
         <NAME>CustID</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>3</INDEX>
         <NAME>Att1</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>4</INDEX>
         <NAME>Att2</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>5</INDEX>
         <NAME>CountryField1</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>6</INDEX>
         <NAME>CountryField1</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
      <COLUMN>
         <INDEX>7</INDEX>
         <NAME>DateField1</NAME>
         <INCLUDE>true</INCLUDE>
      </COLUMN>
   </MAPPING>
   <CONTACT_LISTS>
      <CONTACT_LIST_ID>123</CONTACT_LIST_ID>
      <CONTACT_LIST_ID>456</CONTACT_LIST_ID>
   </CONTACT_LISTS>
</LIST_IMPORT>

<LIST_IMPORT>
   <LIST_INFO>
      <ACTION>UPDATE_ONLY</ACTION>
      <LIST_ID>123</LIST_ID>
      <FILE_TYPE>0</FILE_TYPE>
      <HASHEADERS>true</HASHEADERS>
      <USE_RECIPIENT_ID>true</USE_RECIPIENT_ID>
   </LIST_INFO>
   <COLUMNS>
      <COLUMN>
         <NAME>EMAIL</NAME>
         <TYPE>9</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE/>
      </COLUMN>
      <COLUMN>
         <NAME>RECIPIENT_ID</NAME>
         <TYPE>9</TYPE>
         <IS_REQUIRED>true</IS_REQUIRED>
         <KEY_COLUMN>true</KEY_COLUMN>
         <DEFAULT_VALUE/>
      </COLUMN>
      <COLUMN>
         <NAME>CustomerId</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE/>
      </COLUMN>
      <COLUMN>
         <NAME>First Name</NAME>
         <TYPE>0</TYPE>
         <IS_REQUIRED>false</IS_REQUIRED>
         <DEFAULT_VALUE/>
      </COLUMN>
   </COLUMNS>
   <MAPPING>
      <COLUMN>
         <INDEX>1</INDEX>
         <INCLUDE>true</INCLUDE>
         <NAME>Contact Identifier (Recipient ID)</NAME>
      </COLUMN>
      <COLUMN>
         <INDEX>2</INDEX>
         <INCLUDE>true</INCLUDE>
         <NAME>Email</NAME>
      </COLUMN>
      <COLUMN>
         <INDEX>3</INDEX>
         <INCLUDE>true</INCLUDE>
         <NAME>CustomerId</NAME>
      </COLUMN>
      <COLUMN>
         <INDEX>4</INDEX>
         <INCLUDE>true</INCLUDE>
         <NAME>First Name</NAME>
      </COLUMN>
    </MAPPING>
</LIST_IMPORT>

<LIST_IMPORT>
    <LIST_INFO>
        <ACTION>CREATE</ACTION>
        <LIST_NAME>Premier Accts</LIST_NAME>
        <LIST_VISIBILITY>0</LIST_VISIBILITY>
        <FILE_TYPE>0</FILE_TYPE>
        <HASHEADERS>true</HASHEADERS>
    </LIST_INFO>
    <COLUMNS>
       <COLUMN>
          <NAME>EMAIL</NAME>
          <TYPE>9</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
       </COLUMN>
       <COLUMN>
          <NAME>CustID</NAME>
          <TYPE>0</TYPE>
          <IS_REQUIRED>true</IS_REQUIRED>
          <KEY_COLUMN>true</KEY_COLUMN>
       </COLUMN>
    </COLUMNS>
    <MAPPING>
       <COLUMN>
          <INDEX>1</INDEX>
          <NAME>EMAIL</NAME>
          <INCLUDE>true</INCLUDE>
       </COLUMN>
       <COLUMN>
          <INDEX>2</INDEX>
          <NAME>CustID</NAME>
          <INCLUDE>true</INCLUDE>
       </COLUMN>
    </MAPPING>
</LIST_IMPORT>

Import to a database - ImportList

📘
Complete an import with the APi

Elements (Required)

Elements (Optional)

Elements (Results)

Database definitions and column mapping for your import files

📘
Formatting note

Section Elements

📘
Note:

📘Complete an import with the APi

Elements (Required)

Elements (Optional)

Elements (Results)

Database definitions and column mapping for your import files

📘Formatting note

Section Elements

📘Note:

📘
Complete an import with the APi

📘
Formatting note

📘
Note: