🌐

Other languages available for this guide

🇫🇷 La version française de ce guide
🇪🇸 La versión española de esta guía
🇮🇹 La versione italiana di questa guida

This article explains how to prepare a contacts file for import with Splio. All examples used in this file are either anonymized or fictitious.

Prerequisites

• Basic knowledge of the CSV format and UTF-8 encoding.
• A UTF-8-enabled text editor.
• A spreadsheet software.
• The sub-sequence must be defined in the config file under the “contacts” scope.

Preparation of a contacts file

Edit the import file with your favorite UTF-8-capable text editor. If necessary, use a spreadsheet software you are comfortable with to control the number and position of columns.

📘

Always remember to save using the UTF-8 encoding without BOM.

🚧

Please limit the size of your file to 200K lines.

Header and Columns

The first line of the file called the header, is used to determine the content of the following lines. Therefore, it should be constructed only from the names of columns.

🚧

Remember that Splio will skip the file if it encounters a column name it cannot recognize, or if the file does not contain the mandatory columns.

The following columns are available in “contacts” files:

📘

• Note that the column names are case sensitive, e.g. “cellphone” and “CellPhone” are two different columns.
• Each column name should be used only once.
• The “email” and “customer_key” columns are mandatory, but you always use only one of them, depending on the configuration of your Splio universe. See the “Customer key” section below for more details.

Customer Key

Splio uses a single column to recognize unique contacts in the database. It is often called “customer key” because each value in this column is always unique, and you can identify your customer (contact) records by this value. For this reason, the customer key column is always mandatory.

By default, the “email” field is the customer key. This case is presented in Example 1.

Your SCP universe can be configured to use a custom column instead of “email” to identify contacts. If it is so, this column replaces “email” as the customer key and becomes mandatory. Use the alias of this custom column in the header of your import file.

🚧

Remember that Splio does not normally distinguish between contacts on the basis of phone numbers or check for duplicated phone numbers unless the field "cellphone" is set as customer key. It is up to you to deduplicate the cellphone data when necessary.

Example 1: Default Customer Key

This example shows the default case when the contact’s email address is the customer key. Use “email” in the header to identify it:

email;cellphone;firstname;lastname;language;subscriptions 
"[email protected]";"2235334599";"Spotty";"Spots";"fr";"+2" 
"[email protected]";"+4499884472525";"Monica";"Jameson";"en";"+2" 
"[email protected]";"+48502553311";"Lukas";"Syndyk";"pl";"+2" 

📘

• There are 6 columns in the example. The email addresses will be matched against the database to see if each record already exists. New records will be added and existing ones updated, depending on the settings in your Config file.
• Note that the phone number in the first line below the header does not have the country code. In such a case, the default code defined in the config file will be used.
• The last column tells Splio to subscribe all contacts to list No 2. See “list subscriptions” below.

Example 2: Custom column as Customer Key

This example presents an import file for a Splio universe which uses a custom field to distinguish between unique contacts.

In this universe the “c0” column was configured to identify contacts. This column has the alias “ExternalId”. We recomment using the alias to improve clarity.

"ExternalId";email;cellphone;firstname;lastname;language;subscriptions 
"PPL000000045732";"[email protected]";"+332235334599";"Mister";"Jinx";"fr";"+1,+2,-11" 
"PPL000000107463";"[email protected]";"+4499884472525";"Monica";"Jameson";"en";"+1,+2,-11" 
"PPL000000084232";"[email protected]";"+420257537371";"Ijon";"Tichy";"cz";"+1,+3,-11" 

📘

• Seven columns can be seen, the first is the custom “customer_key” column containing external identifiers. Splio will use these values to check which records are new, and which already exist.
• The “email” column is still used to import email addresses for the contacts.

Name your file

Save your file under a name composed of the universe name, scope (“contacts”), sub-sequence, and current date. For example:

myuniverse_contacts_business_20180410.csv

This filename belongs to the universe “myuniverse”, sub-sequence “business” defined for contacts, and is dated April 10, 2018.

If you wish to know more, consult the File Naming and Grouping guide.

You can now upload the file to SFTP/FTPS.

Explanations

This section contains additional explanations which may be useful when working with contacts files.

List subscriptions

Contacts in Splio can belong to numbered lists, and imports allow you to perform batch subscribing and unsubscribing using the subscriptions column.

The column contains a list of signed numbers separated by commas (“,”). Each number must be preceded by the plus sign (“+”) to subscribe (e.g., “+3”) or by the minus sign (“-”) to unsubscribe (e.g., “-6”). The whole list must be enclosed within quotation marks, like “+3,-6”.

In the following example, the contact is subscribed to lists 1 and 7, and unsubscribes from lists 2 and 4:

email;subscriptions 
"[email protected]";"+1,-2,-4,+7" 

Be careful when editing files containing the subscription column with spreadsheet software. It may misinterpret the list and break it, especially if it wants to use the comma (“,”) as separator.

Dates

The dates in the “dateOfCreation” field consist of 4 digits for a year, 2 for a month, and 2 for a day, followed by hours, minutes, and seconds, 2 digits each. A correct date for March 11th, 2021, 1:17 pm will take the following form:

2021-03-11 13:17:00

The day and hour are separated by a blank space. You can omit the time part and only use the date. If you do, Splio will assume the earliest possible hour for the day (midnight). Therefore,

2021-03-07 is treated exactly as if you entered 2021-03-07 00:00:00

Remember that if you do not specify the dateOfCreation, Splio will use the import date and time.

🚧

• Using dates without time should never be an option for dates and times: 00:00:00 is midnight and Splio will try to process the date as such. This may result in errors and some triggers associated with dates will fail. An additional benefit of using complete dates is that you will be able to search and filter by date with much greater accuracy.
• When importing dates with time, make sure to always use the CEST timezone).

NULL and erasing values

NULL is a special value that tells the database that the field holding it is empty.

Your Splio universe can be configured to interpret NULL values as instructions to empty fields. You can use this to erase values stored in the database. To do so, make sure that the imported value is exactly NULL. You need to avoid leading or trailing spaces: NULL or NULL will be recognized as string values.

If this option is not set, Splio will retain the values for the fields where the imported value is NULL.

Difference between NULL and empty string

"" is an empty string. In most cases, both NULL and "" will be imported as an empty value.

Most importantly, the empty string "" is never considered a NULL value, so it will not cause an existing value to be erased.