Data is transferred in and out of Emarsys in comma-separated file format (.csv). In order to ensure that the right values arrive in the right database fields, here is an overview of the conventions that we use.

Since the product catalog and sales data files used by Web Extend for Predict and Smart Insight must follow the conventions outlined below, we strongly advise you to do this for your regular contact data imports and exports as well, as these are standard for the industry and will also enable you to take advantage of our import file validation feature. However, if you cannot do this, there are a number of exceptions supported by contact data files.

Contents

Introduction

All incoming data files must be UTF-8 Unicode encoded, .csv (comma separated values) files.

If you are using Predict or Smart Insight, you may also want to confirm with Emarsys Support that your data structure matches the requirements of your particular configuration, in particular when relating to custom fields.

The most important rule for uploading data is that the format of all uploaded files must remain consistent. Once a format has been agreed upon, this should not subsequently be changed. If you do, please contact Emarsys Support before the changes take effect.

CSV syntax

Here are our basic rules for .csv files:

  • The field separator is always the comma.
  • Values which contain commas can be quoted using double quotes.
  • No fields may contain new line characters (not even if they are quoted).
  • Quotes can be escaped within fields by doubling them: "".
  • The first line must be the header row containing the field names; subsequent lines contain fields in the order defined in the header.
  • Redundant (starting and trailing) white spaces are automatically removed unless they are quoted (e.g. 1, 2, 3 is the same as 1,2,3).

Encoding

In order to maximize foreign language support and to ensure that content is correctly displayed, Emarsys insists on UTF-8 encoding as a standard.

For floats, you must always use a full stop/period (.) as the decimal separator, even for locales which use a different character such as the comma.

Changing the encoding

Most standard texts editors offer functionality to change the encoding of your content, or will let you select the encoding of a file when you save it (for example as part of the Save As dialog. For more information on encoding, please see Encoding an Import File to UTF-8.

URL encoding

URLs (for example in the link field of the product catalog), should be properly URL encoded. Characters such as spaces (`) or brackets ([,]`) should not be present in these values.

URLs should include the protocol, which can be http://, https:// or the protocol-relative //.

Field with multiple values

Some product catalog fields, such as author, may have several values, which should be separated with the pipe (|) character, for example:

item, title, author
BOOK0012, Best essays of 1908, Charles Dickens | Oscar Wilde | Douglas Adams

Important note: Smart Insight only support multiple values in one field: category (see below). For all other fields, multiple values separated by pipes will stored by Smart Insight as a single string, for example: Charles Dickens|Oscar Wilde|Douglas Adams.

Category format

The product category path is a series of increasingly fine-grained sets which describe the product. For example, The Hitchhiker’s Guide to the Galaxy coud be categorized as Literature > Fiction > Sci-Fi. Note that elements in the category path are separated by the > character in the product catalog file. In a bookstore, all products would be in the Literature category. If there is a category which includes all items, we call it the root category. The category field should not include the root.

Some items may belong to multiple categories. For example, The Hitchhiker’s Guide could also be categorized as Literature > British literature > Contemporary. Multiple categories should be separated by the pipe, |.

The correct value for the catalog field in this example would be:

Fiction > Sci-Fi | British literature > Contemporary

Note: There is no limit to the number of category hierarchies in Predict, but Smart Insight, which also uses the product catalog, accepts only the top five and ignores the rest. For more details on managing catogories in Predict, see Product Categories.

Date format

When you are using date fields, the following formats are supported by Emarsys for importing and exporting data:

Date format Data type
YYYY-MM-DD Contact, Sales and Product data.
DD.MM.YYYY Contact data only.
DD/MM/YYYY Contact data only.
MM/DD/YYYY Contact data only.

Timestamp

When you are using a timestamp field, for example to identify individual purchases, you must use the following UTC format:

  • YYYY-MM-DDTHH:MM:SSZ

Quoting

If the data itself contains the separator character, then you need to indicate to the import mechanism that this is part of the field value, and it does not use it as an indication to start a new data row. To do this you must surround the entire field value with double quotes. In the example below, the separator ‘,’ appears in the text of the second field value. Therefore when this field comes to be imported, the entire value is enclosed in quotes.

Value in Database Value in CSV Comments
Notebook computers …,Notebook computers,… No quoting required.
Laptops, notebooks …,"Laptops, notebooks",… Quotes required.

Escaping

Similar to the separator character, if the imported text contains the quote character, this must also be indicated to the import mechanism, this time by ‘escaping’ it. Escaping is done by adding another quote character to the existing one and using quotes to surround the entire value. In the first example below, the quote character is used in the text as an abbreviation for the measurement ‘inch’. In the second example, quotes are used not only to escape the ‘inch’ symbol but also to indicate that the value includes the separator ‘,’ as well:

Value in Database Value in CSV Comments
21″ monitors …,"21"" monitors",… Quote has been escaped.
21″ monitors, flat screens …,"21"" monitors, flat screens",… Quotes and separators are used.

Note:  Both the separator and the quote characters can be customized, but in each case the character used should be chosen with care.

Empty Values

Fields with no values must be still exported as an empty value without any quoting, so that the structure of the data is not affected.

Example

Value in Database Value in CSV Comments
NULL …,,… Import recognises an empty field.

Sample files

Here are some sample files with a selection of typical fields which you can download and adapt for your own use: