CSV delimiters, quoting and encoding: the three things that break files

There is a specification for CSV, RFC 4180, published in 2005, and it is barely two pages. It is also descriptive rather than authoritative: it documented common practice years after the format was in universal use. Real files depart from it constantly, in three specific dimensions.

1. The delimiter

The name says comma. Practice says otherwise.

In locales where the decimal separator is a comma, Germany, France, Spain, Brazil, most of continental Europe and Latin America, putting commas between fields is ambiguous, so semicolons are used instead. Excel follows the operating system's list separator setting, which is why the same file behaves differently on two machines.

Tabs (TSV) are also common, and arguably better: tabs almost never appear inside data fields, so quoting is rarely needed. The pipe | is used for the same reason.

Detection heuristics work by counting candidate delimiters per line and picking the one that yields a consistent column count. This is reliable most of the time and fails on files where a field contains many instances of another candidate.

Always state the delimiter when you hand a file to someone. It cannot be recorded inside the file.

2. Quoting

The rules from RFC 4180:

• A field must be quoted if it contains the delimiter, a double quote, or a line break.
• A double quote inside a quoted field is escaped by doubling it: "".
• Quoting a field that does not need it is allowed.

id,description
1,"Contains, a comma"
2,"She said ""hello"""
3,"Line one
Line two"

Row 3 is worth dwelling on: a single CSV record can span multiple physical lines. Any parser that splits the file on newlines before handling quotes will corrupt it. This is the most common flaw in hand-written CSV parsing, and the reason "just split on commas" fails on real data.

Departures you will meet in the wild: backslash escaping (\") instead of doubling, which is a MySQL export convention; single quotes used as the quote character; and unescaped quotes inside unquoted fields, which is simply broken and requires guessing.

3. Encoding

CSV has no way to declare its own encoding. The bytes are just bytes.

• UTF-8 is the correct modern choice.
• UTF-8 with BOM is what Excel needs to detect UTF-8 in a .csv. Without it, Excel falls back to the system code page and non-ASCII characters become mojibake.
• Windows-1252 / Latin-1 appears constantly in older exports.

The BOM is a genuine dilemma rather than a solved problem: Excel wants it, and many parsers include it in the first header name, so id becomes \ufeffid and every lookup by that name fails. Choose based on the consumer, and strip the BOM on ingest if you are the consumer.

Line endings

RFC 4180 specifies CRLF. Unix tooling produces LF. Most parsers accept both, but a parser that splits strictly on \n will leave a trailing \r on the last field of every row, which then fails to match anything, invisibly. This is a frequent cause of "the last column never matches".

Old Mac files using CR alone still occasionally appear.

Headers

Not required by the specification, and universally expected in practice. Problems worth checking for:

• Duplicate names. Two columns called name, one silently wins.
• Empty names, from a trailing delimiter on the header row.
• Trailing whitespace in header names, which breaks lookups invisibly.
• A title row above the headers, which shifts everything.

Diagnosing a broken file

1. Open it in a plain text editor, not a spreadsheet. Look at the raw first three lines. Most problems are visible immediately.
2. Count the delimiters on the first few lines. Inconsistent counts mean unescaped delimiters inside fields.
3. Check the first bytes for a BOM.
4. Look for lone \r at the end of fields.
5. Look for an unbalanced quote, which makes the parser swallow the rest of the file into one field, the signature is a file that parses as a handful of enormous rows.

Producing files that do not break

• Quote every field that could possibly need it. Over-quoting is harmless.
• Double internal quotes; do not use backslashes.
• Write UTF-8, with a BOM only for Excel.
• Use CRLF if you want strict conformance; LF is fine in practice.
• Use ISO dates and unformatted numbers.
• Document the delimiter and encoding alongside the file.

For files you receive, a delimiter converter and CSV cleaner will normalise most of this in one pass, locally, which matters given how often these files contain personal data.