CSV looks simple and isn't
CSV (Comma-Separated Values) has no official type system, no schema, and — until 2005 — no formal specification at all. Every value is a string; every parser has to guess where fields, rows, and quoted text actually begin and end. Most CSV bugs come from this ambiguity, not from complex data.
What RFC 4180 actually standardizes
RFC 4180 (2005) retroactively documented the conventions most tools had already converged on:
- Records are separated by
CRLF(\r\n), though\nalone is widely accepted in practice. - Fields are separated by commas; the last field in a row has no trailing comma.
- Fields may be wrapped in double quotes; fields containing a comma, a quote, or a newline must be quoted.
- A literal double quote inside a quoted field is escaped by doubling it (
""). - All rows should have the same number of fields, though real-world files frequently violate this.
name,role,bio
Ada,Engineer,"Loves math, and machines"
"Grace, Rear Admiral",Engineer,"Said ""do it now"" a lot"
Notice the second row: Grace, Rear Admiral is quoted because it contains a comma, and the embedded quote around do it now is escaped by doubling.
Why naive splitting breaks
The obvious approach — line.split(',') — fails the moment any field contains a comma, because the split has no concept of "inside a quoted field." A correct CSV parser must track quote state character by character: a comma inside an open quote is data, not a delimiter, and a quote followed immediately by another quote inside a quoted field is an escaped literal quote, not the end of the field. This is why hand-rolled CSV parsing is a reliable source of subtle bugs — it looks like a one-liner problem and isn't.
Delimiter variants
Not everything called "CSV" actually uses commas:
| Format | Delimiter | Common source |
|---|---|---|
| CSV | , |
Most exports, RFC 4180 |
| TSV | Tab (\t) |
Excel "Save as Tab-delimited", bioinformatics tools |
| Semicolon-CSV | ; |
European locales, where , is the decimal separator |
| Pipe-delimited | | |
Legacy enterprise/mainframe exports |
Excel's regional settings silently change the delimiter it exports by default — a file that's genuinely valid semicolon-CSV can look "broken" if you assume commas, and vice versa.
Common mistakes
- Splitting on comma without quote-awareness, breaking on any field containing one.
- Assuming every row has the same column count. Malformed or hand-edited files often don't; a robust parser should report the mismatch rather than silently shifting columns.
- Losing leading zeros. Opening a CSV in a spreadsheet app frequently strips leading zeros from numeric-looking strings (
00123→123) unless the column is explicitly formatted as text — a common source of corrupted ZIP codes and IDs. - Encoding mismatches. A CSV saved as Windows-1252 but read as UTF-8 (or vice versa) silently corrupts non-ASCII characters like accented letters or currency symbols without throwing an error.
- Trusting the file extension. A
.csvfile might actually be tab- or semicolon-delimited; always sniff the first line's delimiter rather than assuming.
FAQ
Do all CSV files use commas?
No — tab, semicolon, and pipe delimiters are all common depending on the tool and locale that generated the file; "CSV" is often used loosely to mean "delimited text."
Why do quotes matter if my data has no commas?
They don't, until someone edits the file and adds a value containing a comma, quote, or newline — a parser that ignores quoting will silently corrupt that one row later.
Is CSV a good format for nested or hierarchical data?
No — CSV is inherently flat/tabular; nested structures need a format like JSON or XML, or a workaround like flattening keys (address.city).
What causes "extra column" errors when opening a CSV?
Usually an unescaped delimiter inside a field (a comma that should have been inside quotes) or a row with a different field count than the header.
For inspecting and converting structured data alongside CSV exports, the JSON Toolkit handles validation, formatting, and JSON↔object conversions entirely in your browser.