Understanding the relationships between tables is important when importing data. The relationships impact the requirements of the file for data import. The same steps through the application apply no matter the relationship of your tables when completing an import.
The ability to import data into a Relational Table or column of a table is controlled in several ways.
- CREATE or EDIT TABLE page - the ability to import can be enabled or disabled via a checkbox when creating or editing a table.
- CREATE or EDIT FIELDS page - a specific column can be enabled or disabled via a check box when creating or editing a field or fields.
A Table must be ACTIVE, IMPORT enabled, and any column expected to be included in an import must also be enabled.
Tools to import data into a table can be accesses two ways and are essentially the same for all table types. Data layout that matches the table/column layout is important to understand.
General Import Steps
From the Relational Table Dashboard:
- Go to Subscribers > Relational Tables
- Click the Import link next to the table you'd like to import data into.
- From the Import Wizard page, verify the Data Type drop down is set to Relational and the Table drop down is set to the appropriate table.
- Select the File Type of the file to be imported.
- Click the Browse button to add the file.
- Indicate by checking the check box if the file includes column headers.
- Indicate by checking the check box if the file includes duplicates, where FIRST NAME is unique for a duplicate address.
- Click Next
- Map your file columns to the Relational Table columns defined in the table within the application. In a Customer Key realm, you must only map the subscriber.customer_key field. In a non-customer key field, you must map subscriber.email.
- If your file import should also clear any existing data, indicate by checking the CLEAR DATA checkbox. To learn more about Clear Date, review the CLEAR DATA section in this Learning Center.
- Click Next for your import to start.
Alternately, importing into a Relational Table can also be accomplished from the standard subscriber import pages.
- Go to Subscribers > Subscriber Management > Import Subscribers.
- Select the Relational options from the Data Type drop down.
- Select the Table you'd like to import your file into
- Complete steps 4-11 above.
As a Relational Table data import is initializing and admin email will be generate to summarize the file that will be imported. When the import is complete a second email will be generated that will report successes and/or failures where needed.
The below section outlines a few ERDs and corresponding sample File Format layouts
It should also be noted that in the ERD's below and throughout this guide subscriber_id indicates the system generated unique_id for each subscriber record. For data import the actual system generated subscriber_id is not required however email address is required.
*Where duplicate emails may exist first name is needed along with email address to distinguish unique subscriber_id records. If a Realm is configured to use the Customer Key feature (custom primary key) the customer_key is required.
REGULAR Table Import
For simple single Regular Table import where the Foreign Key is Subscriber, email address is required. Regular Table data import does not also add new subscribers or add/modify subscriptions.
If an email address is included in the Relational Table import file that does not already exist as a subscriber in the Subscriber system table an error will be produced, that row will be ignored, but the file will continue to process. Any error(s) will be available in the admin confirmation email.
A Regular Table with a Foreign Key of another Regular Table does not require an email address but does require the unique_id of the first table (the Foreign Key) for a successful import into the second table.
If the Foreign Key does not already exist in the first table an error will be produced, that row will be ignored, but the file will continue to process. Any error(s) will be available in the admin confirmation email.
A rule of thumb when working with Regular Tables - import right to left! This means start with your subscriber table, then the first regular table, then second regular table, and so on.
FLAT Table Import
Unlike a Regular or Join table, Flat tables do not require any other table relationship columns to be included in a import file. A Flat tables only requirement is that it include a single column with a unique identifier.
JOIN Table Import
Because the purpose of a JOIN Table is to connect the other tables to each other at minimum the file requires 3 columns - a Join_ID, and the Field of each Foreign Keyed table.
A rule of thumb: import data into a Join table last! Another way to say it might be: import from the outside in! Importing Join data last, or joined tables first(outside in), ensures foreign keys match all tables joined.