To upload your data files to an Emarsys-hosted SFTP server, first you need to obtain the following:

• SSH public key: Generate your OpenSSH public key, for example by using PUTTYgen. Once the key is created make sure to use the OpenSSH key format. Confirm that the text in the SSH key starts with: "ssh-rsa". If the text in the SSH key starts with "BEGIN SSH2 PUBLIC KEY", it is in wrong format.
• User account and user privileges: You can ask your access from Emarsys Support. You need to attach your public key to the requesting ticket.
• User directory: Emarsys provides you a directory to store your data.

Details to access an Emarsys-hosted SFTP server :

• URL: sftp://suitesftp.emarsys.net
• Port: 22

To import your data into the correct database table first, you need to configure the parameters and connections between the CSV file and the table. In this way, we can identify which CSV to load into which table. To do this, go to Add-ons -> Relational Data -> Tables & Views tab. In this list you can see all the tables and views for all connections.

This page lists all available data tables and views. Be aware, you can import data into tables only.

1. Click the Set upload file icon at the right side of the table’s row.

2. Enable SFTP upload to the table by ticking the checkbox Allow SFTP upload.

3. In the File name field you can set the filename pattern. All files with matching filename for the given filename pattern will be loaded to the table. E.g.: If you use product* every file that starts with the word "product" will be imported to this table.

• For the File name pattern, you can use an asterisk (*) as a wildcard character.
• A File name field can  contain only alphanumeric characters and '.', '_', '-' or '*'.

4. By ticking the checkbox First row is header the first line in the file will be ignored from being imported. Be aware, we do not match the columns in the .csv file with the fields of the target table. The order of the columns in your .csv must match with the order of the fields in your table.

5. You can set different separators. It is recommended to use the same separator for each file.

6. Click Save to save your settings.

Naming your .csv data files is crucial, as the loading mode of your .csv files can be controlled with the postfix of the filename. Filenames are case-sensitive, use small letters only. E.g.: filename.[loadingmode].csv

Import modes:

• Replace: If not specified otherwise, the Replace import mode replaces the existing content of the target table with the rows in the file. e.g.: filename.csv
• In case of replacing, loading mechanism first loads your data into a temporary table. When loading data is finished successfully the old table is dropped and replaced with the new one.
• Append: All rows are inserted as new records from the .csv file to the table. E.g.: filename.append.csv
• Upsert: Inserts all new rows from the .csv file to the table. E.g.: filename.upsert.csv Existing rows are updated.  If there is a primary key in the .csv file. which has an exact match in the table, the record in the table will be updated with the data in the .csv file.

To import your data files they must meet the following criteria:

• Format is .csv (comma separated values) file.
• Encoded with UTF-8 Unicode.
• Have Windows (\r\n) or UNIX (\n) file ending. Old line endings used by classic MacOS (\r) is not supported.

The structure of the .csv data file should be as follows:

Header

• In case your .csv file contains a header, in the Upload settings dialog tick that the First row is a header to avoid the header is imported as data.
  Be aware, we do not match the columns in the .csv file with the fields of the target table. The order of the columns in the .csv must match with the order of the fields in the data table.

Primary key field(s)

• Ensure not to use a primary key twice, because only one is imported. In case of using multiple primary keys Emarsys cannot guarantee which primary key will be the final one, because the processing of the rows is not sequential.
• Ordering data by primary key(s) increases the importing process significantly.

Fields

• Fields must be separated by a character configured in the upload settings.
• Data can be enclosed within double quotation marks (").
• If your data contains a quotation mark, you have to use the data within double quotation marks (") and you need to escape it with a backslash (\). E.g.: "hello \" world" is imported as hello " world
• In case to import a backslash (\) character, you need to escape it. E.g.: hello\\world is imported as hello\world.
• To import a separator character, you must enclose the field value within double quotation marks (").
• Ensure to use the correct data type defined by the relevant column of the table, otherwise the import will cause data loss. Pay attention to the length of the data, because text fields are truncated if longer than defined. If Relation Data setup was performed by Emarsys (Fully-managed) you can check the structure of the tables in your previous agreements. Ensure to keep the agreed structure of the columns, or your data becomes inconsistent and the import might fail.
  • DATE, use 'YYYY-MM-DD' format, e.g.: '1970-01-01'
  • DATETIME or TIMESTAMP, use 'YYYY-MM-DD hh:mm:ss' e.g.: '1970-01-01 00:00:01'

After you have successfully granted access and connected to the Emarsys-hosted SFTP server you can start uploading your data files. Only the files from the SFTP root directory are imported, any other subdirectories are ignored.

1. Once you are ready with the upload, you must notify the system to proceed with the importing mechanism by uploading a control file named upload.finished. Ensure to upload the control file after all the .csv files. The control file can be empty. It prevents processing partially uploaded files.
2. The importing process starts as soon as the control file uploaded (and you configured the SFTP upload properly).
3. Wait until upload.finished disappears before you start to upload new files, otherwise you will receive “The import failed because another import was already in progress” error message.
4. Relational Data checks for new data files in regular intervals. The successfully processed files and the files left on SFTP for long time are deleted from the server.

There are two options to monitor the status of an import:

• In the SFTP History tab you can keep track of your import process. You can see the result of the import and check the number of rows affected. If an upload failed, hover over the status to see a short explanation in the appearing tooltip.

• You can also check the upload_result.json file. This file was created at the root of your SFTP folder when the upload finished. The file lists the result of the import for all affected tables in a JSON array.

Example: Results
Successful import:

[{
 "tableName": "activities",
"status": "finished",
"operation": "append",
"affectedRows": 2228
}]


Unsuccessful import:
[{
"tableName": "sale",
"status": "finished",
"operation": "append",
"error": "failure:query_timeout"
}]

You can find here a few ideas how to debug your SFTP upload.

The import process does not start

Check the following for possible reasons:

• File format and extension is .csv.
• Allow SFTP upload is enabled for the table at the Tables & Views tab in Relational Data.
• Filename follows the conventions set in the SFTP upload settings.
• Data file is uploaded to the root of your SFTP directory.
• You uploaded the control file. E.g: upload.finished

The import process has been failed

Check the reason for the error at the SFTP History tab or in the upload_result.json file. 

Check the following possible errors:

• wrong_encoding - "The import failed because of an encoding error. Please make sure the CSV file’s character encoding is UTF-8, then try to upload it again."
• non_existent_target_table - "The import failed because the table you are trying to load into was not found. Please make sure that the table exists and try again.",
• conflicting_files - "The import failed, because multiple files were targeting the same table in the database. Try importing the files separately.",
• foreign_key_constraint_failed - "The import failed because of a foreign key violation. Please make sure the uploaded CSV file is up-to-date and try again.",
• overlapping_load - "The import failed because another import was already in progress. Please try again when the previous import is finished."

The affected rows are different than expected

Check the following for possible reasons:

• The import mode was not upsert:
  • In case of an upsert the number of the affected rows can be higher than the original number of rows. In some cases the update counts as 2 updated rows.
  • A row can be updated multiple times by the input rows. It can result in a "lower-than-expected" update count.
• There were not any identical primary keys or unique constraints.
• The data types are correct. E.g.: Strings cannot be imported into numeric fields, otherwise they will be represented as 0.

When you are done, you can continue and create segments with Relational Data or personalize content with Relational Data.