RFC 4180 Explained: The CSV Edge Cases Every Tool Gets Wrong

CSV looks like the simplest format in computing until a field contains a comma. RFC 4180 is the one-page document that most parsers claim to follow, and the gap between what it says and what tools actually do is where exports silently corrupt. This is a field guide to the edge cases: quoting, embedded line breaks, encoding, delimiters, and the three problems the spec never addressed at all.

#TL;DR

• RFC 4180 is Informational — it documents common practice, not a mandate.
• Quote any field with a comma, a double quote, or a line break.
• Escape a literal double quote by doubling it, never with a backslash.
• The ABNF grammar is ASCII-only; UTF-8 lives in the charset parameter.
• Ragged rows, NULL vs empty, and formula injection are all out of scope.

#What does RFC 4180 actually specify?

The document is short enough to read in five minutes, and reading it removes most CSV arguments from a codebase.

#The seven rules in Section 2

RFC 4180 was published in October 2005 by Yakov Shafranovich, and Section 2 is the entire format definition — seven numbered rules plus an ABNF grammar. Compressed:

• Each record sits on its own line, delimited by CRLF.
• The last record may omit the trailing line break.
• An optional header line comes first, with the same field count as the records.
• Fields are separated by commas; the last field has no trailing comma.
• Spaces are part of a field and must not be trimmed.
• Fields containing CRLF, a double quote, or a comma go in double quotes.
• Inside a quoted field, a double quote is escaped by doubling it.

That is the whole format. Everything else you have argued about — types, NULLs, encodings, semicolons — is not in there.

#The ABNF grammar, read as a parser

The grammar is worth memorising because it answers questions the prose leaves open:

field       = escaped / non-escaped
escaped     = DQUOTE
              *(TEXTDATA / COMMA / CR / LF / 2DQUOTE)
              DQUOTE
non-escaped = *TEXTDATA
TEXTDATA    = %x20-21 / %x23-2B / %x2D-7E

Read the TEXTDATA range carefully. It is printable ASCII with two holes punched in it: %x22 (the double quote) and %x2C (the comma) are excluded, which is the formal way of saying those two characters may only appear inside a quoted field. There is no %x80 and above — the grammar does not describe non-ASCII bytes at all.

#Why RFC 4180 is Informational, not a standard

The RFC's category is Informational, and its own registration section admits there is no master specification for the format. Its advice to implementers is the Postel principle, borrowed from RFC 793:

"be conservative in what you do, be liberal in what you accept from others" — RFC 4180, Interoperability considerations

That single sentence explains why every CSV library ships a pile of dialect options. The spec was updated once, by RFC 7111 in January 2014, which added URI fragment identifiers so a URL can address #row=4 or #cell=4,1 inside a CSV — and, more usefully, changed the media type registration so that when no charset parameter is present, UTF-8 should be assumed.

#How to escape commas and quotes in a CSV file

This is the rule everyone half-remembers, and the half that gets forgotten is the doubling.

#When a field must be quoted

Exactly three characters force quoting: the comma, the double quote, and a line break. Nothing else does. A field containing a semicolon, a tab, a backslash, or an emoji is legal unquoted. Quoting more than the minimum is always safe — that is what Python's QUOTE_ALL does — but it is not required.

id,name,note
1,"Smith, John",plain text is fine
2,"She said ""no""",backslashes\are\fine
3,"line one
line two",trailing space is kept

#Why a double quote is escaped by doubling it

Because CSV has no escape character. The grammar's 2DQUOTE production means a literal quote is written as two quotes, and the parser collapses the pair. Writing \" produces a field containing a backslash followed by the end of the field — a bug that appears constantly in hand-rolled exporters that borrowed JSON's escaping rules. If you need JSON escaping semantics, you want a JSON tool, not a CSV writer.

#Spaces are part of the field

Rule 4 is explicit: spaces are considered part of a field and should not be ignored. So a, b is a two-field record where the second field is " b" with a leading space. Python's csv module exposes this as skipinitialspace, which defaults to False — spec-compliant, and the source of many "why does my lookup fail" bugs when the producing tool put a space after every comma.

#Why does my CSV break on line breaks inside a field?

Embedded newlines are the single most common cause of a CSV that "has the wrong number of rows".

#CRLF between records, CRLF inside quoted fields

A quoted field may contain CR, LF, or CRLF, and those bytes are data, not record separators. A parser therefore cannot split on newlines first and tokenise second — it has to track quote state character by character. Any tool built on file.split("\n") will shred multi-line address fields, and the failure is silent: you get more rows than records, each with the wrong field count.

#Mixed CRLF and LF in the same file

The spec says CRLF. Real files mix. A Windows export appended to by a Unix script produces records separated by \r\n and quoted fields containing bare \n. Strict CRLF parsers then see the bare \n as data (correct) but a naive LF-splitter sees a record boundary (wrong). Python sidesteps this by hard-coding its reader to accept either \r or \n as end of line and ignoring the lineterminator dialect setting entirely — which is why the docs insist you open files with newline=''.

import csv

