The traps hiding in CSV files

The world's simplest-looking format is also its most quietly corrupting. ← Workbench

CSV looks trivial: rows are lines, columns are separated by commas. Whole industries run on it. And yet almost everyone who has moved data around for a living has a story about a file that imported fine and turned out wrong. The simplicity is the trap — CSV is less a format than a loose convention, and every step of a CSV's life is a place where data can change without anyone noticing.

Trap 1: the quoting rules nobody reads

The standard most tools follow (RFC 4180) is small, but it has teeth. A field containing a comma, a quote or a line break must be wrapped in double quotes, and a quote inside such a field is escaped by doubling it: "Smith, ""John""" is a single field that reads Smith, "John". Plenty of hand-rolled parsers split on every comma and stop at every line — and silently shred any address field, note or JSON snippet that used those characters. If your column count starts varying row by row, this is why.

Trap 2: spreadsheet apps editing your data for you

The most expensive CSV losses happen before the file is even exported. Open a CSV in a spreadsheet and it helpfully displays values the way it thinks you want them — and saves them that way:

Long numbers become 6.5E+11, and any digits beyond the fifteenth turn into zeros — which is why account and card numbers with 16+ digits survive only if kept as text. Leading zeros vanish (00123 becomes 123). Dates get reinterpreted against the computer's regional settings, so 1/2/3 lands as January 2nd, February 1st or March 1st depending on the machine. In one famous genetics incident, roughly a fifth of the papers in one major supplement had affected gene names, converted into calendar dates and floating-point numbers (SEPT1 reading as 1-Sep) — the confusion ran so deep that the genetics community eventually renamed the affected genes (SEPTIN1) so they would survive spreadsheets.

Trap 3: encoding and the invisible BOM

A CSV claiming to be plain text still needs an encoding, and the usual failure is Müller arriving as Müller: UTF-8 bytes read as Windows-1252. The subtler version is the byte order mark — three invisible bytes some Windows tools put at the start of a UTF-8 file. Read naively, the first column of the first row suddenly has a name that looks right on screen but doesn't match anything in code ("name" vs the BOM-prefixed version). Oddly, this works in reverse too: older spreadsheet apps on Windows only detect UTF-8 correctly with a BOM. Know which side of that divide your reader is on.

Trap 4: newlines inside fields

Because quoted fields may contain line breaks, "one record per line" is only true for files that happen to be simple. A notes column with a paragraph break makes the record span three physical lines — fine for a real parser, fatal for split("\n"). Relatedly, files from Windows end lines with CRLF and Unix tools with LF; most readers cope, but the stray \r has ended up glued to more than one last column's values.

Trap 5: delimiter drift

"CSV" is often not comma-separated at all. A German or Dutch spreadsheet exports semicolons, because the comma is already the decimal separator there. Tabs (.tsv), pipes and other characters circulate too. Before parsing an unfamiliar file, look at the first two lines in a plain text editor and count which character actually divides the fields — or count fields per row for each candidate delimiter and pick the one that's consistent.

Trap 6: everything is a string

CSV has no type system. 007, "007", 7 and 7.0 are four different files that all mean roughly "seven" — and a value that looks like a number (a postal code, a phone number with a leading +) will be silently reinterpreted by whatever imports it. If a column must stay text, it has to stay text at every step of the journey; there is no place in the file itself to say so.

What actually helps

A few habits catch most of this: open suspicious files in a text editor instead of a spreadsheet; quote every field when writing if any field could ever need it; keep numbers you care about out of spreadsheets or formatted as text; and prefer a real parser over string splitting — most languages have one that handles quoting, BOMs and delimiters correctly. Round-trip testing pays for itself: parse a file, write it back out, and diff the two — anything that changed silently is a bug you found early.

You can try this right here: the CSV ⇄ JSON converter in the Workbench parses RFC 4180 properly (quoted commas, doubled quotes, embedded newlines), sniffs the delimiter, and reports ragged rows instead of hiding them — everything in your browser, nothing uploaded.