Commas and Quotes in CSV: Why Your File Breaks and How to Fix It

CSV is a convention, not a strict standard

There is a specification — RFC 4180 — but it was written after the fact and plenty of software ignores parts of it. That is why two programs can read the same file and disagree about how many columns it has. Most CSV pain comes from this gap between what the format is assumed to be and what tools actually emit.

The quoting rules that matter

A field containing a comma, a double quote or a line break must be wrapped in double quotes. Inside such a field, a literal double quote is written twice.

So the value: She said "hello", loudly — is correctly written as a single field like this: "She said ""hello"", loudly". Split that line naively on commas and you get two broken fields instead of one correct one.

Line breaks inside fields are legal

A quoted field may contain newlines. An address block or a comment field routinely does. This is the rule that catches out the largest number of home-grown parsers, because the intuitive first step — read the file line by line, split each line on commas — is wrong the moment a record spans two lines.

A correct parser reads the file as a stream of characters and tracks whether it is currently inside a quoted field, so a newline in that state is data rather than the end of the row. Our CSV to JSON converter is built this way, which is why multi-line fields survive it intact.

Delimiters are not always commas

In locales where the comma is the decimal separator, spreadsheet software commonly writes and expects semicolon-separated files while still calling them CSV. Tab-separated files are also widespread. If every row of your file lands in a single column, the delimiter is the first thing to check — the file is probably fine and the assumption is wrong.

Encoding, and the mysterious characters at the start

Save as UTF-8 and read as UTF-8, or accented characters and anything non-Latin will arrive corrupted. Some Windows tools prepend a byte order mark to UTF-8 files, which shows up as invisible junk on the first header name and quietly breaks column matching. A parser that trims it, or an export that omits it, solves the problem.

Duplicate and empty headers

Converting CSV to JSON turns each column name into an object key, so two columns with the same header would silently overwrite each other and one column of your data would vanish without a message. Our converter renames the repeats instead — a second column called value becomes value (2) — and reports what it did, so you can see that it happened rather than discovering it later.

How to tell a bad parse from a good one

Check the row count against what you expected, and check that every row has the same number of fields. Then look specifically at the rows containing your messiest data: the addresses, the free-text notes, the names with apostrophes. If those survive, the boring rows almost certainly did too.