CSV is not a format: quoting, delimiters and bugs

Why “comma-separated” is not enough

If a value contains a comma, a naive parser splits it into two fields. If it contains a newline, the parser treats one record as two. If it contains a quote, everything after it may be read the wrong way. The whole of CSV quoting exists to solve those three problems, and every disagreement between tools is a disagreement about how to solve them.

RFC 4180 is the closest thing to a standard. It is explicit that it is informational, not a specification anyone must follow — which is exactly why two tools can both claim CSV support and disagree on the same file.

The RFC 4180 rules that matter

1. A field containing a comma, a double quote, or a line break must be enclosed in double quotes.
2. A double quote inside a quoted field is escaped by doubling it: say ""hi"" is the value say "hi".
3. Records are separated by CRLF. Most parsers accept LF and many accept CR alone.
4. The final record may or may not have a trailing line break — both are valid.
5. Every record should have the same number of fields, but real files frequently do not.

name,note
Ann,"Lives in Paris, France"
Bob,"Said ""hello"" twice"
Cara,"Line one
Line two"

Row 2 has a comma inside the value, row 3 has escaped quotes, row 4 spans two physical lines. All three are one field, and all three break a parser that splits on commas and newlines.

Where real files break the rules

Detect the delimiter before parsing

Guessing “comma” is the first mistake. A reliable heuristic is to count each candidate delimiter per line outside quotes and pick the one whose count is both non-zero and consistent across lines. A semicolon in prose appears irregularly; a delimiter appears once per record boundary. Tabs are almost always delimiters and almost never data.

Consistency is the signal, not frequency. A file with commas inside a free-text column may have more commas per line than the semicolons that actually separate fields, so frequency alone picks wrong.

BOM: three bytes that break the first header

Some tools write UTF-8 with a byte-order mark (EF BB BF) at the start. A compliant reader strips it; many do not, and the first column name becomes id, so a lookup for id misses. If your code does rows[0][0] === 'id' and it fails on one file only, check for a BOM.

The reverse problem is real too: Excel on Windows often expects a BOM to recognise UTF-8. That is why “export for Excel” sometimes deliberately adds one.

Injection: the CSV-specific security bug

A spreadsheet opens a .csv and evaluates any cell beginning with =, +, - or @ as a formula. If your export includes user-supplied text, a value like =HYPERLINK("http://attacker.example","click") becomes an active link, and older Excel builds would execute DDE payloads. The mitigation is to prefix such cells with a single quote or a leading space on export — and to document that you did, because it changes the value.

This is not hypothetical: it is a recurring finding in security reviews of report exports, and it is invisible in every plain-text view of the file.

Parsing safely in practice

1. Sniff the encoding (BOM, or UTF-8 validity), then the delimiter, then parse.
2. Handle quotes statefully — a machine that tracks “inside quotes” across commas and newlines. Do not split on a regex.
3. Decide what to do with ragged rows: pad with empty fields, skip, or fail loudly. Silently shifting columns is the worst option.
4. Keep every field a string until you know its type. Parsing to numbers at read time is what eats leading zeros and long ids.
5. Neutralise formula prefixes if the file will be opened in a spreadsheet.

Writing a parser: the state machine

A correct CSV parser is a small state machine, not a split. The states are “at field start”, “inside an unquoted field”, “inside a quoted field”, and “just saw a quote inside a quoted field”. The last one is the subtle one: a quote inside a quoted field is either an escaped quote (if the next character is another quote) or the end of the quoted field (otherwise).

for each character:
  inside quotes?
    yes: quote followed by quote  → emit one quote, stay inside
         quote otherwise          → leave quoted mode
         anything else            → emit verbatim (including , and newline)
    no:  comma                    → end field
         newline                  → end record
         quote at field start     → enter quoted mode
         anything else            → emit verbatim

Two consequences fall out of this that a split-based parser cannot reproduce. A comma inside quotes never ends a field, and a newline inside quotes does not end a record — which is why a single logical record can span several physical lines. Any tool that reports “line 42” while parsing CSV is really reporting a physical line, not a record.

Excel-specific behaviour worth knowing

Excel is the most common consumer, and it has habits worth anticipating when you export:

• It sniffs the delimiter from the first few lines and can guess wrong on a file whose first rows contain no commas in the data.
• It treats a leading =, +, - or @ as a formula, which is the injection issue above.
• It strips leading zeros from values it decides are numbers, so a zip code 01234 becomes 1234.
• It parses long digit strings into scientific notation once they exceed about 15 significant digits, which corrupts ids.
• It expects CRLF on Windows and a BOM for UTF-8; a plain LF, no-BOM file can open with the whole file in one column.

None of these are corruption caused by your export — they are Excel applying its own type inference. The mitigation is to write values that cannot be misread: quote identifiers, keep ids as text with a leading apostrophe or as a string the consumer will not coerce, and be explicit about encoding.

Streaming large files

A CSV of a million rows does not fit comfortably in memory as an array of arrays. Because CSV is line-oriented (except inside quotes), a streaming parser can emit one record at a time — but only if it carries the quoted-state across chunk boundaries. That is the detail that makes a naive line-splitter fail on real files: the chunk boundary lands inside a quoted field and the parser treats the newline as a record separator.

If you are writing one, the rule is: never assume a chunk ends at a record boundary. Keep the state between chunks, and only emit records when you have seen a newline outside quotes.

A checklist before you ship an export

1. Quote every field that can contain a comma, quote or newline — or quote everything, which is simpler and always valid.
2. Normalise line endings to CRLF if the file is meant for Excel, LF otherwise.
3. Decide on encoding and write it deliberately: UTF-8 with or without BOM, documented.
4. Keep identifiers as text; do not let the consumer guess a type.
5. Neutralise formula prefixes if the file may be opened in a spreadsheet.
6. Test with a value containing a comma, a quote, a newline, and a non-ASCII character — all four in one row, which is the case that fails.

Check it yourself

The CSV viewer detects the delimiter and header row, renders a sortable table and reports ragged rows rather than hiding them. To convert, CSV to JSON applies the same parser, and JSON to table goes the other way.