Relational Data onboarding for clients using Emarsys-hosted databases

You can upload your data in any of the following options:

• API: Emarsys API provides access for clients to upload their data into Emarsys servers. For optimum performance and data transfer you can use the Emarsys API, however it requires you to have some technical resources to integrate it with your application(s). For details, see Transferring data via the API.
• SFTP:  You can upload your data files into an Emarsys-hosted SFTP (Secure File Transfer Protocol) server. In this more frequently used way you can process more data (high-volume data) much faster.

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. 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.
• 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"
}]

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.