CSV: the simple format with surprisingly strict rules
7 Oct 2026 · Data · 6 min read
CSV — comma-separated values — is the cockroach of data formats: unglamorous, ancient, and impossible to kill. Open any spreadsheet program, any database tool, any data-science library, and it will read and write CSV without complaint. The format looks so obvious that most people assume there is nothing to learn: values separated by commas, one row per line, done.
And then a customer's name turns out to be "O'Brien, Jr." and the whole file falls apart. CSV's simplicity is real, but it rests on a small set of quoting rules that everyone reinvents badly. This article states those rules plainly, shows where they bite, and explains why the world's most boring file format keeps causing the world's most avoidable bugs.
The basic shape
A CSV file is a table written as text. The first line is usually a header naming the columns, and every line after it is one row:
name,age,city
Ada,36,London
Grace,85,New York
Alan,41,Manchester
Three columns, three data rows. A program reading this splits each line at the commas and lines the pieces up under the headers. So far, so obvious. The trouble starts the moment a value itself contains a comma — a city like "Port of Spain", a title like "Hello, World" — because now the reader can't tell a comma inside a value from a comma between values.
The quoting rules
The fix is quoting, and the rules were eventually written down in RFC 4180 (2005). There are really only four of them:
- Any field containing a comma, a double quote, or a line break must be wrapped in double quotes. "Port of Spain" is one field; Port of Spain without quotes would be read as two.
- A double quote inside a quoted field is written as two double quotes. The name She said "hi" becomes "She said ""hi""". It looks absurd. It is correct.
- Line breaks separate records — unless they're inside quotes. A quoted field may legally span multiple lines, which means a naive "one line equals one row" reader will miscount rows on such files.
- Spaces next to commas are part of the field. a, b has a second field of " b" with a leading space, not "b". Trimming is the reader's business, not the format's.
Here is a row that uses all of them at once — a product with a comma in its name, a quote in its description, and a multi-line note:
1042,"Widget, Deluxe","A ""must-have"" gadget",19.99,"Ships in
2-3 days"
(The <br> above stands in for a real line break inside the last quoted field.) A correct parser returns five fields: 1042, Widget, Deluxe, A "must-have" gadget, 19.99, and Ships in[ newline ]2-3 days. A parser written by splitting on commas returns garbage.
What breaks CSV in practice
The rules are clear, but the real world delights in ignoring them:
- The delimiter isn't always a comma. Much of Europe uses semicolons, because the comma is the decimal separator there (19,99 means nineteen ninety-nine). A "CSV" from Berlin may split on ;. Good tools let you choose; bad ones guess.
- Encodings. CSV has no standard way to declare its character encoding. A file written on a Windows machine in one encoding and opened on a Mac in another produces the classic garbled-text mess. UTF-8 is the sane default, and Excel on Windows famously needs a byte-order mark to detect it.
- Headers are a convention, not a rule. Nothing in the format says the first row is special. Some files have headers, some don't, and some have three lines of human-readable preamble before the data starts.
- Types are fiction. Everything in CSV is text. Whether 00123 is a number, a string, or a product code with a meaningful leading zero is decided by the reader — and spreadsheet programs love to "help" by stripping those zeroes.
- Excel's "helpfulness". Open a CSV with long numeric IDs in Excel and it may convert them to scientific notation, permanently destroying data on save. This single behaviour has corrupted more datasets than any other.
CSV against the alternatives
| Format | Strengths | Weaknesses | Reach for it when… |
|---|---|---|---|
| CSV | Universal, tiny, human-readable | No types, no nesting, fragile | …moving flat tables between tools |
| JSON | Nesting, types, ubiquitous in APIs | Verbose, poor for huge tables | …the data has structure or hierarchy |
| Excel (.xlsx) | Formatting, formulas, sheets | Proprietary-ish, opaque, huge | …a human will edit and format it |
| SQLite / Parquet | Types, indexes, compression | Needs specific software | …the data is large or queried often |
CSV wins on universality and loses on almost everything else. That's a fine trade for moving a flat table from A to B — and a terrible one for anything with nested structure, where JSON or a real database belongs.
A brief history: older than the web
CSV predates the personal computer. Its roots trace to 1970s IBM Fortran, where "list-directed" input let programs read comma-separated numbers — the comma was chosen precisely because it was easy to type and easy to split on. Spreadsheets adopted it in the 1980s as the neutral interchange format, and every spreadsheet since has spoken it. Remarkably, nobody wrote down the rules until RFC 4180 in 2005 — three decades of de-facto use first. That long unstandardised childhood explains the dialects: the format was defined by what programs happened to accept, not by a specification anyone designed.
Checklist: writing CSV that survives contact with the real world
- Always quote fields that need it — or simpler, quote every text field. Over-quoting is harmless; under-quoting is fatal.
- Write UTF-8, and say so. If your audience is Excel on Windows, include the byte-order mark; otherwise leave it off.
- Put headers first and document them. A header row plus one line of documentation ("dates are ISO 8601, amounts are in cents") prevents a whole class of misreads.
- Never let a spreadsheet "clean up" your file. Open CSVs in a text editor for inspection; only open in Excel if you must, and never save from it without checking what changed.
- Use a library on both ends. Writing CSV by joining strings with commas is how quoting bugs are born. Every language has a CSV module — it exists precisely so you don't hand-roll this.
The honest caveat: never hand-roll a parser
Here is the practical moral of the whole article: do not write your own CSV parser by splitting strings on commas. It will work on your test file and fail on the first real one — on a quoted comma, a doubled quote, or a newline inside a field. Every mature language ships a proper CSV library that handles the quoting rules correctly; use it. The same goes in reverse: when writing CSV, use a library rather than concatenating strings, or your output will be the next person's bug report.
CSV endures because it is the lowest common denominator every tool agrees on. Respect its four quoting rules, expect the world's encodings and delimiters to surprise you, and let libraries do the parsing. Do that, and the cockroach of data formats will serve you faithfully for decades.