Scheduled import

Introduction

Scheduled Imports enables you to automatically synchronize recipient data from your databases into Symplify lists.

This article will give you information about

Here is a scenario that explains the basics Scheduled Import:

A CRM system is collecting email addresses and phone numbers to clients who wish to be contacted. A database administrator sets up a daily export to save these new addresses and phone numbers to a text file. The text file is then placed on Symplify's FTP server.
The import function in Symplify is configured to pick up the file placed on the FTP and populate a list in Symplify with it’s content.

Note that you can also update your database in Symplify with our API. Learn more in this article

Setup overview

The setup of a Scheduled Import consists of four parts:

  1. Symplify supplies this document along with Symplify user login and FTP login information.
  2. You create a file according to the file format specifications (below).
  3. Create the scheduled import in Symplify (Account settings) to connect your file with a specific list and set the import frequency.
  4. Map the fileheader-columns in your file to attributes in Symplify. This is how you control that your file data is saved in the correct attributes in Symplify.
    Attributes and mapping are managed on Account settings > (Audience) Recipient attributes.
    If a new fileheader-column is sent to Symplify that hasn’t been added before, Symplify will automatically create the new attribute. This is called Magic mapping.

Symplify support Secure Copy over SSH. This file transfer method provides greater security.

Preparing your file

Selecting data to be included in the file

What data that should be included in your import file depends on what data you wish to either be presented in the your sendouts or what data should be used for segmentation.

The more data you import, the more segmentation can be made but select relevant data that will help you to create smart communication. By importing non-relevant data, it may get harder to keep track of your core data and may also cause unnecessary import and export time.

One requirement is that the recipients in your import file are identified by an ID, as OriginalID. This can be a primary key from a database or anything else that uniquely identify each recipient. The result of this is that the simplest import file must at least contain two columns (OriginalID and email address/phone number).

​When no suitable ID for recipients can be found you can choose to identify recipients by their email address or phone number.
Please note that this is an inferior method and by doing so recipients that change their email address or phone number will be treated as new recipients in the system.

 

File format

