How to convert CSV to JSON without losing data
August 7, 2026 · 8 min read
A spreadsheet export and a JSON API want fundamentally different shapes for the same data: CSV is a flat grid of rows and columns, while JSON APIs typically expect an array of objects, each keyed by field name. Converting between them is conceptually simple - each row becomes one object, each column header becomes a key - but a naive comma-split breaks the moment real-world data shows up with commas or quotes inside a field.
The shape mismatch
CSV has no concept of nested structure or explicit types - it's rows of text separated by a delimiter, full stop. JSON's array-of-objects shape is what most modern code actually wants to iterate over: for (const row of data) { row.name } reads naturally, whereas indexing into a raw CSV row by column position (row[2]) is brittle the moment a column gets reordered upstream.
Why the header row matters
The first row of a CSV file conventionally holds column names, and that row is what becomes your JSON object keys - without it, a converter has nothing to key each field by except its column position, which produces objects like { "0": "Ada", "1": "42" } instead of { "name": "Ada", "age": "42" }. If your source CSV genuinely has no header row, add one manually before converting; there's no way to reliably infer meaningful field names from data alone.
Quoted fields and embedded commas
The CSV spec (RFC 4180) allows a field to contain the delimiter itself, provided the whole field is wrapped in double quotes - "Smith, John" is one field, not two. A literal double quote inside a quoted field is escaped by doubling it: "She said ""hello""". A naive split(",") implementation breaks on both of these immediately, silently shifting every subsequent column over by one - which is exactly the kind of corruption that's easy to miss until a later column ends up with obviously wrong data in it.
name,note
"Smith, John","Said ""hi"" at checkout"Everything in CSV is a string
CSV has no native concept of numbers, booleans, or nulls - every cell is just text, even if it looks like 42 or true. A faithful CSV-to-JSON conversion should generally keep values as strings rather than guessing types, since silently converting "007" to the number 7 loses the leading zero, and a blank cell is ambiguous between "empty string" and "null" without more context about the source data. If your downstream code needs real numbers or booleans, do that type coercion deliberately, field by field, after conversion.
CSV vs TSV
Tab-separated values (TSV) solve the "what if my data contains commas" problem at the source by using a tab character as the delimiter instead - tabs are rare inside real text fields, which is why TSV needs quoting far less often in practice, and why copy-pasting a range directly out of Excel or Google Sheets produces TSV, not CSV.
FAQ
- What happens if a row has fewer columns than the header?
- The missing fields typically come back as empty strings, keyed by whatever header column they were missing under - the object still gets every header key, just with blank values for the columns that ran short.
- Does CSV-to-JSON conversion handle nested data?
- Not natively - CSV is inherently flat. If a cell contains its own JSON or delimited sub-values, that's a second parsing step after the CSV-to-JSON conversion, not part of it.
- My data is tab-separated, not comma-separated - does that matter?
- A good CSV-to-JSON tool auto-detects tabs when there are no commas in the input, so TSV pasted directly from a spreadsheet usually works without any manual configuration.