with open("data.csv", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        print(row)

Drop newline="" and the file object performs its own universal-newline translation before the CSV reader sees the bytes, mangling embedded line breaks.

#The trailing newline on the last record

Rule 2 says the final record may or may not end with a line break, so both forms are valid input. On output, pick one and be consistent: a trailing newline is friendlier to cat, wc -l, and line-oriented diffs. Parsers that emit a phantom empty final row are failing to distinguish "file ends after CRLF" from "file contains an empty record".

#Why does Excel show my CSV as gibberish?

Encoding is where RFC 4180 stops helping, because the format has no in-band way to declare one.

#The spec's grammar is ASCII-only

TEXTDATA tops out at %x7E. Anything above that — accents, CJK, emoji — is outside the grammar, and RFC 4180 pushed the problem to the MIME layer with the optional charset parameter, noting that common usage was US-ASCII. RFC 7111 later made UTF-8 the assumed default when the parameter is absent. Neither helps a file sitting on disk, where there is no Content-Type header at all.

#When to write a UTF-8 BOM

A CSV file has no magic number, so Excel guesses. Without a UTF-8 byte order mark (EF BB BF) it falls back to the locale code page — Windows-1252 in the US and UK, something else elsewhere — and non-ASCII text arrives as mojibake. Writing the BOM as the first three bytes tells Excel the file is UTF-8.

The trade-off is real: the BOM fixes Excel and mildly annoys everything else. If the file is for humans opening it in a spreadsheet, write it. If it feeds a pipeline, don't — or strip it on read with the utf-8-sig codec.

#Semicolon CSV and the locale list separator

COMMA = %x2C. There is no clause permitting another delimiter, so a semicolon-separated file is simply not RFC 4180 CSV. It exists anyway because Excel writes the Windows regional list separator, which is a semicolon across most of continental Europe, where the comma is the decimal mark. Tools cope by sniffing. Python's Sniffer breaks ties using a preference order of comma, tab, semicolon, space, colon — a heuristic that its own documentation calls rough and prone to false positives in both directions.

#The edge cases RFC 4180 never addressed

Three problems bite in production and appear nowhere in the spec.

#Ragged rows: more fields than the header

Rule 3 says the header should contain the same number of fields as the records, and rule 4 says each line should contain the same number of fields throughout. "Should", not "must", and no error behaviour is defined. Every library invents its own: Python's DictReader collects surplus values into a list under restkey and back-fills missing ones with restval, while strict=True on the dialect turns malformed input into an exception. Decide which you want before the data decides for you.

#Empty string versus NULL

CSV has no type system and no null literal. a,,c gives a middle field of zero length, and nothing in the file distinguishes "the empty string" from "no value". Databases and CSV disagree here permanently — Python's writer stringifies None to the empty string and documents that this is deliberately not reversible. If the round trip matters, agree on a sentinel out of band, or move the payload to a format with types before it hits the CSV boundary. This is also why converting a SQL dump to a spreadsheet needs an explicit NULL policy rather than a default.

#CSV injection: fields starting with =, +, -, or @

A perfectly valid CSV field can be a spreadsheet formula. Per OWASP's write-up on CSV injection, a cell beginning with =, +, -, @, tab (0x09), CR, or LF is evaluated on open, along with full-width variants such as ＝ in some locales. Quoting does not save you — OWASP notes that Excel may drop quotes when a file is saved and reopened, reactivating a previously neutralised formula. The mitigation that survives that round trip is prefixing a tab character inside the quoted field, at the cost of a stray tab in the data.

#A practical compliance checklist

If you are writing a CSV emitter in 2026, this is the short list that keeps it interoperable:

• Terminate records with CRLF; accept CRLF, LF, and CR on input.
• Quote only fields containing a comma, a double quote, or a line break.
• Double every embedded quote; never emit a backslash escape.
• Preserve spaces exactly; do not trim on write or read.
• Emit a header with exactly the field count of every record.
• Write a UTF-8 BOM only when the target is a spreadsheet app.
• Neutralise leading =, +, -, @ before untrusted data reaches a cell.

Testing that is easier than reasoning about it. Paste a record containing all four hazards into iKit's CSV ↔ JSON converter and check the JSON that comes back: if the comma, the doubled quote, the newline, and the leading space all survive the round trip, your quoting is correct. The conversion runs entirely in the page, so a customer list never leaves the machine — which matters more here than in most formats, because CSV is what people export contact data into.

#References

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — primary source for the seven Section 2 rules, the ABNF grammar, and the text/csv registration.
• RFC 7111 — URI Fragment Identifiers for the text/csv Media Type — the 2014 update; cited for fragment syntax and the UTF-8 default charset change.
• csv — CSV File Reading and Writing — dialect defaults, newline='' requirement, Sniffer delimiter preference, and None handling.
• CSV Injection — formula-injection characters and the Excel-resistant tab-prefix mitigation.
• Model for Tabular Data and Metadata on the Web — W3C's later attempt to give CSV the type and metadata layer RFC 4180 lacks.

#Related on iKit

• Start from the round trip: how CSV becomes JSON and back — the full conversion walkthrough, including how the header row becomes object keys.
• Going the other direction turns quoting into the hard part — flattening nested objects into columns, where RFC 4180 quoting rules decide whether the output survives Excel.
• Same escaping problems, different source: SQL dumps into a spreadsheet — NULL handling and delimiter choice when the input is a CREATE/INSERT script rather than a CSV.