Five CSV to JSON Traps: Leading Zeros, Quotes and Newlines Inside Cells

2026-10-04 · 5 min read · CSV JSON · 简体中文

CSV looks like the easiest format there is, which is exactly why it breaks things. The difficulty is not complexity but a pile of unstated conventions that every parser guesses at. These five cover most real incidents, and each of them shows up in the output the moment you paste two rows and read it back.

1. `007` is not a number, and neither is `N/A`

Every cell in a CSV is a string; the format has no types. Converters that “helpfully” infer types turn 007 into 7, 1e5 into 100000, N/A into null and 2026-10-04 into a date in some timezone. That looks harmless and destroys information: the next program cannot tell “a postcode that lost its leading zero” from “the number seven”.

So the rule is boring on purpose: keep everything as a string during conversion; typing is business logic. CSV to JSON follows it - 007 comes out as "007", and you call Number() yourself when you mean it.

2. A quote only counts at the start of a cell

RFC 4180 is mechanical here: a cell is quoted only if its first character is "; quotes anywhere else are data. So "abc"def parses as abc"def rather than ending early at the second quote. Likewise two quotes in a row mean one quote - he said ""hi"" decodes to he said "hi".

This is also why split(",") plus split("\n") never survives contact with real data: a quoted cell may legally contain commas, newlines, or a whole multi-line address.

Set the two readings side by side on the fragments people actually meet. Each result is written as the list of rows you end up with, and each row is a list of cells:

What the file contains	`split(",")` + `split("\n")`	RFC 4180
`"1,2 Street",7`	`[['"1', '2 Street"', '7']]` — one row, three cells	`[['1,2 Street', '7']]`
`"a\nb",9`, the newline inside quotes	`[['"a'], ['b"', '9']]` — two rows	`[['a\nb', '9']]` — one row
`"abc"def`	`[['"abc"def']]`, both quotes kept	`[['abc"def']]`
a file ending in `\n`, say `a,b\n`	`[['a', 'b'], ['']]`	`[['a', 'b']]`

The first two rows are the ones that corrupt data silently; the last two only look wrong when you read the output back.

3. A newline inside a cell wrecks your row count

id,name,note
1,"Jane Wu","first line of the note
second line of the note"
2,Li Hua,

That is two records, not three lines. Count rows by splitting on \n and you invent a record that does not exist. Going the other way, such a cell has to be written out quoted, with CRLF line endings - because that is what Excel expects.

Two related details: a trailing newline at the end of the file just closes the last row rather than adding an empty record, and blank lines at the very start are skipped (with a note saying how many), because with the header option on they would otherwise become your column names.

4. When rows have different widths, say what got dropped

Real exports are ragged, and JSON cannot express “this column was simply absent”, so a converter needs an explicit policy - and has to announce it:

Short rows: missing keys become null, so every record keeps the same shape instead of growing and shrinking.
Long rows: JSON has no room for them, so the note names the row number, how many cells it had, and what the discarded cell started with. No silent truncation - that message exists precisely so you do not find out three days later.
Duplicate header names collapse, because one key holds one value; the note says so instead of letting you discover it in the output.
Blank header cells still need a key, so such a column is named after its position (column3), and the note lists which ones.
Header option off: output is an array of arrays, the only shape that survives a table with uneven rows.

5. The delimiter is often not a comma

“My file landed in one column” is nearly always a delimiter mismatch. European spreadsheet software defaults to semicolons (a comma is the local decimal point), database clients export tabs, and pipe | shows up in log dumps. The converter offers all four as buttons and the cell count changes the moment you click.

Garbled text is out of scope on purpose. By the time you paste it into a box it is already Unicode; if it was decoded as the wrong codepage upstream, it stays wrong. Go back to whatever produced it and re-save as UTF-8.

The other direction: JSON to CSV

Three shapes a JSON table can have, all of which need to be writable: an array of objects (header from the union of keys in first-seen order, empty cells where a record lacks a key), an array of arrays (rows written straight out, no header - there are no keys to build one from), and a single object (two lines: keys as the header, values as the row).

Cells holding nested objects or arrays are written as JSON text rather than flattened. “How should a sub-object spread across columns?” has no accepted answer, so a brace in the spreadsheet is the honest result.

When a browser tab is the wrong tool

Parsing is capped at 200,000 characters and 5,000 rows, and the note states how much was left out. Past that, split the file or use a real streaming tool - pandas, jq, or your database’s import command. Holding a whole export as one string in a tab is slow and fragile. And since all of it happens locally, what you paste never leaves this device, which is also why this route is meant for data small enough to paste.

Why is everything quoted in the JSON output? Because CSV has no types and strings are the only lossless choice.

Excel ate my leading zeros on open. That is Excel guessing types at import time, not the converter. Use Data → From Text/CSV and mark the column as text, or stay out of Excel for that step.

Are non-ASCII headers fine? Yes - JSON keys accept any string. What bites people is saving the file as ANSI from Excel.

Want dedupe, sorting or a SQL IN (...) list instead? Different job: the list converter moves between lines, commas, arrays and IN lists.

1. 007 is not a number, and neither is N/A