Scheduled Import - file setup
Introduction
Carma Scheduled Imports enables you to automatically synchronize recipient data from your databases into Carma lists.
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 set up a scheduled job to once every day export a portion of these addresses and phone numbers to a text file. The text file is then placed on Carma’s FTP server.
The import function in Carma is configured to pick up the file placed on the FTP and populate a list in Carma with it’s content.
Setup overview
The setup of an Scheduled Import consists of 5 parts:
1. Compost supplies this document and FTP1 login information
2. A Carma account administrator supplies user login information to Carma
3. The customer creates a file according to the file format specifications
4. A scheduled import needs to be set up that connects a specific filename with a specific list and also handles the scheduling on how often the system will look for a new file
5. To map the correct fileheader-column in the file to a attribute in Carma. You can control that correct information is entered in the mapping filed in Carma. You find that under Account settings -> Profile attributes.
If a new fileheader-column is sent to Carma that hasn’t been added before, Carma will automatically create the new attribute.
We call it Magic mapping, you can read more about it here.
Carma support Secure Copy over SSH. This file transfer method provides greater security.
Setup in Carma
If you are an Admin user on your account you are able to setup Scheduled Imports.
You find them under Account settings -> Scheduled Imports.
Choosing fields to be included in the file
What data that should be included in the file depends on what data you wish to either be presented in the sending or what data should be used for segmenting. Select only data that is relevant to your email content and segmentation needs to minimize unnecessary export and import time.
In some cases it’s only necessary to import email addresses or phone numbers. One requirement is that the recipients in your import file are identified by an ID, in Carma we called i 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 emailaddress/phone number).
When no suitable ID for recipients can be found you can choose to identify recipients by their email address or phone number. 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 Carma 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 Carma.
– Newline 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:
o UTF-8
o UTF-16
o ISO-8859-1
o 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 Carma see to that correct information is entered in the mapping filed in Carma.
You can get a attribute and file header overview for your existing properties in Carma on Account settings -> Profile attributes.
** 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 Carma.
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 Carma is Central European Time (CET).
Calendar dates
Carma 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
Carma 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:
2013-04-05 13:37
2014-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>name <tab>emailaddress
001 <tab>paul <tab>pm@compost.se
002 <tab>lars <tab>lh@compost.se
003 <tab>vegar <tab>vl@compost.se
004 <tab>tobbe <tab>th@compost.se
The example below is a layout using Collection fields with tab as field delimiter and curly brackets as collection delimiter:
originalID name categories email
123 pm {clothing}{kitchen} pm@compost.se
345 lh {games}{music}{home} lh@compost.se
678 vl {health}{music}{movies} vl@compost.se
910 th {games}{clothing}{kids} th@compost.se
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 Carma 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 Carma support. We recommend our clients to use sFTP since this will encrypt the data when transferred to Carma.
Scheduled Import setup in Carma
To get the import running you need to, in Carma, enter information about the import, when the import should run and where Carma will find the file. These settings are found in Carma: Account settings -> Scheduled imports (-> Create new).
Job name
Name your import and select which list you want to use.
Select your file delimiter in the dropdown menu.
Schedule
By scheduling your job you tell Carma when to import your file*. You can choose to run the import every day or specific days.
*Compost reserves the right to change the time for the Import.
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.
The filename should be a fixes 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 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 Carma. This can easily be done by exporting all attribute values from the list in Carma and analyse it.