CSV looks like the simplest format imaginable: values separated by commas, rows separated by newlines. It is also one of the most consistently mishandled, because that description is wrong in several important ways and the exceptions are exactly what real data contains.
Commas inside values
The obvious problem: what if a value contains a comma? An address like "221B Baker Street, London" would split into two fields under naive parsing. The convention is to wrap such values in double quotes, and any parser that splits on commas without tracking quote state will corrupt exactly the rows containing addresses and names with titles.
This is why a spreadsheet export can look perfect for two hundred rows and then quietly shift every column by one from row 201 onwards.
Quotes inside quoted values
If quotes delimit a value, how do you include a literal quote? The convention doubles it: a field containing She said "hello" is written as "She said ""hello""". A parser has to recognise a doubled quote inside a quoted field as an escaped quote rather than the end of the field.
Newlines inside values
A quoted field may contain literal line breaks — common in exported comments, addresses, and product descriptions. This breaks the intuitive approach of reading the file line by line, because one record can span several lines. A correct parser processes the file as a character stream and treats newlines inside quotes as data.
The separator is not always a comma
In locales where the comma is the decimal separator — much of continental Europe — spreadsheet software commonly exports semicolon-separated files while still calling them CSV. Tab-separated files are also widespread. If a file opens as a single column, the separator is usually the reason.
Encoding, and the mystery characters
If names in your data appear as strange character pairs — é where é should be, or ’ where an apostrophe should be — the file is UTF-8 being read as a single-byte encoding such as Windows-1252. The bytes are intact; the interpretation is wrong.
The reverse also occurs. A byte order mark at the start of a UTF-8 file can appear as  prepended to the first header, which quietly breaks column matching because the first key no longer equals what your code expects.
Practical guidance
- Never split on commas with a regular expression. Use a parser that tracks quote state.
- Check the first and last rows after any conversion, plus a few rows containing addresses or free text.
- If a column shifts partway down the file, look for an unescaped quote or comma in the row above.
- Confirm encoding is UTF-8 at export time rather than trying to repair mangled characters afterwards.