CSV is harder than splitting on commas

Almost every CSV bug comes from the same assumption: that a line is a row and a comma is a separator. Neither is reliably true.

A comma inside a value

RFC 4180 allows any field to be wrapped in double quotes, and a quoted field may contain the delimiter. This is the single most common real-world case, because names and addresses contain commas.

name,city
"Doe, Jane",Pune

Split that second line on commas and you get three fields where the header declares two. The damage is not limited to the one row: every column from that point on is shifted by one, so a city lands in an email column and nothing complains.

A newline inside a value

A quoted field may also contain line breaks. A single record can span several physical lines, which means reading a CSV line by line is wrong in the same way splitting on commas is wrong.

note,id
"line one
line two",1

Any parser that starts with readLines() will report two records here, the second one malformed. The format has to be scanned character by character, tracking whether the scanner is currently inside quotes — there is no shortcut.

A quote inside a quoted value

A literal double quote is written as two double quotes. There is no backslash escaping in CSV.

quote
"She said ""hi"""

The one that destroys data

Everything above produces a visible error eventually. This one does not.

Most converters call something like Number(value) on anything that looks numeric. That turns 007 into 7, 1.50 into 1.5, +1 into 1, and a nineteen-digit account id into a rounded approximation of itself. The file still imports. The report still renders. The zip codes are simply wrong.

There is a simple rule that avoids the entire class: only convert a value when converting it back produces the identical string.

const n = Number(value);
const safe = Number.isFinite(n) && String(n) === value;

// "42"    → 42      (String(42) === "42")
// "007"   → "007"   (String(7) !== "007")
// "1e5"   → "1e5"   (String(100000) !== "1e5")
// "1.50"  → "1.50"  (String(1.5) !== "1.50")

Four lines, and leading zeros, exponent notation, trailing zeros and long identifiers all survive.

Delimiters that are not commas

In locales where the comma is the decimal separator — much of Europe — Excel writes and expects semicolons. Tab-separated files are common in bioinformatics and in anything exported from a database console. The delimiter is a parameter, not a constant, and a file with sep=; on its first line is telling you so explicitly.

Line endings and the BOM

RFC 4180 specifies CRLF. Unix tooling writes LF. Both are everywhere, so a parser has to accept either, and the same file can contain both if it was assembled by more than one program.

Separately, files written by Excel on Windows often begin with a UTF-8 byte order mark. It is invisible, and it attaches itself to the first header name — so the first column becomes id instead of id, and every lookup by that key returns undefined. If exactly one column is mysteriously missing, this is usually why.

What to check in a parser

1. Does it handle a quoted delimiter? Test with "Doe, Jane".
2. Does it handle a quoted newline? Test with a value containing a line break.
3. Does it handle "" as an escaped quote?
4. Does it preserve 007?
5. Does it report an unterminated quote, or silently swallow the rest of the file?
6. Can you set the delimiter?

A parser that passes all six is doing the job. Most hand-rolled ones fail the first two and the fourth.

Excel is not a CSV editor

Opening a CSV in Excel and saving it again is a lossy operation, and it happens constantly because double-clicking a .csv file opens Excel by default on most machines.

• Leading zeros are stripped from anything that parses as a number.
• Long numbers are converted to scientific notation — a 16-digit card number becomes 1.23457E+15.
• Values that look like dates are converted to dates, in the local format.
• Text containing a comma is re-quoted, usually harmlessly, and sometimes not.

None of this is announced. The file opens, looks broadly right, and saves with the damage baked in. If a CSV has to survive a round trip through a spreadsheet, use the import wizard and mark the affected columns as Text — or do not use a spreadsheet.

Where CSV runs out

CSV has no types, no nesting, no schema and no encoding declaration. Every value is text until something decides otherwise, which is the root of most of the problems above. It also has no way to represent the difference between an empty string and a missing value — both are just nothing between two delimiters.

For a data exchange you control on both ends, newline-delimited JSON gives you types and nesting for the same streaming-friendly shape, and Parquet gives you types and compression for anything analytical. CSV remains the right choice for exactly one reason, which is that everything can read it.