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, contact data fields are required to identify the Contact to associate the Relational Data in a Regular table associated to the Contacts table. To identify the Contact, Email and First Name must be defined, or Customer Key in a customer key enabled Realm.
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 contact 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 contact 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
confirmation_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 contact. If your Realm has Customer Key enabled, then you must include the customer_key in the Columns definition for tables associated to the Contacts 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 contact 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 |
Yes |
Name of the realm to run the command. The realm is required in the Header, but optional in the command line to manipulate data for a child realm. |
realm=[realm_name] |
|
Header |
Yes |
API Password, or authentication code, for the realm. See Conventions for more information. |
password=[password] |
|
Header |
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 |
**COMING IN FUTURE RELEASE** |
clear_data=[0,1]
DEFAULT==0 |
|
|
Header |
|
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 |
Yes | Name of the table into which to import data. CSV supports only ONE table per file. | table=[table_name] |
|
Header |
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 |
Contact and Table related data. To identify the Contact to which the data is related, the Contact Email and First Name are always required or Customer Key for Realms 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