Short answer: a delimiter inside double quotes is data, not a separator, and a double quote inside a quoted field is written twice. A parser that splits on commas and ignores those two rules loses data on any file that uses them — which is why the columns shift partway down a spreadsheet import.
CSV has no specification anyone follows, no type system, no encoding declaration, and — the one that causes the most damage — a single quoting rule that most code written to read it gets wrong.
If you have ever imported a spreadsheet and found the columns shifted by one from row 47 onwards, this is almost certainly why.
The rule: a comma inside quotes is not a separator
The whole standard, such as it is, comes down to this. A field may be wrapped in double quotes. Inside those quotes, a comma is an ordinary character. So this line has two fields, not three:
"Smith, John",42The naive implementation — line.split(",") — produces three. Every address containing a comma,
every company name written as "Acme, Inc.", every product description with a comma in it, silently shifts the
rest of that row one column to the right. And because the error only affects rows that happen to contain a
comma, it does not show up in testing on a clean sample.
The second rule, which is worse: how to put a quote inside quotes
There is no backslash escape in CSV. To include a literal double quote inside a quoted field, you write two of them:
"He said ""hello"" and left",2026-01-04That is a single field whose value is He said "hello" and left. A parser that understands the
first rule but not this one will produce He said followed by a stray hello and
and left — and will still be off by however many columns that consumed.
The third: newlines inside a field are legal
A quoted field may contain an actual line break. This is valid CSV, and it is a single row:
id,notes
7,"First line
second line"This is where line-oriented parsers fail completely. Reading the file line by line and splitting each line cannot work, because a line is not a row. The parser has to track whether it is currently inside quotes as it scans the file, character by character.
The characters that will wreck a file written by hand
| Character | What happens | How to handle it |
|---|---|---|
, | Splits the field in two | Wrap the whole field in double quotes |
" | Ends the quoted field early | Double it: "" |
| Line break | Turns one row into two | Wrap the field in double quotes |
Leading =, +, -, @ | A spreadsheet may treat the cell as a formula | Prefix with a single quote, or wrap in quotes and disable formula parsing on import |
Leading zeros: 007 | A spreadsheet converts it to the number 7 | Wrap in quotes, and import that column as text |
| Byte-order mark at the start of the file | The first column header does not match, because it begins with an invisible character | Write UTF-8 without a BOM, or strip it on read |
That fourth row is worth pausing on. Spreadsheet applications historically interpreted any cell beginning with
= as a formula, which means a CSV of user-supplied data can be a code execution vector against
whoever opens it. If you are generating CSV from data you did not author, sanitise those leading characters.
Encoding, briefly
CSV has no way to declare its encoding, so readers guess. In practice:
- Write UTF-8. It is the only sensible default.
- Prefer UTF-8 without a byte-order mark. The BOM is invisible but real, and it is the cause of "why does my first column header have a weird character in front of it".
- Expect legacy files to be in something else. Data exported from older Windows software is
frequently Windows-1252 or GBK. A file that displays as mojibake —
éwhere you expectedé— is UTF-8 bytes being read as Windows-1252, or the reverse.
Why not just use TSV or semicolons?
Neither fixes the problem, it just relocates it. Any delimiter you choose can appear inside a field, so you need quoting either way. Tab-separated files are harder to debug because the delimiter is invisible, and they break the moment a field contains a tab, which is more common in pasted data than you would expect.
Semicolon-delimited files are common in parts of Europe precisely because the comma is the decimal separator there — it is a locale workaround, not a better format. If you receive one, the CSV to JSON converter detects the delimiter, but it is worth setting it explicitly when you know.
The rules a correct parser follows
- Scan character by character. Do not split on lines or on delimiters.
- Outside quotes, a delimiter ends the field; a newline ends the row; a
"at the start of a field opens quoting. - Inside quotes, everything is literal except
"", which means one double quote. - A quote character appearing mid-field, outside quotes, is usually a malformed file — but the pragmatic choice most parsers make is to keep it as a literal character rather than fail.
- Trim nothing unless the file is known to be padded. Leading and trailing spaces in a quoted field are usually significant.
That is perhaps thirty lines of code in most languages, and it is thirty lines that a great deal of production code does not have. If you are writing an importer, this is the part worth getting right and worth writing a test for — specifically a test with a field containing a comma, a field containing an escaped quote, and a field containing a newline. Those three cases cover nearly every real-world corruption.
Checking a file without uploading it
CSV exports from business systems are usually the most sensitive files a person handles: customer lists, payroll data, transaction records. There is no reason for any of it to go through a server just to be reformatted. The converter here parses in the page, handles quoted fields and embedded newlines correctly, and shows you the JSON so you can see exactly where the columns landed. Nothing is transmitted.