CSV to JSON: 8 Edge Cases That Quietly Break Converters
Embedded commas, multi-line fields, BOMs, leading zero IDs, ambiguous dates. Each one silently produces wrong JSON. Here is what they look like, why they break, and how to spot the failure before downstream code crashes.
You convert a customer export CSV to JSON. The downstream tool says some records are missing and some have wrong fields. You re-run the conversion in a different tool, get different output, and now you have two sets of broken records to compare. The CSV is the same in both runs, but each parser handled the edge cases differently.
CSV is not a single format. It is a family of related formats with subtle disagreements. The eight edge cases below break a lot of converters quietly, including some popular ones. Each comes with the input that triggers it, the wrong output a naive parser produces, and the fix.
Quick Diagnosis
Before debugging the JSON output, run these three quick checks on the source CSV:
- Check the first three bytes with
head -c 3 file.csv | xxd.ef bb bfmeans a BOM is present and parsers may misread the first column header. - Look for embedded quotes or commas in any field. If the data contains user-typed text, it almost certainly has them.
- Look at one large numeric value. Anything more than 16 digits will lose precision in JavaScript-based JSON tools.
If any of these are present, naive parsers will produce wrong output. The full list of failure modes and fixes is below.
Edge Case Summary
| Edge case | Wrong output it produces | Fix |
|---|---|---|
| Quoted comma in field | Field gets split into two columns | Use an RFC 4180-aware parser |
| Quoted newline in field | Record count is wrong; row split mid-field | Parser must track quote state across lines |
| BOM at file start | First property name is prefixed with junk | Strip BOM before parsing |
| Mixed line endings | Rows merged or duplicated | Normalize to LF or CRLF first |
| Numeric type inference | Leading zeros dropped (ZIP codes, IDs) | Disable type inference; treat as string |
| Numbers too large for IEEE 754 | Last digits change silently | Treat 17+ digit values as strings |
| Ambiguous date format | Months and days swapped | Keep dates as strings; convert to ISO 8601 |
| Duplicate or empty headers | Columns overwritten or merged | Rename duplicates; emit arrays for collisions |
1. Embedded Commas in Quoted Fields
The input:
name,age
"Smith, John",30
Doe Jane,28What a naive parser does: splits each line by comma. Row one becomes three fields: "Smith, John", 30. Output JSON has the wrong number of columns and downstream code crashes or silently drops the row.
The fix: use a parser that tracks quote state. RFC 4180 specifies that a field wrapped in double quotes can contain commas. Every serious CSV library (Python's csv, Node csv-parse, Ruby CSV) handles this. Hand-rolled split-on-comma parsers do not.
2. Embedded Newlines in Quoted Fields
The input:
id,description
1,"First line.
Second line."
2,"All on one line"What a naive parser does: splits by newline. The first record's description gets cut at the newline; "Second line." becomes its own malformed row. The total record count is wrong.
The fix: use a parser that handles quoted multi-line records. Quoted fields can contain literal newlines per RFC 4180. The parser must track whether it is inside a quote across lines.
This case shows up wherever fields hold free-form text: user comments, address fields with line breaks, log messages, anything copy-pasted from a textarea.
3. BOM at the Start of the File
A CSV that begins with the three bytes EF BB BF (UTF-8 byte order mark) before the header row. Excel writes this when you save as "CSV UTF-8."
What a naive parser does: reads the BOM as part of the first header. Your first JSON property becomes "\ufeffName" instead of "Name". Code that looks for row.Name finds nothing.
The fix: strip the BOM before parsing. Most modern parsers do this automatically; older or hand-written ones do not.
How to detect: open the file in a hex editor or run head -c 3 file.csv | xxd. If the first three bytes are ef bb bf, the BOM is there.
4. Mixed or Wrong Line Endings
Three line-ending conventions exist:
- CRLF (
\r\n) — Windows, RFC 4180 spec. - LF (
\n) — Unix, macOS, most modern tools. - CR (
\r) — classic Mac OS up to 9. Still found in legacy data.
A file generated on one OS and parsed on another can produce wrong output if the parser only handles one convention. The most common failure: a CSV with CR line endings parsed by a tool that splits on LF, producing a single-row file with embedded \r characters in every field.
The fix: normalize line endings before parsing, or use a parser that recognizes all three. dos2unix, tr, or any text editor's line-ending setting can normalize.
5. Type Inference and Leading Zeros
The input:
name,zip,phone,id
John,02134,0123456789,001
Jane,90210,1800555000,002What a type-inferring parser does: sees 02134, decides it is a number, outputs 2134. Same for the phone number and the ID. Three fields are now wrong, and the wrongness is silent.
The fix: disable type inference. Treat every field as a string and cast explicitly only where you know the column is numeric. The default behavior of converting numeric-looking strings is wrong for any column that holds an identifier, a code, a phone number, a postal code, or a SKU.
Type inference is fine for actual quantities: temperatures, monetary amounts, counts. Even there, prefer explicit casting in the consuming code over implicit casting in the parser.
6. Numbers Too Large for IEEE 754
JavaScript represents all numbers as IEEE 754 doubles. The maximum safe integer is 2^53 - 1 = 9007199254740991, which is about 16 decimal digits. Numbers larger than this lose precision silently.
The input:
customer_id,account_number,balance
1,1234567890123456,100.50
2,1234567890123457,200.00Both 16-digit account numbers fit (just barely). A 17-digit number does not. After parsing, the two records may have the same account_number field even though the source CSV had different values.
The fix: treat all IDs as strings. Treat any field that might exceed 2^53 as a string. Use a string representation in the JSON output if precision matters. Common offenders: account numbers, IMEI numbers, large database IDs, credit card numbers.
7. Date Format Ambiguity
The string 01/02/2023 means January 2 in the US and February 1 in most of the world. ISO 8601 (2023-02-01) is unambiguous, but most CSV files in the wild are not in ISO format.
What parsers do: pick a locale-dependent default. Some use the system locale. Some default to US. Some try to guess by looking at all dates in the column and seeing if any value would be invalid under one interpretation. The guess is often wrong.
The fix: never let a CSV-to-JSON converter parse dates. Keep them as strings in JSON, or convert the source to ISO 8601 (YYYY-MM-DD) before parsing. ISO format eliminates the ambiguity entirely.
The trap: a CSV with dates that are valid in both interpretations (such as 03/04/2023, which could be March 4 or April 3) gives no warning that the parser picked the wrong one. The wrongness only surfaces when downstream reports look weird.
8. Duplicate Headers and Empty Fields
Duplicate headers:
id,name,id
1,John,external-1
2,Jane,external-2Most parsers map a row to a flat object, so the second id overwrites the first. Output: {"id": "external-1", "name": "John"}. The internal ID is lost.
The fix: rename duplicates before parsing, or use a parser that emits arrays for duplicate header names, or output rows as arrays of [key, value] pairs that preserve order and duplicates.
Empty field ambiguity:
id,middle_name
1,Robert
2,
3,""Row 2 has an unquoted empty field. Row 3 has an explicitly quoted empty field. Some parsers treat them the same (both null). Some treat row 2 as null and row 3 as the empty string. Pick a convention and apply it everywhere; document it for downstream consumers.
The Test CSV You Should Always Run
Save this 5-row test fixture as edge-cases.csv, including a UTF-8 BOM, and run it through any converter you are evaluating:
id,name,description,zip,joined,balance
001,"Smith, John","First line.
Second line.",02134,01/02/2023,100.50
002,"O'Brien","Quote: ""yes""",90210,2023-02-01,9007199254740993
003,"Doe, Jane",,07005,2023-12-31,0
004,"Multi,quote","He said ""hi""",10001,03/04/2023,250.00Expected output: four JSON objects, IDs preserved as strings, ZIP codes preserved with leading zeros, the balance on row 002 preserved as a string (not a JS number), the multi-line description on row 001 preserved with its embedded newline, and dates preserved in whichever string format you chose.
Most converters fail on at least two of the four rows. Pick a tool that passes all four.
Choosing the Output Shape
Once parsing is correct, you have a separate decision: what JSON structure should the output take?
| Output | Best for | Trade-off |
|---|---|---|
Array of objects [{...},{...}] | Small to medium files. Web APIs. | Cannot stream; whole array must parse before access. |
| JSON Lines (one object per line) | Large files. Streaming. BigQuery, Snowflake. | Not valid JSON as a whole file; must read line-by-line. |
| Nested JSON (dot-notation headers) | Hierarchical data with parent.child columns. | Header design must be intentional. |
| Array of arrays | Matrix data, ML inputs. | Loses readability; consumers must know column order. |
For most use cases, array of objects is the right answer. For files over a few hundred megabytes, JSON Lines wins because you can stream without loading the entire file into memory.
Why Excel-Exported CSVs Are Especially Painful
Excel writes CSV in a way that conforms to neither RFC 4180 nor common Unix conventions:
- Adds a UTF-8 BOM by default (CSV UTF-8 option).
- Uses CRLF line endings even on Mac.
- Quotes fields inconsistently (only when necessary, but the rules differ by Excel version).
- Sometimes converts straight quotes to curly quotes if a cell was edited with autocorrect on.
- Drops leading zeros if the source cell was formatted as a number, even though the cell is exported as text.
If your input came from Excel, run it through a strict parser and check the BOM, line endings, and leading zeros explicitly.
The Practical Workflow
For routine conversions, use the CSV to JSON converter with these settings, in order:
- Strip BOM if present.
- Treat all fields as strings unless you have explicit confidence in a numeric column.
- Choose array of objects for files under 50 MB, JSON Lines above that.
- Run the test fixture above through your converter once. Verify all four rows survive correctly.
- Sample-check the output: pick three records at random and compare against the source CSV by eye.
Five minutes of verification at the start prevents days of downstream bug hunting. Most CSV-to-JSON failures are silent until they show up in a report or a customer-facing UI, by which point you have to back-trace through a pipeline to find the bad record. Catch them at the conversion step instead.
The Single Most Useful Habit
Keep a one-row sample at the top of every CSV in your pipeline that exercises every edge case present in your real data. When you change tools, change versions, or onboard a new converter, run that one row through and verify the output. If the test row passes, the rest of the file probably will. If it fails, you have the bug isolated before processing a million records.
