With the BlueConic customer data platform, you can exchange data files with other systems via CSV file format. When exchanging data via BlueConic Connections, you are going to need a file format that both you and BlueConic understand and interpret in the same way.
One of the most commonly used file formats is the comma-separated values (CSV) file format. It describes how to store table data in a file so it can be read in another platform without losing any information. Most applications and platforms support this format, including BlueConic. While the CSV definition is deceivingly simple, it also leaves a ton of room for ambiguity. This document describes best practices for working with CSV files, to help you avoid ambiguities.
CSV best practices
- Use commas as separators
- Use double quotes for encapsulating values
- Use the same number of columns in each row
- Use column names in the first row
- Use the UTF-8 character set
- Use a spreadsheet to export CSV
- Be consistent
For more information on the file format, see the Wikipedia page on comma-separated values (CSV).
Best practices for exchanging data via CSV files
Use commas for separators
The CSV file format allows you to pick any character as separator. Regardless, stick with the actual comma character (,) for separator, as most applications will be compatible with it out of the box.
| Use | Avoid |
|---|---|
id,email,name |
id|email|name |
Use double quotes for encapsulating values
Some of your values may contain comma characters. Surround these values with double quotes (") to avoid clashes with the use of commas for separators.
But what if your values contain double quotes characters? Well, follow these rules:
- If values are not enclosed with double quotes, then double quotes may not appear inside the fields.
- Each value that can contain a double quote, a delimited character, a carriage return/line feed, or a leading/trailing space has to be enclosed in double quotes.
- If double-quotes are used to enclose values, then a double-quote appearing inside a value must be escaped by preceding it with another double quote (
"").
| Use | Avoid |
|---|---|
id,email,city |
id,email,city |
Note: the last line of the bad example contains so-called "smart quotes". These are not the same as regular double quotes! Make sure you don't accidentally use smart quotes to encapsulate values.
Use the exact same number of columns in every row
Your file contains table data, which means every row contains the same number of columns, even if a column is empty. Make sure you use commas to separate empty values as well, and don't skip columns.
| Use | Avoid |
|---|---|
id,email,name,zip_code |
id,email,name,zip_code |
Use column names in the first row
Names are used to identify columns on the receiving end. Provide them in the first row of the CSV file.
| Use | Avoid |
|---|---|
id,email,name |
8338214,john@gnu.com,John Doe |
Use UTF-8 as character set
Characters in CSV files are interpreted as-is. So use the actual character instead of HTML entities.
| Use | Avoid |
|---|---|
id,email,comment |
id,email,comment |
Use a spreadsheet to export CSV
Exporting your data to file from a spreadsheet application like e.g. Microsoft Excel already makes sure you adhere to the best practices mentioned above.
Be consistent
If your CSV file is one in a series of many, keep it consistent over time. In other words:
- Use the exact same column names as the original CSV.
- Use the exact same column order as the original CSV.
- Use a file name similar to the original CSV; typically include a date.
| Use | Avoid |
|---|---|
|
contacts-20191124.csv id,email,name |
contacts.csv id,email,name |
|
contacts-20191125.csv id,email,name |
moreNames.csv id,name,city,email |
For the technical details of the CSV format, see RFC 4180.
Technical Deep Dive into BlueConic CSV Connections
For a technical deep dive into how BlueConic CSV connections work, see:
- CSV-Based Connections in BlueConic: Authentication Deep Dive
- CSV-Based Export Connections: Deep Dive
- CSV-Based Import Connections: Deep Dive
Learn more about the BlueConic CDP
To learn more about the BlueConic customer data platform for unifying your customer data, contact us to see how BlueConic works.