Working with CSV Files Without Breaking Them

Commas inside values, and how quoting works

The moment a value contains the delimiter, the format needs an escape, and the convention is to wrap the field in double quotes. A quote character inside a quoted field is then written twice. This is why a name field holding Smith, John appears as "Smith, John" and why a value containing a quotation mark doubles it — not a typo, but the escape rule.

Splitting a CSV line on commas therefore does not work, however tempting it is. A correct parser tracks whether it is inside a quoted field, and quoted fields may legally contain commas, quotes and even line breaks. If a file suddenly gains extra columns partway through, an unescaped delimiter inside a value is nearly always the cause.

The delimiter is not always a comma

In locales where the comma is the decimal separator, spreadsheets commonly write CSV with semicolons instead, so a file that opens perfectly on one machine lands in a single column on another. Tabs are also widespread, and are safer precisely because tabs rarely occur inside values.

Because the delimiter is not declared anywhere in the file, tools have to guess it, usually by testing candidates and seeing which produces a consistent column count. When an import goes wrong, checking the delimiter is the fastest thing to rule out — and when you control the output, tab-separated data avoids the whole class of problem.

Encoding, and the spreadsheet that wants a BOM

Text that looks like é where é should be is a UTF-8 file being read as a legacy single-byte encoding. CSV carries no encoding declaration, so the reader has to be told or has to guess, and guessing wrong garbles every non-ASCII character in the file while leaving the structure intact — which is why the damage often survives all the way into a database.

Excel is the usual culprit: on many systems it assumes a legacy codepage unless the file begins with a UTF-8 byte order mark. Writing the BOM makes accented characters open correctly there, at the cost of a few invisible bytes at the start that some strict parsers will hand you as part of the first column name. Knowing that in advance saves a confusing half hour.

Values a spreadsheet will silently rewrite

CSV has no types — everything is text — so the reader decides what things mean, and spreadsheets are aggressive about it. Leading zeros vanish when a postcode or a product code is read as a number. Long identifiers turn into scientific notation. Strings that resemble dates get reformatted according to the machine's locale, which is how the same file produces different results in two offices.

The safe move is to keep such columns as text at the moment of import rather than fixing them afterwards, because the original digits are gone once the conversion happens. For anything where the exact characters matter — identifiers, phone numbers, codes — treat automatic type detection as something to switch off, not something to correct later.

Moving CSV into other shapes

Converting to JSON gives you real types and nesting, which makes the data far easier to validate — but it also forces the ambiguities above to be resolved explicitly, so it is worth doing early rather than late. Going the other way, generating SQL inserts from a CSV, the same escaping question returns in a more dangerous form: quotes inside values must be escaped for SQL, not for CSV.

A short pre-flight before trusting any CSV:

• Confirm the delimiter — comma, semicolon or tab — before parsing.
• Confirm the encoding, and add a BOM if the file is destined for Excel.
• Never split on the delimiter naively; respect quoted fields.
• Import identifier-like columns as text so leading zeros survive.
• Check the row and column counts after a conversion, not just the first few rows.