CSV is not a format, it is a family of dialects

CSV looks like the simplest data format in existence: values, commas, newlines. It is in fact one of the least interoperable, because almost every property you would want to rely on is a convention rather than a rule.

The delimiter is not always a comma

In locales that use a comma as the decimal separator — much of Europe — Excel writes and expects semicolons. A file exported in Berlin and opened in Boston will land entirely in column A. Tab-separated files are common in bioinformatics and log processing, and pipe-separated files turn up wherever the data itself contains both commas and semicolons.

Quoting is where the real damage happens

RFC 4180 says a field containing a delimiter, a quote or a line break must be wrapped in double quotes, and a literal quote inside is doubled. Both halves matter:

id,name,note
1,"Hopper, Grace","She said ""it works"""
2,"Multi-line
note",fine

That is one header row and two data rows, even though it spans four lines. A naive parser that splits on newlines produces three broken rows, and one that splits on commas turns Grace Hopper into two people. This is why splitting CSV with a regex fails on real data — the format is not regular.

If your parser is a call to split(","), it is not a CSV parser. It works until someone's address contains a comma, which is roughly the first day it meets production data.

Encoding and the BOM

Excel on Windows historically wrote CSV in the system code page rather than UTF-8, so accented characters arrive as mojibake. When it does write UTF-8, it often prefixes a byte order mark. That BOM becomes part of your first header name, so a lookup for "id" fails against a column actually called "\uFEFFid" — a bug that survives inspection because the two strings look identical on screen.

Line endings

The specification says CRLF. Unix tools write LF. Old Mac software wrote CR alone. A parser that only handles one leaves stray \r characters at the end of every final field, so "true\r" does not equal "true" and every comparison silently fails.

Types that are not types

CSV has no type system: every value is text, and any interpretation is a guess made by the reader. The guesses are frequently wrong.

• Leading zeros vanish — a postcode of 01234 becomes 1234
• Long identifiers become floats — a 19-digit ID loses its last digits to rounding
• Anything shaped like a date becomes one — the gene SEPT1 famously became 1 September
• An empty field is ambiguous: empty string, null, or absent are indistinguishable

Surviving it

• Use a real parser. Every language has one; none of them cost you anything.
• State the delimiter explicitly when you can, rather than relying on detection.
• Write UTF-8, and add the BOM only if you know Excel is the consumer.
• Quote defensively — quoting a field that did not need it is always safe.
• Validate the column count per row before processing. A ragged row means something upstream is broken.
• Treat everything as a string until you deliberately convert it.

When to stop

CSV is a reasonable interchange format for flat, tabular, mostly-textual data going into a spreadsheet. It is a poor choice for nested structures, for anything where types matter, and for anything requiring a schema. If you are inventing conventions to encode nesting inside cells, you have outgrown the format — JSON Lines or Parquet will cost you less than the workarounds.