Not all systems have APIs or JSON data export tools allowing for simple data handling. So if you have data from spreadsheets, competitions, forms, or something as simple as a purchased list of customer records, CSV imports might be a suitable backup to get data into your CDXP.

With that in mind, the most common use case for CSV data transfers into a CDXP is for customer data, which leads us to an example for a fictional competition:

[.code] email, marketing opt in, favorite destination
fake@fake.com, true, New York
fake2@fake2.com, false, London
[.code]

Convert your CSV into an NDJSON file

The recommended option! The quickest and easiest way to get your customer profile data into your Hub is to convert it to an NDJSON file using our [.code]csv_to_ndjson[.code] schema. It's specifically intended to be used when importing customer profile custom fields and can't be used for commerce transaction records. If you have a spreadsheet you want to convert into a NDJSON, follow the detailed instructions here. The script provided will map the data, specifically the headers in the CSV, so it can be parsed using [.code]lexer_schema[.code].

For example, your CSV data might look something like this:

This will be converted into the following:

{"link": {"customer_id": "1", "system_name": null}, "email": null, "email_sha256": null, "mobile": null, "customer_id": null, "custom_fields": {"loyalty_points": "600"}, "first_name": null, "last_name": null, "gender": null, "date_of_birth": null, "country": null, "state": null, "city": null, "postcode": null, "zip": null, "employee_flag": null, "customer_type": null, "address_1": null, "address_2": null}

Once converted you can upload the data using the our File Upload API.

If you don't have the capability to do this, you can still send through a CSV using the steps below.

Steps to provide CSV data to Lexer

1. Review the columns of your CSV data, are they well labeled? Is there any unnecessary data?
   [.in]- Feel free to reach out to your Account Manager for help in validating the use cases against data provided.[.in]
2. Generate your CSV file, and check that the data is clean and matches the formatting rules below.
3. Name the file as per the naming convention below.
4. Determine the process for delivering the data.[.in]
   [.in]- SFTP/S3: Upload using SFTP or Upload using S3[.in]
   [.in]- File Uploader: Read File Upload API for specifics.[.in]
   [.in]- Custom: Chat with your Lexer contact.
5. Transfer the data.
6. Notify with your Lexer contact.

File naming

Files must be named using the following format: 

[.code]{content_descriptor}-{date}-{version}.csv[.code] 

This format consists of the following three parts:

1. Content Descriptor: A short description of the files contents, separated by an underscore `_`.
2. Date: An ISO8601 date for the file, reflecting the contents or date of file generation.
3. Version: An optional version identifier. We recommend using versions. This allows you to easily revert to an old file if you identify any issues with the current version.

File names are case-sensitive, and may not contain special characters other than an hyphen '-', or an underscore '_'.

File naming examples

Let’s look at an example for a fictional competition made on August 29th, 2022:

• [.code]summer_holiday_compeition_results-20220829.csv[.code]

In the case where a file might need to be overwritten with a newer version, you can add an additional suffix. This will update the file currently being used, while keeping the old file: 

• [.code]summer_holiday_compeition_results-20220829-a.csv[.code]
• [.code]summer_holiday_compeition_results-20220829-b.csv[.code]

The higher suffix will cause the newer version to be used in place of previous versions. In these examples [.code]summer_holiday_compeition_results-20220829-a.csv[.code] will be ignored, and [.code]summer_holiday_compeition_results-20220829-b.csv[.code] used.

Equally, you could simply delete the [.code]summer_holiday_compeition_results-20220829-a.csv[.code] file entirely if necessary, although this is not the recommended option.

Formatting your CSV

Column values

Column values should not exceed 1,000 characters. Any characters over this limit may be trimmed.

Double-quote values with commas

If a value contains a comma `,` it must be surrounded by double-quotes to ensure the integrity of the CSV columns. We recommend putting all values in double-quotes.
We use the standards, outlined in RFC4180, for double quotes used to enclose fields. Use a doubled double quote `""`, to indicate a double quote character in the data, and enclose this with double quotes. 

For example:

[.code]"aaa","b""bb","ccc"[.code]

Column names and order

Ensure that column names are well defined for their use for later reference.

The order of the columns does not matter, but we recommend placing identifiers in the first column to help navigate throughout the file if necessary. 

File encoding

We recommend UTF-8 (without BOM encoding).

Normalize empty values

Some rows might not contain a value for each column. Make sure that these columns are empty and do not contain placeholder values such as "null", "empty", "blank", etc.

Need some help?

If you are having any issues with this process please reach out to your Success Manager of Lexer Support.