CSV Dialects: RFC 4180 vs Excel vs Pandas vs DuckDB

CSVFormat — CSV Formatter & Converter Guides · Updated 2026-10-12 · All guides

"CSV" is not a format, it is a family. RFC 4180 defines about five rules; every producer in the table below adds or relaxes a few. The useful model is that a CSV file has dialect settings, and almost every parse failure is a dialect mismatch rather than a malformed file.

The five settings that differ

Setting	Values seen in the wild	Default that breaks
Delimiter	`,` `;` `\t` `\|`	comma, on a European export that uses `;`
Quote char	`"` `'`	`"` when the file uses `'` — quotes silently become data
Escape style	`""` doubling · `\` backslash	doubling, on a Postgres text-mode dump that uses `\N` and backslashes
NULL marker	`\N` · empty · `NULL` · `NA`	empty string, so "no value" and "empty value" merge
Line ending	`\n` · `\r\n`	reader splits on `\n`, `\r` sticks to the last column

Plus three that libraries add: whether the first row is a header, whether a trailing newline is required, and whether whitespace outside quotes is significant.

What each tool writes, and what it chokes on

Tool	Writes	Reads by default	Notable
RFC 4180	CRLF, `"` doubling, quote only when required	—	the spec allows a quoted newline; it does not define `\` escapes
Excel	locale delimiter, CRLF, quotes when required; BOM with "CSV UTF-8"	guesses; offers a delimiter picker	will not re-read what it wrote if the system list-separator changed since
Python `csv`	`\r\n`, `"` doubling	`"` doubling, `\n` split	`lineterminator` default is `\r\n` for the writer, `\n` for the reader — asymmetric by design
pandas	`\n` by default	sniffs delimiter from a sample (`engine='python'` sniffs; `c` does not)	`read_csv` guesses types too, which is a separate and worse default
DuckDB `read_csv`	`\n`, `"` doubling	`auto_detect=true` samples the file	inference is right often enough to hide the failure until a `01234` column arrives
Postgres `COPY … CSV`	`\n` server-side, `\N` for NULL	`""` doubling	NULL handling differs from both Excel and pandas
MySQL `INTO OUTFILE`	tab-delimited, `\n`, `\N`, no quoting by default	—	the most surprising one: a `.csv` from MySQL is often a TSV wearing a hat
Go `encoding/csv`	`\n` (not CRLF, spec-noncompliant)	`\n` and `\r\n` accepted	`FieldsPerRecord` errors loudly where most readers pad

Two asymmetries to memorise: Python writes CRLF and reads LF; Go writes LF where the spec asks for CRLF. Both are documented, both surprise someone every quarter.

The NULL problem, stated once

Producer	Empty field means	NULL is written as
Excel	empty string	indistinguishable from empty
Postgres `COPY CSV`	empty string	`\N` (unquoted)
pandas	NaN	empty string by default (`na_rep=''`)
MySQL `LOAD DATA`	empty string	`\N`
BigQuery export	empty string	empty string

So a Postgres→Excel→Postgres round-trip turns every NULL into an empty string, and every empty string into… whichever the receiving loader decides. Use NULL '' on one side and NULL 'NULL' on the other deliberately, per migration, and put the choice in the migration file rather than in someone's memory.

Sniffing a dialect

import csv
sample = open('mystery.csv', encoding='utf-8-sig', newline='').read(8192)
d = csv.Sniffer().sniff(sample)          # -> delimiter=';', quotechar='"'
print(d)

The sniffer is a heuristic over a 8 KB sample and will happily report a wrong delimiter for a file where the first 8 KB happen to be well-behaved. Prefer the shape test that never lies:

head -1 mystery.csv                                   # look at it
awk -F';' 'NR==1{print "semicolons:", NF}' mystery.csv
awk -F,   'NR==1{print "commas:",     NF}' mystery.csv

Whichever gives a plausible column count is the delimiter. A ;-delimited file parsed as comma-separated yields exactly one column and no error — the single most common silent failure in this format, and the reason the converter reports a row count you can sanity-check.

When to stop using CSV

Three honest triggers:

The file has a schema you care about. Then it wants Parquet or a typed format; CSV's type-free-ness is the bug you keep re-paying.
**Fields legitimately contain newlines and humans read the raw file.** Then the file is unreadable-by-humans already; use TSV (tabs in text are rarer than commas) or JSON Lines.
Two producers append to one file. Then the dialect drifts per section, and no parser setting fixes a file that is two dialects.

For a tool that must accept whatever arrives, the working pattern is: detect dialect, parse to a typed intermediate, then emit a canonical dialect — never re-emit the input dialect, because that is how the drift continues. See data cleaning for the order of operations and encoding and the BOM for the step that comes before all of this.