Data uploaded to Symplify for import must conform to the following specifications:

  • Data must be sent as an unformatted plain text file.
  • You must not surround data fields in quotes or double quotes.
  • The first row in the file will be used as a mapping matrix to insert the file data into the correct attribute* in Symplify.
  • New line must be represented by ASCII compatible characters (i.e. LF, CR or CR+LF).
  • The only allowed ASCII control chars are horizontal tab, space, carriage return and line feed.
  • Column header names may only contain ASCII alphanumeric characters (A-Z a-z 0-9), e.g. no space or underscore.
  • Mobile numbers must start with 00[country code] or +[country code], e.g. 0046 or +46 (where 46 is country code for Sweden). **
    Please read more detailed info about SMS number formatting here.
  • Allowed file extensions are .txt and .csv (+.zip if compressed).
  • The following file encodings are supported:
    • UTF-8
    • UTF-16
    • ISO-8859-1
    • ANSI
  • The file must be structured in a table format so that each line represents a table row with fields separated by a delimiter***. We recommend tab or triple hash (###) as delimiter.
  • The file must not include byte order mark (BOM).

 

* To map the correct column header in the file to a attribute in Symplify see to that correct information is entered in the mapping filed in Symplify. You can get an attribute and file header overview for your existing properties in Symplify on Account settings > (Audience) Recipient attributes.

** Symplify updates country codes after import to numbers only (e.g. +46 will be visible as 46).

*** Choose a delimiter that won’t be represented as a data character in your file since this will result in offset data. Avoid delimiters such as “,” (comma), “.” (dot), “:” (colon) or “;” (semicolon).

 

Collection fields

To reduce the number of fields it’s sometimes convenient to merge a collection of attributes to a single field. This can greatly reduce the time spent creating filters and managing attribute in Symplify.

To create a Collection field you will have to select a new set of delimiters that’s different from your normal field separator. The data is written to the Collection field delimited by this new separator according to the following specifications:

  • Each value in the Collection field must start with the Collection field start delimiter
  • Each value in the Collection field must end with the Collection end field delimiter

We recommend using curly brackets, parentheses or angled brackets as delimiters. See section Example layouts for more information.

PLEASE NOTE: Data in a collection field can with great benefit be used for segmentation but is not suited to be presented with placeholders in an email since you have several values.

 

Calendar and time fields

Date and time values are organized from the most to the least significant: year, month, day, hour, minute, second, and fraction of second.

Each date and time value has a fixed number of digits that must be padded with leading zeros.

Representations can be done in one of two formats: a basic format with a minimal number of separators or an extended format with separators added to enhance human readability. The separator used between date values (year, month and day) is the hyphen, while the colon is used as the separator between time values (hours, minutes, and seconds).

The time zone used in Symplify is Central European Time (CET).

Calendar dates

Symplify uses all-numeric data notation [yyyy]-[MM]-[dd]. A four-digit year [yyyy] is used to avoid the year 2000 problem. [MM] indicates a two-digit month of the year, 01 through 12. [dd] indicates a two-digit day of that month, 01 through 31.

For example, “the 5th of April 2016” is represented as “2016-04-05”.

Time

Symplify uses the 24-hour clock system. The accepted format is [hh]:[mm]:[ss].

It is acceptable to omit lower order time elements for reduced accuracy.

​Date and time examples:

2018-04-05 13:37

2019-12-11 20:00:00

 

Example layouts

Here is a simple example file layout with three columns (OriginalID, name and email address).
The first row is a header row and indicates how the file is organized. The delimiter chosen for this layout is tab.

OriginalID<tab>firstname<tab>emailaddress
001<tab>paul<tab>paul.simon@example.com
002<tab>aretha<tab>a.franklin@example.com
003<tab>bruce<tab>the_boss@example.com
004<tab>madonna<tab>queen@example.com

 

The example below is a layout using Collection fields with tab as field delimiter and curly brackets as collection delimiter:

OriginalID<tab>firstname<tab>emailaddress<tab>categories
001<tab>paul<tab>paul.simon@example.com<tab>{clothing}{kitchen}
002<tab>aretha<tab>a.franklin@example.com<tab>{games}{music}{home}
003<tab>bruce<tab>the_boss@example.com<tab>{health}{music}{movies}
004<tab>madonna<tab>queen@example.com<tab>{games}{clothing}{kids}

 

Archiving

As soon as a Schedule Import is started it will rename the file to filename.archive.timestamp (timestamp is when the import started), example SE_CRM.csv.archive.[timestamp].

Server details

You can use both sFTP and FTP protocols on their standard ports with the credentials obtained from Symplify support. The internet hostname of our file server is storage.carmamail.com.

If you have any questions regarding the different file transfer methods please contact Symplify support. We recommend our clients to use sFTP since this will encrypt the data when transferred to Symplify.

 

Scheduled Import setup in Symplify

If you are an Admin user on your account you are able to setup Scheduled Imports.

Find your way to Account settings > (Integration) Scheduled Imports.

To get the import running you need to, in Symplify, enter information about the import, when the import should run and where Symplify will find the file.

To create a new import, click Create new and select Import.

Name your import and select which list you want to update.

Select your file delimiter in the dropdown menu.

Select if you want your import to replace the current list or only add data.

 

Schedule

En programmant votre travail, vous indiquez à Symplify quand importer votre fichier*. Vous pouvez choisir d'exécuter l'importation tous les jours ou à des jours précis.

*Symplify se réserve le droit de modifier l'heure de l'importation.

 

File settings

The last setting you need to enter is where the file is to be found: path, username and password information to the FTP and the column delimiter.

​The filename should be a fixed name, for example SE_CRM.csv. Enter the name and path in the field File name format.
If you save the file in the root of your FTP account you don’t need to type any path except the filename. But if you save the file in a subfolder enter the exact path to the file: CRM/ SE_CRM.csv.

​Use the supplied login credentials to connect to the server and upload the file to the root of your home folder. Allowed characters for file names are “A–Z 0–9 – _”.

To speed up the import process it is possible to compress the import files with ZIP. If multiple files are to be delivered, each file must be compressed into a separate ZIP file.

IMPORTANT: Don’t start an upload of a new file until the previous file has been archived (and renamed) since this will risk that you overwrite a file that hasn’t yet been processed by Schedule Imports.

 

Verifying the data

When the setup is completed you are responsible to verify that your data is represented correctly in Symplify. This can easily be done by exporting all attribute values from the list in Symplify and analyse it.

 

Scheduled import multifile

Several files can be imported to the same list in Symplify with additional data. This is common if a customer has several systems containing different data that should be imported to the same list. The solution is based on a master file with child files adding data to the list.

 

Naming convention multifile

The files in a multifile import must conform to the following specifications:

Filename

The master file should be named = [_]_Master.txt*. This file contains the original ID’s of active recipients and, if any, property updates. The master file sets the active recipients on the list. If a recipient in a child file is not included in the master file, the recipient will be updated with the new data but not activated.

There are two different child solutions. The child file either contains the data that should be added to the recipients in the file (A) or the filename contains the data that should be set on the recipients in the file (B).

A. This solution is used when the import data is in the file and should be added with an import. The import will use the row-header in the file to map the data to the correct attribute in Symplify. The file must contain original ID and the properties you wish to update.

Filename: [_]_Child<numeric id if several child files will be sent>.txt
Example
: SE_CRM_Child1.txt.

B. This solution is used when one specific recipient attribute should be updated with a value given in the filename. The only data aloud in the file is the original ID for each recipient who should be updated.
Filename: [_]_Child_<row-header>_<propvalue>.txt
Example: SE_CRM _Child_vip_YES.txt
(where SE_CRM is the name of the import, Child defines the child file, vip is the property that should be updated and YES is the new property value).

* [_] = is dynamic, file format can be .txt

For the child file to be found it needs to be placed in the same folder as the master file and it must be in place before the scheduled import start. The child file also needs to have the same encoding and delimiter as the master file.

The same property may not be updated by more than one child file.

PLEASE NOTE: It´s not possible to include any of the following Symplify attributes with the child files: city, country, emailaddress, firstname, lastname, middlename, mobilenumber, sex, title, zipcode

A child file import will clear the previously set values in the specific attributes.

 

File examples:

  • Master file: SE_CRM_Master.txt (contains original ID; email; sex; zip)
    • Child file A: SE_CRM_Child1.txt (contains several properties that should be added on each recipient; original ID, value and level)
      • Child file B: SE_CRM_Child_vip_YES.txt (only contains original ID)

If you add a new file, Symplify will automatically include it in the next import.

With the above setup the properties that will be imported are:

  • Original ID (Master file)
  • Email (Master file)
  • Sex (Master file)
  • Zip (Master file)
  • Value (Child file A)
  • Level (Child file A)
  • Vip (Child file B)

 Master file with child update of recipients

In the case a child file contains a recipient that isn’t represented in the master file the recipient will not be active after the import.

Original ID overview

SE_CRM_Master.txt

SE_CRM_Child_vip_YES

SE_CRM_Child1.txt

Was this article helpful?
0 out of 0 found this helpful