CSV Cheat Sheet: RFC 4180 Rules, Delimiters, and the Flags That Matter

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

Print this. Everything below is either a clause of RFC 4180 or a flag you will otherwise look up for the fourth time this month.

The whole specification

RFC 4180 defines a record as one or more fields separated by a comma, records separated by CRLF. Five rules cover it:

Rule	Written form	Consequence
Trailing CRLF optional	last record may omit it	a file with and without a trailing newline are both valid
Same field count per record	every record has identical field count	violation is informative, not an error — most readers accept it
Fields with `"` , `,`, CR or LF must be quoted	`"1,2"` is one field	splitting on `,` is only safe on files you generated
Quote inside a quoted field is doubled	`"He said ""go"""` → `He said "go"`	a single inner quote shifts every later column
Whitespace is part of the field	`" x"` ≠ `x`	any trim is a data transformation, not a cleanup

Anything you have seen "CSV" do that is not in that table is a dialect, not the format.

Delimiters by producer

Producer	Default delimiter	Decimal mark	Notes
Excel, en-US	`,`	`.`	follows the OS list separator, so it can be `;` on the same machine
Excel, de-DE / fr-FR / pt-BR	`;`	`,`	`12,34` is twelve-point-three-four
Excel "CSV UTF-8" export	`,`	locale	writes a UTF-8 BOM; plain "CSV" writes ANSI/code page
Google Sheets	`,`	`.`	no BOM
Postgres `COPY ... CSV`	`,`	`.`	`\N` is NULL; quote only when required
MySQL `SELECT ... INTO OUTFILE`	`\t`	`.`	tab-delimited by default, fields not quoted
pandas `to_csv`	`,`	`.`	`lineterminator` defaults to `\n`, not the CRLF the RFC asks for

Mixed output — a semicolon file parsed as a comma file — parses "successfully" into a single column, every time, with no warning. Column count is the cheapest validity check you have.

Quoting modes

minimal / all-unnecessary-omitted — quote only fields containing the delimiter, quote char, or newline. Postgres, Google Sheets, most APIs.
all — quote every field. Safest to round-trip; largest files. Excel offers this as a checkbox.
nonnumeric — quote anything that is not a number. Ambiguous by construction: it depends on your writer's type system, which CSV does not have.

Import flags worth memorising

Postgres:

COPY t FROM '/tmp/f.csv' WITH (FORMAT csv, HEADER true, DELIMITER ';',
  QUOTE '"', ESCAPE '"', NULL '', ENCODING 'UTF8');

NULL '' treats an empty field as NULL instead of the empty string — the single most common import surprise. Omit it when empty and absent mean different things in your schema.

MySQL:

LOAD DATA INFILE '/tmp/f.csv' INTO TABLE t
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
  LINES TERMINATED BY '\r\n' IGNORE 1 LINES
  (id, name, @phone) SET phone = NULLIF(@phone, '');

Column-count mismatch aborts the whole load in strict mode and silently fills with defaults in non-strict mode. Check sql_mode before trusting a load that "worked".

Python: csv.reader(f, delimiter=';', quotechar='"', doublequote=True, skipinitialspace=True) — skipinitialspace is the flag that fixes a, b becoming ' b'.

BOM and encoding

A UTF-8 BOM is bytes EF BB BF. If your first header arrives as ï»¿id or \ufeffid, the writer was Excel's "CSV UTF-8". Strip with Python encoding='utf-8-sig', Node strip-bom, or Postgres ENCODING 'UTF8' which accepts it. Windows Excel opening a BOM-less UTF-8 file reads it as ANSI and mojibakes every accented character — the fix is the BOM, not re-encoding the file.

Five checks before a file reaches a database

Column count per row is constant, or the exceptions are counted and named.
First header is free of a BOM and of a leading " that should have been stripped.
No field begins with =, +, -, or @ unless you chose that data (Excel formula injection).
Values with leading zeros survive a round-trip: 0123456789 must still be 11 characters after parsing.
Row count matches the source's row count, not the file's line count — quoted newlines make those different numbers.

Paste into the converter to see the row counter and ragged-row warning; the best practices page covers why type inference is the check most likely to fail silently.