The reason a CSV parser is harder to write than line.split(",") is quoting. Real data contains commas, quotation marks and even line breaks, and CSV needs a way to represent them without breaking the structure.
The rules from RFC 4180
RFC 4180 describes the conventions most CSV writers follow:
- Each record is on its own line, ended by a line break (CRLF in the RFC, though many files use LF).
- The last record may or may not end with a line break.
- There may be an optional header line with the same number of fields as the records.
- Fields are separated by commas, and every record should have the same number of fields.
- Fields may be enclosed in double quotes. Fields containing a comma, a double quote or a line break must be enclosed in double quotes.
- A double quote inside a quoted field is escaped by putting another double quote before it.
Examples
A value with a comma:
name,address
Ada,"12 High St, London"
A value containing double quotes. Double each one and wrap the field:
quote
"She said ""hello"" and left"
That field's real content is: She said "hello" and left.
A value with a line break inside quotes:
note
"Line one
Line two"
This is one record spanning two lines of text. A parser that splits on newlines first will read it as two broken records, which is why you should use a real CSV parser rather than splitting by hand.
An empty value is just two commas in a row: Ada,,36. An empty string and a missing value are usually indistinguishable in CSV.
Spaces
Spaces around values are part of the data in RFC 4180, so a, b has a second field of b with a leading space. Some tools trim, which causes differences between programs. Avoid spaces after delimiters.
Where the rules are not followed
- Backslash escaping. Some tools write
\"instead of doubling the quote. That is not RFC 4180 and breaks standard parsers. - Different delimiters. Semicolons or tabs, with the same quoting rules applied to the new delimiter. See CSV vs TSV.
- Smart quotes. Typographic quotes
“ ”are not delimiters, and if they replace straight quotes the field will not be recognised as quoted. - Unbalanced quotes. An opening quote with no closing quote swallows the rest of the file into one field.
Typical symptoms and causes
| Symptom | Likely cause |
|---|---|
| Extra columns in some rows | A comma inside an unquoted value |
| One row split into two | A line break inside an unquoted value |
| Everything after row N in one cell | A missing closing quote |
| Quotes appear in the data | Quote characters not removed or not doubled |
| Header not matching columns | BOM or stray whitespace |
How to check a file
Count the fields per row. A well-formed CSV has the same number in every record. Docento's Text & Markdown Editor does this for .csv and .tsv files as you type, understanding quoted fields and doubled quotes, and tells you the line where the count first differs or a quote is left open. More detail in how to validate a CSV file.
Writing CSV correctly
Do not build CSV by concatenating strings. Use the CSV writer in your language's standard library, which quotes only when needed and doubles internal quotes. If you must generate by hand, always quote every field, which is valid and avoids edge cases at the cost of a larger file.
Takeaway
Wrap any field containing a comma, quote or line break in double quotes, and double any quote inside. Use a real CSV library to read and write, and validate that every row has the same number of fields.
