To import recipients or data into Agillic, we need to first create a functioning Import File. In this article, we will walk you through the process of creating an Import File to finally importing the File to your Agillic instance.
Note that Import Files need to be created with a specific format so your Agillic instance can accept it–you can read more about the correct format depending on the different data types in this article: All You Need to Know About Data Models.
What is Being Imported
Agillic automatically imports all the listed data from your Import File that matches the correct format. If some of the data already exists in Agillic, the original is overwritten by the content of your Import File. This includes blank values in your Import File, which will "wipe" any existing data in your system. Any columns of data that aren't mentioned in your Import File won't be affected by the import.
If two rows of your Import File have the same ID in the first column, both rows are skipped. In this case, the rest of the data is still imported. The Import Log documents which rows were skipped.
How to Format an Import File
The format of the Import File depends on the data to be imported, e.g., Person Data, One-to-Many Data, or Global Data.
Person Data
When importing Person Data, the first column must include a unique identifier of each recipient–this might be their email. Each of the following columns must contain the other Person Data fields you wish to import–these Person Data field columns can be set in any order.
In the example below, we use the recipient's email as a unique identifier.
| MOBILE | FIRSTNAME | AGE | FAVOURITE_CANDY | EMAIL_PERMISSION | SMS_PERMISSION | |
|---|---|---|---|---|---|---|
| blue@sweetmail.com | 12345678 | Mr. Blue | 29 | Caramel | TRUE | FALSE |
| green@sweetmail.com | 23456789 | Mrs. Green | 51 | Chocolate | TRUE | TRUE |
| yellow@sweetmail.com | 34567890 | Ms. Yellow | 27 | Vanilla | TRUE | TRUE |
| purple@sweetmail.com | 45678901 | Mr. Purple | 38 | Fruity | FALSE | FALSE |
| black@sweetmail.com | 56789012 | Ms. Black | 30 | Licorice | TRUE | TRUE |
| red@sweetmail.com | 67890123 | Mrs. Red | 60 | Caramel | TRUE | FALSE |
| white@sweetmail.com | 78901234 | Mr. White | 47 | Fruity | TRUE | FALSE |
| pink@sweetmail.com | 89012345 | Ms. Pink | 19 | Vanilla | TRUE | TRUE |
Example of a Person Data Import File.
One-to-Many Data
When importing One-to-Many (OTM) Data:
- The first column of your upload table should be a unique recipient identifier such as an email address or customer number.
- In the second column, insert an ID column for the OTM Table.
- For the rest of the columns, we will use pointers in the format [OTMTABLENAME].[DATAFIELDNAME]: first insert the name of the OTM Table, then insert a '.' (a full stop) and finally insert the name of the data field in the OTM table you want to import data to.
As OTM data is in table form, you can import more than one row to a recipient's OTM table. You can see an example of an OTM table Import File structure below (in our example, the unique recipient identifier is called "EMAIL" and the OTM table is called "PURCHASES").
|
EMAIL
|
PURCHASES.ID
|
PURCHASES.DATE
|
PURCHASES.PRODUCT_ID
|
PURCHASES.STORE
|
| blue@agilliccandy.com | 1 | 01.02.2016 | 21 | Berlin |
| blue@agilliccandy.com | 2 | 31.12.2016 | 32 | Copenhagen |
| green@agilliccandy.com | 3 | 18.10.2016 | 6 | London |
| green@agilliccandy.com | 4 | 15.03.2017 | 18 | Berlin |
Example of a One-to-Many table import named 'PURCHASES'.
Global Data
Unlike Person Data and One-to-Many Data, you don't need a unique recipient identifier to import to a Global Data Table (GDT). However, you will need a unique row ID in the first column.
In order for the system to recognise the GDT, we use pointers in the format [GDTNAME].[DATAFIELDNAME]: for every column in your Global Data Table first insert the name of the GDT, then, insert a '.' (a full stop) and the name of the data field in the GDT you want to import data to.
You can see an example of an Import File structure for a Global Data table below (in our example, the GDT is called "PRODUCT").
|
PRODUCT.ID
|
PRODUCT.CATEGORY
|
PRODUCT.NAME
|
PRODUCT.DESCRIPTION
|
PRODUCT.RANK
|
| 1 | Fruity | Fruity Bubble Gum | The sweet and fruity Bubble Gum will give you a long-lasting taste of fruity goodness that is sure to POP! | 1 |
| 15 | Liquorice | Liquorice Bubble Gum | The sweet taste of our liquorice Bubble Gum will give you a long-lasting taste of liquorice goodness that is sure to POP! | 2 |
| 16 | Chocolate | Chocolate Butter Scotch | This family favourite classic with a chocolate filling is sure to bring back sweet memories, indulging in the chewy caramel and rich chocolate taste | 1 |
| 2 | Caramel | Caramel Butter Scotch | This family favourite classic is sure to bring back sweet memories, indulging in chewy caramel delights | 2 |
| 17 | Vanilla | Vanilla Butter Scotch | We’ve added the best Bourbon-Madagascar vanilla to the classic, making it the newest and sweetest addition to the Butter Scotch family. | 2 |
| 3 | Fruity | Fruity Candy Cane | An oldie but goldie on the candy scene, this iconic pepperminty sweet treat is a symbol of our favourite thing: Candy! | 2 |
| 4 | Fruity | Fruity Candy Floss | Light as air, sweet as the smiles of babies - this Candy Floss might best be described as a piece of those pretty pink clouds as the sun rises! | 2 |
| 18 | Vanilla | Vanilla Candy Floss | This Candy Floss might best be described as a piece of those clouds moving by on a summer day! Add vanilla, and you’ve got a little piece of Heaven. | 2 |
| 19 | Caramel | Caramel Chocolate Bar | Made from the finest Ecuadorian beans, this 85% Dark Chocolate Bar is the perfect indulgence - it’s said to be healthy too! | 1 |
| 5 | Chocolate | Chocolate Chocolate Bar | At home or on the go, the Chocolate Bar is a sweet treat that melts on the tongue and is sure to lift your spirit! | 1 |
Example of a Global Data Table Import File
Note that Global Data Tables are only intended for public non-personal data e.g., product information. Be aware that recipient-specific data in Global Data Tables could lead to GDPR issues for you as a data controller. Recipient-specific data has to exist either as Person Data or One-to-Many data.
Import File Format
By default, the format Settings for your Import Files will be set to the following values:
| Field | Default Value |
| File delimiter | ; |
| Decimal separator | . |
| Date format | dd.MM.yyyy |
| Timestamp format | dd.MM.yyyy HH:mm:ss |
You control the following settings under System Settings in your instance.
Note that we recommend that you upload all files in CSV format–CSV stores data exactly as it is, ensuring no misformatting or mistranslation when opened or imported, and avoiding database errors during uploads.
Importing the File
Once you've created the file to match both the data format and the System Settings, the file is ready for import. You can import the file in a few different ways, the most common being through the User Interface or through a WebDAV connection (Read more about the available import methods).