Toolcedar

Why CSV Is Harder to Parse Than It Looks

Splitting on commas works until the first quoted field, and CSV has no real standard to fall back on. The cases that break naive parsers.

· 2 min read

Everyone writes the same first version: split each line on commas. It works on the example file and fails on the first real export, because a CSV field is allowed to contain the delimiter, the line break and the quote character itself. None of those survive a split, and none of them are rare.

There is no standard, only a description

RFC 4180 exists, but it was published years after CSV was already everywhere and it documents common practice rather than defining a format anyone must follow. Real files disagree about delimiters, quoting, line endings and encoding, and every one of those files is valid CSV in the only sense that matters: some program wrote it and expects some other program to read it.

name,note
"Hopper, Grace","She said ""compilers"" and meant it"

Splitting on commas finds four fields. There are two.

The three cases that break naive parsers

A quoted field may contain a comma, which defeats splitting on the delimiter. It may contain a newline, which defeats reading the file line by line — the single most common bug, because it only appears once someone types a paragraph into a spreadsheet cell. And a literal quote is escaped by doubling it, which defeats any attempt to strip quotes with a simple replace.

  • Delimiter inside quotes: a field is not bounded by the next comma.
  • Newline inside quotes: a record is not bounded by the next line.
  • Doubled quotes: a quote is not always a delimiter.

Together these mean a correct reader has to walk the file character by character, tracking whether it is currently inside a quoted field. That is a small state machine rather than a call to split, and it is the difference between a parser that works on your sample and one that works on your data.

The delimiter does not delimit. That is the whole difficulty in one sentence.

The delimiter is not always a comma

In locales where the comma is the decimal separator, spreadsheet software exports semicolons instead, which is why a file that opens perfectly for a colleague arrives as one column for you. Tabs are also common. Guessing the delimiter by counting candidates in the header line works surprisingly often, but offering the choice is more honest than guessing silently.

Everything is a string, and that is a feature

CSV has no type system, so any parser that infers types is guessing. The guesses fail in specific and damaging ways: a postcode with a leading zero loses it, a long identifier becomes a rounded float, and a value like 1-2 can be read as a date. Keeping values as written and converting deliberately, where you know what the column means, is almost always the safer default.

Frequently asked questions

Should I use a library rather than writing a parser?
For production work, yes. The quoting rules are small enough to implement correctly but large enough that most hand-written attempts miss the newline-inside-quotes case, which fails only on real data.
Why does my file open as one column in a spreadsheet?
Almost always a delimiter mismatch, usually semicolons in a locale where the application expects commas. The import dialog lets you set the delimiter explicitly rather than relying on detection.
What is the byte order mark at the start of my file?
A few invisible bytes some tools write to mark the encoding as UTF-8. It becomes part of the first column name if the reader does not strip it, which is why the first header sometimes appears not to match.

Related reading