CSV pitfalls: quoting rules, delimiters, and the ways Excel edits your data

CSV looks too simple to go wrong, which is exactly how it goes wrong: shifted columns from an unquoted comma, dates reinterpreted by locale, IDs mangled into scientific notation. This guide states the actual quoting standard, the delimiter politics, and the defensive moves for files that must survive Excel.

There is no CSV standard in the way there is a JSON standard — there is RFC 4180, a memo that documents the common practice, plus forty years of spreadsheet behaviour layered on top. Files live in the gap between the two: a producer writes RFC quoting, Excel applies its own ideas about what your text meant, and the data that arrives is not the data that left. Most CSV corruption is born in that gap, and most of it is preventable once the traps are named.

This article states the quoting rules exactly, deals with the delimiter question (comma is not universal), and then walks the Excel-specific hazards — type guessing, dates, encoding, giant numbers — with a defence for each.

The quoting grammar, all of it

A CSV record is fields separated by a delimiter, terminated by a line break (CRLF in the RFC, LF in most modern practice — parsers accept both, be liberal in reading and consistent in writing). A field may be wrapped in double quotes; it must be, if it contains the delimiter, a quote, or a line break. Inside a quoted field, a double quote is written twice. There is no backslash escaping in CSV — \" is a backslash followed by an end-quote, and producers that emit backslash escapes are writing a private dialect that strict parsers will misread.

id,attendee,note
7,"Osei, Daniel","Said ""arriving at 6"" at check-in"
8,"Mina Park","First line of her note
second line of her note"

Both records above have three fields. The second record spans two physical lines — legal, quoted, and the reason line-count is not a record-count check. When a CSV breaks in the wild, post-mortems find the same two causes: a producer that did not quote a comma-bearing field, or a consumer splitting on lines before parsing quotes. Insist on a real parser at every hop — every language ships one — and half the genre of bug disappears.

Delimiters: the comma is a local custom

In locales where the decimal separator is a comma (much of Europe), Excel expects semicolon-delimited files when you double-click them, and a comma-delimited file opens as one wide column of unsplit text. The file is not broken; the expectation is local. Practical policy: if you know the consumer's locale, match it; if you do not, tab-separated values (.tsv) sidestep the entire politics — tabs almost never appear in data, every spreadsheet imports them, and parsers handle them cleanly. Whatever you choose, communicate it in the filename or a covering note; a .csv extension carrying semicolons is a riddle recipients solve by trial and error.

How Excel rewrites your file on open

The deeper principle: Excel's open-file behaviour is designed for humans typing data, not for pipelines. Where a file's consumer is really a program, do the verification in a parser and treat Excel as a viewer of last resort; where the consumer is a human in Excel, export an Excel-shaped file (BOM, locale delimiter, protected text columns) and say so.

Encoding: the mojibake chapter

CSV carries no encoding declaration, so readers guess. The modern correct answer is UTF-8; the Excel-on-Windows answer is UTF-8 with a byte-order mark (BOM), which is the signal that makes it render accents, Arabic, Chinese and emoji correctly instead of as two-character rubble. Unix tooling prefers no BOM (a BOM becomes part of the first column's header name in naive parsers) — yet another consumer negotiation to settle deliberately. One file cannot please both defaults; produce the variant for the audience you have, and note the choice.

Mistakes and pitfalls

Splitting rows on newlines when reading CSV

Any quoted field may legally contain newlines, so line-splitting corrupts exactly the records that needed quoting most. Use a parser; if you wrote the splitter yourself, you have written the bug yourself.

Hand-writing CSV by joining strings with commas

Concatenation without a quoting function is correct until the first comma-bearing value, which in real data is a 'when', not an 'if'. Every platform has a CSV writer; the two lines it takes are the cheapest insurance in data work.

Saving the Excel-edited file back over the source

Once Excel has guessed types and you press save, the losses are baked in and the round trip multiplies them. Keep the pipeline file read-only; let Excel have a copy.

Common questions

Should every field be quoted, always?

Quoting everything is legal, maximally defensive, and what many robust writers do. Costs: larger files, and some elderly importers prefer minimal quoting. If your consumer is unknown and modern, quote-all is a sound default.

Why does my file open in one column in Excel?

Delimiter mismatch with the machine's locale, nine times out of ten. Either re-export with the locale's delimiter (often semicolon) or use Excel's import wizard / Data → From Text, which lets you declare the delimiter explicitly.

Can CSV hold unicode, emoji and Arabic safely?

Yes — as UTF-8, with the BOM caveat for Excel. The format is bytes-agnostic; every mojibake incident is an encoding declaration lost between writer and reader, not a limitation of CSV itself.

To wrap up

CSV survives because it is the least common denominator of data exchange — and it bites because everyone assumes the denominator is lower than it is. Quote by the grammar, choose delimiters and encoding as explicit agreements with the consumer, and quarantine Excel's type-guessing behind deliberate defences, and the humble .csv goes back to being boring, which is the highest praise a data format can earn.