CSV and JSON look like they should convert into each other with zero friction — rows become objects, columns become keys, done. In practice almost every real-world CSV hits at least one edge case that a naive converter gets wrong: a value that should stay a string turns into a number, a comma inside a quoted field splits into two columns, or a nested JSON object collapses into an unreadable string. None of this is a bug in the tool — it's a structural mismatch between a format with no types (CSV) and a format with strict types and nesting (JSON).
CSV Has No Types — JSON Does
Every single value in a CSV file is text, full stop. There's no way in the format itself to say "this column is a number" or "this one's a boolean." A converter has to guess the type by looking at the string, and every guessing rule breaks something:
- Leading zeros disappear. A ZIP code
"01234", an account number"00087", or a phone extension"007"gets read as the number1234,87, or7the moment a converter decides "looks numeric, convert it." The leading zero was meaningful; the converter didn't know that. - IDs that look like numbers become numbers. A product SKU or a national ID that happens to be all digits (
4102345678901) can silently overflow or lose precision once it's treated as a JSON number instead of a string, especially in JavaScript where numbers above 2^53 lose accuracy. TRUE/FALSE/Yes/Noare ambiguous. Some converters coerce these to JSON booleans, others leave them as strings — and a spreadsheet exported from Excel vs. Google Sheets vs. a database dump may spell them differently (TRUE,true,1,Y).- Empty cells are ambiguous too. An empty CSV cell could mean
null,""(empty string), or "this field doesn't apply" — CSV can't distinguish any of these, so the converter has to pick one convention.
The fix: for any column where the exact string representation matters (IDs, codes, phone numbers, ZIP codes), force it to stay a string rather than letting auto-detection guess. A converter that lets you pin specific columns to "text" avoids the leading-zero and precision problems entirely.
Commas Inside Fields Aren't Column Breaks
CSV's escaping rule is that a field containing a comma, a quote, or a line break must be wrapped in double quotes — "Smith, John" is one field, not two. The problem is that a lot of CSV in the wild was exported by tools that don't quote consistently, or was hand-edited afterward:
- A field like
Description,with a commawithout surrounding quotes will split into two columns, shifting every column after it one position to the right for that row. - A quote character inside a quoted field must be escaped by doubling it (
"She said ""hello""") — a single unescaped"prematurely closes the field and corrupts everything after it. - A quoted field can legally contain a raw line break (a multi-line address or a comment field) — a naive line-by-line parser that doesn't track "am I inside quotes" will treat that as a new row and produce a broken record with a stray fragment.
A correct CSV parser has to be quote-aware, not just split on commas and newlines — this is exactly the class of bug that makes hand-rolled line.split(',') parsing fail on real-world data.
JSON Nests — CSV Is Flat
JSON supports objects inside objects and arrays inside objects. CSV is a flat grid of rows and columns — there's no native way to represent {"address": {"city": "Lahore", "zip": "54000"}} as a single cell.
Converting JSON → CSV, a nested object usually gets handled one of two ways:
- Flattened into dotted columns —
address.city,address.zipbecome separate columns. This preserves the data but only works cleanly if every record has the same nested shape. - Stringified into the cell — the whole nested object gets dumped as a literal JSON string inside one cell, which round-trips losslessly but is unreadable in a spreadsheet and needs re-parsing to use.
Converting CSV → JSON, there's no nesting to reconstruct unless the converter specifically supports dotted-column flattening in reverse (address.city → address: { city: ... }). Plain columns always produce a flat JSON object per row — if you need nested output, the column naming has to encode that structure going in.
Arrays inside a single CSV cell (a tags column like red;blue;green) have the same problem — CSV has no standard array syntax, so a converter either splits on a delimiter you specify or leaves it as one string field.
Header Row Problems
The first row of a CSV defines the JSON keys, which creates a few failure modes that don't show up in the raw data itself:
- Duplicate column names — two columns both named
Name(e.g., a spreadsheet with "Name" for both "First Name" and a renamed leftover column) — one silently overwrites the other in the resulting JSON object, since JSON object keys must be unique. - Missing or blank headers — an unnamed column (common with an accidental extra comma from a spreadsheet export) becomes an empty-string key
""or gets a placeholder likecolumn_5, which is easy to miss until something downstream tries to read a key that doesn't exist. - Ragged rows — a row with fewer or more commas than the header row (a trailing comma, a missing trailing field) either gets padded with
null/empty values or throws off the column alignment for every field after the mismatch, depending on how strict the parser is.
Encoding and the Invisible BOM
A CSV exported from Excel on Windows frequently starts with a UTF-8 Byte Order Mark (BOM) — three invisible bytes at the very start of the file. If the converter doesn't strip it, the BOM attaches itself to the first header name, so a column that's clearly named id in every text editor actually has the key id in the resulting JSON — and any code doing row.id gets undefined while row['id'] works, which is a maddening one-off bug to track down. (This is the same invisible-Unicode problem that breaks byte-level diffs — see our diff-tool explainer for more on invisible characters like this.)
Non-ASCII characters — accented names, currency symbols, right-to-left text — are also a common breakage point if the source CSV was saved in a legacy encoding (Windows-1252, ISO-8859-1) rather than UTF-8; a converter that assumes UTF-8 will mangle those characters into replacement symbols (�) instead of erroring loudly.
Quick Checklist Before You Trust the Output
- Force ID-like and code-like columns to stay text — don't let auto-detection turn ZIP codes, phone numbers, or all-digit IDs into numbers.
- Check row count matches — if the JSON has fewer records than CSV rows (minus the header), something got merged or dropped, usually from unescaped commas or line breaks inside a field.
- Spot-check a row with a comma or quote in it — pick a field you know contains one and verify it stayed in one column, not two.
- Look for a
prefix on your first key — a classic sign of an unstripped BOM from an Excel export. - Decide your empty-cell convention up front —
null,"", or omitted key — and confirm the converter applied it consistently. - For nested JSON → CSV, check the flattened column names — dotted paths (
address.city) only make sense if every record shares the same shape; sparse or inconsistent nesting produces a ragged column set.
Quick FAQ
Why did my phone numbers lose their leading zeros after converting?
The converter auto-detected the column as numeric because every value looked like digits, and converted it to a JSON number, which drops leading zeros the same way 01234 and 1234 are the same number in any language. Mark that column as text before converting, or quote the values in the source CSV.
Why does my JSON have a weird address.city key instead of a nested object?
That's a flattening convention some CSV → JSON tools use in reverse, or more likely, the original CSV genuinely had a column literally named address.city rather than true nesting — CSV has no way to represent nested objects other than encoding the path in the column name.
My row count in JSON doesn't match my CSV row count — what happened? Almost always a field with an unescaped comma or a raw line break inside it that split one row into two, or a quoted multi-line field that got merged with the next row by a parser that isn't quote-aware. Check the row immediately before the count starts drifting.
Should I always quote every field when generating CSV from JSON? It's the safest default. Quoting only fields that "need" it (containing a comma, quote, or newline) is more compact but fragile — a value that looks safe today can pick up a comma later, and inconsistent quoting is a common source of parser bugs downstream.
Related Free Tools
- CSV to JSON — convert CSV to JSON with type detection you can override per column
- JSON to CSV — flatten JSON arrays and objects into CSV
- Why Is My JSON Invalid? — common JSON syntax errors, including ones introduced by converters
