CSV Patterns and Gotchas: Duplicate Headers, Ragged Rows, and Silent Pads
CSV fails by looking fine. A malformed JSON file throws; a malformed CSV file produces a slightly different dataset, and you find out from a report three weeks later. These are the six shapes that do it, with what this site's parser does to each — verified against the shipped code, not from memory.
1. Duplicate header keys
id,name,name
1,Ada,LovelaceJSON is built by assigning obj[header] = value in header order, so the second name wins and the first value disappears: {"id":"1","name":"Lovelace"}. The row counter still says one row and the ragged-row check stays silent, because every row does have three columns. Nothing is wrong with the parse; the object model cannot hold two keys called name.
Deduplicate at write time (name,name_2) rather than hoping. If you cannot change the export, join on index instead of name — the SQL conversion is index-based, which is exactly why it survives this and JSON does not.
2. Ragged rows behave differently in each output
A row with fewer columns than the header:
- JSON pads the missing keys with
"". You get a complete object with invented empty values — the database stores an empty string where no data existed. - SQL emits exactly the values it found:
INSERT INTO records (a, b, c) VALUES (1, 2);. Three columns, two values, rejected by the database.
Neither is "right". Padding loses information silently; truncating loses the row loudly. The warning line names the count either way, so read it before you copy the output.
3. A quote that does not open a quoted field
A quote is only treated as an opening quote when nothing precedes it in the field. Consequences, all real inputs:
| Input | Parsed field | Why |
|---|---|---|
a"b | a"b | mid-field quote is literal — correct, RFC-incompatible writers do this |
"quoted" | "quoted" | a leading space means the quote never opens, so the quote marks become data |
"ok"tail | oktail | the closing quote ends quoting and the tail is appended, not dropped |
That second row is the expensive one: a space before a quote turns visible punctuation into part of your value, and downstream string comparisons stop matching.
4. Blank lines and trailing commas are not the same thing
A truly empty line is dropped — the last id in your file does not become "". But a line containing only the delimiter, ,,, survives as three empty fields, because a line of delimiters is a real record in RFC terms. Same asymmetry after a trailing comma: a,b, is three fields with an empty third, and a header that reads a,b, gives you a key called "".
5. The row count will not match your line count
This is not a bug, it is the format. A quoted field may contain a newline, so one record can span four lines. A 500-row export from a payment processor routinely arrives as 640 lines. Any pipeline that validates "line count equals expected rows" is wrong for every file containing an address with a newline in it, which is most address data.
Compare parsed row count to row count. The tool reports parsed rows and flags column-count mismatches, which is the same check a real parser does internally.
6. Numbers get rewritten on the way to SQL
The SQL output leaves a number unquoted only when it round-trips as a finite number. That guard is good and it is also where values change shape:
| Input value | SQL output | Change |
|---|---|---|
42 | 42 | none |
1e5 | 100000 | notation rewritten |
0x10 | 16 | hex parsed as a number |
42 | 42 | already trimmed by the parser, so the guard never sees the padding |
0123456789 | 123456789 | leading zero gone — phone, ZIP, FIPS, IBAN |
9007199254740993 | 9007199254740992 | beyond 2^53, the last digit changes |
1,234 | '1,234' | stays quoted; Number() rejects it |
'' | '' | empty string, not NULL |
Quoted fields do not escape this: every cell is trimmed before conversion, so "01234" — explicitly quoted by the producer to say this is text — still arrives at the number test as bare 01234. CSV's only type signal is discarded on the way in. Keep any column of identifiers as text with a leading character, or run the JSON output and cast deliberately per column.
Related: best practices covers type inference and encoding; alternatives names the parsers that do not trim inside quotes.