Follow

Import Relational Data (CSV Batch Process)

The Import Relational Batch Process is different from all other FTP API commands.  Instead of using a .dat file, the command allows you to add and update data in a Relational Data table through a CSV batch file.  It requires a header setup defining the table name and field names followed by one or more rows of quoted, comma-separated table data. The file must be saved as .csv and uploaded followed by the standard empty signal file (.sig).

table=mytable 
columns=uniqueid,number,float,date,string 
"1","1","1.1","1/1/2011","Test String" 

Each table requires columns to define the Unique ID as well as any columns to associate data to foreign tables.  For example, subscriber data fields are required to identify the Subscriber to associate the Relational Data in a Regular table associated to the Subscribers table.  To identify the Subscriber, Email and First Name must be defined, or Customer Key in a customer key enabled Account.

table=myregulartable 
columns=email,first_name,uniqueid,mynumber
"jane.doe@mydomain.com","Jane","11","1234"

Data will be stored in the table named 'myregulartable', fields named 'uniqueID' and 'myNumber' for the subscriber record with the email 'jane.doe@domain.com' and first name 'Jane'

table=myregulartableCK 
columns=customer_key,uniqueid,mynumber
"rel112","11","1234"

Data will be stored in the table named 'myregulartableCK', fields named 'uniqueID' and 'myNumber' for the subscriber record with the customer key 'rel112'.

NOTE: Only include headers and data for ONE table in each CSV file. 

Example

In order to manage Relational Data, the FTP API file Header must include the following arguments: isrelational set to true (1). 

Clear Data may also be set, but must be supported by the Relational Table defined in the commands. If Clear Data is defined in the header, then the table defined within the FTP API file will be cleared of All data if the table allows Clear Data.  Please be very careful using this option, as it cannot be undone once complete.

Using all commands and arguments for three separate files (please note the command is wrapped for display purposes only):

Regular Table
File name: petTest.csv

realm=myrealm
password=mypass
isrelational=1
clear_data=1
confirmation_email=jane.doe@mydomain.com
table=pets
columns=petid,name,type,sex,age
"101","Buster","dog","m","9"
"320","Snowball","cat","f","2"
"410","Speed","turtle","f","1"

Flat Table
File name: foodTest.csv

realm=realm
password=password
confirm_email=myemail@domain.com
isrelational=1
table=food
columns=brand,foodid,name,style
"Feline Fancy","1","Cat Chow","dry"
"Canine Candy","2","Dog Chow","dry"
"RepTiles","3","Turtle Chow","wet"

Join Table
File name: petfoodTest.csv

realm=myrealm
password=mypass
isrelational=1
confirmation_email=jane.doe@mydomain.com
table=petfood
columns=petid,foodid,purchaseDate
"320","1","9/9/2018"
"101","2","3/12/2018"
"410","3","2/4/2018"

In the examples above, the header indicates that the batch process is for Relational Batch Data and that the data in the first file will be removed before processing new data.  

Customer Key

Some systems have the ability to define a unique Customer Key per subscriber.  If your account has Customer Key enabled, then you must include the customer_key in the Columns definition for tables associated to the Subscribers table.  If the Customer Key is not included, the command will fail.

 

Results

Success

The Import Relational Data Batch CSV delivers two emails to the Confirmation Email address(es) defined in the Header.  The messages are generated when the Data (.csv) file is picked up, and then again when the process is complete.  The messages include the Realm ID, name of data file, number of rows in the file, added or updated, and the number of errors.

Errors

The Import Relational Data Batch CSV will fail if a subscriber record for the Email plus First Name (or Customer Key) does not exist, table or data fields do not exist, or do not allow data update.  Failures result in an email message sent to the Confirmation Email defined in the header.  The message contains information related to the failure. 

Find more details about errors in the TASKS > TASK HISTORY page.

Arguments

The Import Relational Data command supports the following arguments:

Name

Req?

Description

Argument

Header
Realm

 Yes

Name of the realm to run the command.  The realm is required in the Heder, but optional in the command line to manipulate data for a child realm.

realm=[realm_name]

Header
Password

 Yes

API Password, or authentication code, for the realm.  See Conventions for more information.

password=[password]

Header
Relational

 Yes

When working with Relational Tables batch process, the Is Relational flag must be set. This is different from the non-batch Relational flag. By default, Relational is not enabled.

isrelational=[0,1]

DEFAULT==0

Header
Clear Data

 

**COMING IN FUTURE RELEASE**
To remove all data in a table before importing new data, set Clear Data to 1.  A table must be created allowing the use of Clear Data, otherwise the flag is ignored.  See Data Management Options for more information.

clear_data=[0,1]

DEFAULT==0

Header
Confirmation Email

 

Specify who will receive a confirmation email after the data file is processed.  This message details the results of the process, such as any errors that occurred.

To specify more than one email address, separate each using commas, semi-colons, or spaces. This field must not exceed 255 characters (not including the confirmation_email= characters).

confirmation_email= [email_address]

Header
Table

 Yes Name of the table into which to import data.  CSV supports only ONE table per file. table=[table_name]

Header
Columns

 Yes Comma-separated list of data fields to import.  The Columns header must not be quoted.  columns=[comma separated list of field_name]
Data Yes

Subscriber and Table related data. To identify the Subscriber to which the data is related, the Subscriber Email and First Name are always required or Customer Key for accounts in which Customer Key is enabled.

Relational Table Unique ID is required, and additional fields are optional.  

"email","first_name","fieldname(s)"

"customer_key","fieldname(s)"

"fieldnameA","fieldnameB",...

**New in v13.04 release

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk