Comma-separated values (CSV) files

When exchanging data with BlueConic, 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.

Best practices

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
8338214,john@gnu.com,John Doe
1324114,jane@gnat.com,Jane Doe
id|email|name
8338214|john@gnu.com|John Doe
1324114|jane@gnat.com|Jane Doe

 

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
8338214,john@gnu.com,"Boston, MA"
1324114,jane@gnat.com,"San Francisco, CA"
4993027,boss@rad.com,"New York ""Big Apple"" NY"
9223136,josh@bar.com,"Miami, FL"
id,email,city
8338214,john@gnu.com,Boston, MA
1324114,jane@gnat.com,San Francisco\, CA
4993027,boss@rad.com,New York "Big Apple" NY
9223136,josh@bar.com,“Miami, FL”

 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 on every row

Your file contains table data, which means every row contains 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
8338214,john@gnu.com,John Doe,02111
7882860,info@blah.com,,
1324114,jane@gnat.com,Jane Doe,94111
id,email,name,zip_code
8338214,john@gnu.com,John Doe,02111
7882860,info@blah.com
1324114,jane@gnat.com,Jane Doe,94111

 

Use column names on the first row

Names are used to identify columns on the receiving end. Provide them on the first row of the CSV file.

Use Avoid
id,email,name
8338214,john@gnu.com,John Doe
1324114,jane@gnat.com,Jane Doe
8338214,john@gnu.com,John Doe
1324114,jane@gnat.com,Jane 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
8338214,john@gnu.com,¯\_(ツ)_/¯
1324114,jane@gnat.com,¿cómo estas?
id,email,comment
8338214,john@gnu.com,¯\(ツ)/¯
1324114,jane@gnat.com,¿cómo estas?

 

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
8338214,john@gnu.com,John Doe
1324114,jane@gnat.com,Jane Doe

contacts.csv

id,email,name
8338214,john@gnu.com,John Doe
1324114,jane@gnat.com,Jane Doe

contacts-20191125.csv

id,email,name
4993027,boss@rad.com,Gnu Den
9223136,josh@bar.com,Josh Bar

moreNames.csv

id,name,city,email
4993027,Gnu Den,"New York, NY",boss@rad.com
9223136,Josh Bar,"Miami, FL",josh@bar.com

 

For the technical details of the CSV format, check RFC 4180