Why CSV is more complicated than it seems

The CSV (Comma-Separated Values) format is not exactly new. It is one of the most widely used data formats. You can find it everywhere: database exports, Excel files, ERP, CRM, BI tools, APIs...

At first glance, it looks extremely simple: one line represents a record, and columns are separated by commas. However, as soon as CSV files are exchanged between different applications, problems start to appear.

In this article, we will see why CSV is much less trivial than it seems.

What is a CSV file?

A CSV file is a text file where each line represents a record.

Example:

id,name,country
1,Cyril,France
2,Sarah,Germany
3,Justin,Canada

The first line usually contains the column names, although this is optional.

Unlike JSON or XML, CSV does not describe the structure of the data. It only contains lines of text separated by a delimiter.

There is not one CSV, but many CSVs

This is probably the biggest source of confusion. Unlike JSON, there is no single CSV specification followed by all software. A RFC exists (RFC 4180), but many tools deviate from it (That's really unfortunate...).

As a result, two perfectly valid CSV files may not be compatible.

The separator is not always a comma

The name Comma-Separated Values is misleading. In many European countries, especially in my home country France, the comma is used as the decimal separator. To avoid ambiguity, many software tools use a semicolon (;) as the separator instead.

For example:

id;name;salary
1;Cyril;2500,50
2;Sarah;3200,75

On the other hand, English-speaking software usually uses a comma:

id,name,salary
1,Cyril,2500.50
2,Sarah,3200.75

Importing a file with the wrong separator quickly results in completely shifted columns.

Fields can contain separators

What happens if a value contains a comma itself?

For example:

Paris, France

If this value is written without protection:

1,Paris, France

The parser will see three columns instead of two! The solution is to surround the field with quotes.

1,"Paris, France"

The same rules apply when using a ; separator.

Line breaks make things more complicated

A CSV field can also contain a line break.

Example:

id,comment
1,"First line
Second line"

Visually, one might think that the file contains two records. In reality, it only contains one. This is why simply reading a CSV line by line with a basic split("\n") is rarely enough.

Using \n inside the data can be a practical solution, but it is not a solution defined by RFC 4180 to represent a line break inside a CSV field. Some CSV tools also use it to avoid multiline fields.

Quotes must be escaped

What happens if a value already contains quotes?

For example:

He said "Hello"

In CSV, quotes are simply doubled.

1,"He said ""Hello"""

It is not intuitive, but this is the expected behavior for most parsers.

Encoding is not guaranteed

Another difficulty: CSV does not specify its encoding.

A file can be:

• UTF-8;
• UTF-8 with BOM;
• ISO-8859-1;
• Windows-1252;
• or many others.

This is often the reason why accented characters appear as é, è or ’.

Before parsing a CSV file, it is therefore preferable to know its encoding.

All fields are text

CSV does not know about types.

These two values:

42

and

0042

are simply strings.

Likewise, a date, a boolean, or a decimal number are never identified as such.

It is up to the program reading the file to decide how to interpret each column.

Variable columns across rows

CSV does not require every row to have the same number of columns.

Example:

id,name,country
1,Cyril,France
2,Sarah
3,Justin,Canada,Extra

What should the parser do?

• reject the file?
• create an empty value?
• ignore the additional column?
• shift the columns?

Each tool may handle this situation differently.

Empty values and null values

CSV does not really distinguish between:

id,name
1,
2,""
3,NULL

These three values could mean:

• an empty string;
• a missing value;
• an unknown value;
• the literal text "NULL".

This is a common issue when importing or exporting data from databases. It can sometimes be difficult to know how it will be interpreted…

Excel adds its own layer of complexity

Many people think CSV is an Excel format. In reality, Excel only reads and writes CSV files.

Depending on the language, software version, and operating system, Excel can:

• choose a different separator;
• modify date formats;
• remove leading zeros from strings;
• automatically convert identifiers into scientific notation.

For example:

000123456

can become:

123456

or even:

1.23456E+05

These automatic conversions are a frequent source of errors. Thanks, Excel...

When should you use CSV?

CSV remains an excellent choice when dealing with tabular data.

It is particularly suitable for:

• exporting relational data;
• importing data into a spreadsheet;
• exchanging data between applications;
• producing files that are easy to read.

However, as soon as data becomes hierarchical or nested, JSON is often a better fit.

For large volumes of data that need to be processed as a stream, JSONL is also an excellent alternative.

Conclusion

CSV gives the impression of being an extremely simple format that anyone can read easily. Yet separators, quotes, line breaks, encoding, and differences between software tools make processing it much more delicate than it seems.

Most CSV-related bugs come precisely from underestimating its complexity. Fortunately, most programming languages now provide robust libraries capable of correctly handling these edge cases. Phew, we're out of trouble 🙂