Five CSV edge cases that break data imports

1. Commas and quotes inside values

The first rule of CSV is that a comma inside a value does not end the field. RFC 4180 handles this by allowing any field to be wrapped in double quotes, and by escaping a literal double quote as two double quotes:

id,name,note
1,"Smith, John","He said ""no"" twice"
2,Plain value,

Row 1 has three fields, not five. A parser that splits on commas produces five, shifts every later column, and — this is the part that hurts — usually succeeds. The import completes. The notes column now contains surnames.

The rules that a correct reader has to implement are short but not optional:

• A field that starts with a double quote is quoted; the quote is not part of the value.
• Inside a quoted field, two double quotes mean one literal double quote.
• A comma inside a quoted field is data.
• Whitespace between a comma and an opening quote is not permitted by the specification, and is produced by real systems anyway.

2. Newlines inside a field

The rule that breaks the most pipelines: a quoted field may contain a line break, and that line break is part of the value.

id,address
1,"12 Example Street
London
SW1A 1AA"
2,"Somewhere else"

That file has two data rows. Read it line by line and you get four, three of which are malformed. Any code shaped like for line in file is wrong for CSV, and it is wrong in a way that only shows up when someone finally enters a multi-line address.

The same issue makes row counting unreliable: counting newlines does not count records. Splitting a large file into chunks at line boundaries can slice a record in half, which is a particularly unpleasant bug in a parallel import.

There is also a line-ending question underneath. RFC 4180 specifies CRLF, Unix tools produce LF, and older Mac software produced bare CR. A reader should accept all three; a writer should pick one and be consistent, because mixed endings inside one file confuse nearly everything.

3. The byte order mark in the first header

A file saved as "UTF-8 with BOM" begins with three bytes, EF BB BF, before any content. Read it as UTF-8 without stripping them and the first header becomes id rather than id.

The symptom is unmistakable once you have seen it: every column maps correctly except the first, which the importer insists does not exist, while the header visibly says it does. Nothing prints differently, because the BOM is a zero-width character.

Excel writes the BOM when saving as "CSV UTF-8", and it does so for a good reason: without it, Excel guesses the encoding from the locale and mangles every non-ASCII character. So the BOM is often the lesser problem, and the fix belongs on the reading side. Decode with an encoding that strips the BOM, or trim the character from the first header explicitly.

When diagnosing a "column not found" error that makes no sense, check the first bytes of the file before checking anything else.

4. Leading zeros that disappear

CSV has no types. Every field is text, and meaning is assigned by whatever reads it. Most tools guess, and the guess is usually "this looks like a number".

So 007 becomes 7, and a postcode, a product code, a bank sort code or a country dialling code loses the digit that made it valid. Open the file in a spreadsheet, save it, and the loss is now permanent in the file itself.

Quoting does not help. "007" is still read as the number 7 by tools that infer types after unquoting, which is most of them — a point that surprises people, because quoting solves the comma problem so cleanly.

What does work:

• Tell the importer the column is text, explicitly. Every serious import tool has this setting.
• Use a fixed schema rather than inference, which is what a generated CREATE TABLE plus typed INSERT statements gives you.
• If you only control the file, prefix values with an apostrophe for Excel specifically — effective, and it pollutes the data for every other consumer.

5. Long numbers turned into scientific notation

The same type inference, one step further. A long digit string such as a 16-digit card reference or a barcode is read as a number, and because it is large it is displayed — and often saved — as 1.23457E+15.

This is worse than the leading-zero case, because it is lossy. Spreadsheet number precision is limited to around fifteen significant digits, so the digits past that are not hidden, they are gone. Re-saving the file writes the rounded value, and no amount of reformatting recovers it.

It is the same root cause as the JSON precision problem: a value that is an identifier being handled as a quantity. The fix is the same too. Identifiers are text. Say so at every boundary, and where you control the schema, define them as a string type so the database rejects the ambiguity rather than resolving it.

Writing CSV that survives

If you are generating the file, a few habits prevent most of the above:

• Quote defensively. Quote any field containing a comma, a quote, a newline, or leading or trailing whitespace. Quoting everything is also fine and simpler to reason about.
• Pick one line ending and use it for the whole file.
• Write UTF-8, and decide about the BOM deliberately. Include it if a spreadsheet is the primary consumer, omit it if a program is, and document which you chose.
• Never write a bare number for an identifier. Treat codes, references and IDs as text throughout.
• Include a header row and keep the names stable. Positional mapping breaks silently when a column is inserted.

And if you are reading: use a real parser. The rules above are the whole specification, which makes writing one a tempting afternoon project, but the value of a well-used library is that it has already met the files that break the obvious implementation.

The JSON to CSV converter here quotes according to RFC 4180, and CSV to SQL reads quoted fields, embedded newlines and a leading BOM, so a file that round-trips through them keeps its values intact.