JSON to CSV and Back: Handling Nested Data and Edge Cases

CSV looks simple until you feed it real JSON — nested objects, arrays, commas inside strings, leading zeros that Excel eats alive. This guide covers how to convert JSON to CSV and back reliably, with the flattening rules, escaping mechanics, and gotchas that separate a clean round trip from corrupted data.

The core mismatch

JSON is a tree: objects nest inside objects, arrays hold more objects. CSV is a flat grid of rows and columns. Every conversion is really an answer to one question: how do you project a tree onto a grid without losing information? The techniques below are the standard answers.

Flattening nested objects with dot notation

A nested object becomes multiple columns whose headers join the key path with dots. Take this JSON:

[
  {
    "id": 1,
    "name": "Ada",
    "address": { "city": "London", "zip": "EC1A" }
  },
  {
    "id": 2,
    "name": "Linus",
    "address": { "city": "Helsinki", "zip": "00100" }
  }
]

Flattened, address.city and address.zip become their own columns:

id,name,address.city,address.zip
1,Ada,London,EC1A
2,Linus,Helsinki,00100

The dot path is reversible: when you go back to JSON, address.city rebuilds the nested object. This is exactly what the JSON to CSV tool does automatically, and the CSV to JSON tool reads the dot notation back into nested structures.

Arrays of objects

An array of flat objects is the happy path — each object is a row and the union of keys forms the header. The trouble starts with arrays nested inside a record, like a list of tags or line items:

[
  { "id": 1, "tags": ["a", "b"], "scores": [10, 20] }
]

There is no single correct CSV for this. The common strategies:

• Join into one cell. Serialize the array as a string, often pipe- or semicolon-separated: tags becomes "a|b". Simple, but you lose structure and must split it back manually.
• Index into columns. Expand to tags.0, tags.1, scores.0, scores.1. Reversible, but the column count depends on the longest array.
• Explode into rows. Emit one row per array element, repeating the scalar fields. This denormalizes the data and is what spreadsheet pivots expect.

id,tags,scores
1,"a|b","10|20"

Pick a strategy deliberately and document it, because the reverse conversion has to use the same convention.

Escaping commas, quotes, and newlines

This is where hand-rolled converters break. The CSV format (RFC 4180) has three rules you must honor:

1. A field containing a comma, a double quote, or a newline must be wrapped in double quotes.
2. A literal double quote inside a quoted field is escaped by doubling it.
3. Newlines inside a quoted field are preserved as part of the value.

Given this JSON:

[
  {
    "note": "Hello, world",
    "quote": "She said \"hi\"",
    "multiline": "line1\nline2"
  }
]

The correct CSV is:

note,quote,multiline
"Hello, world","She said ""hi""","line1
line2"

Note the doubled "" around hi, the quotes around the comma-containing field, and the genuine line break preserved inside quotes. A naive value.join(",") gets every one of these wrong and silently shifts columns. Use a real parser — or the JSON to CSV tool — which handles all three cases.

Delimiters: CSV vs TSV

Comma is the default, but it collides constantly with text that contains commas. Tab-separated values (TSV) sidestep that because tabs rarely appear inside data, which is why TSV often pastes more cleanly into spreadsheets:

id	name	city
1	Ada	London
2	Linus	Helsinki

The escaping rules still apply to whatever delimiter you choose. If you want tab output specifically, the JSON to TSV tool is purpose-built for it; otherwise pick your delimiter in the converter and keep it consistent with whatever will read the file.

Type detection going CSV to JSON

CSV has no types — every cell is text. When converting back to JSON, a good converter infers types, but inference is guesswork and can bite you:

id,active,price,zip
1,true,9.99,00100

A reasonable inference yields:

[
  { "id": 1, "active": true, "price": 9.99, "zip": "00100" }
]

id and price become numbers, active becomes a boolean. But zip is the trap: 00100 is a string with a meaningful leading zero, and aggressive inference would turn it into the number 100, destroying it. Good tooling keeps values with leading zeros as strings. When in doubt, disable type coercion and treat everything as a string, then cast deliberately downstream. The CSV to JSON tool lets you control this behavior.

Headers

The header row is the contract between the two formats. A few rules keep round trips clean:

• Ensure headers are unique. Duplicate headers cause the later column to overwrite the earlier one when building JSON objects.
• Preserve exact casing and dot paths — Address.City and address.city are different keys.
• If your data has no natural header, generate one rather than promoting the first data row, which silently drops a record.

Common pitfalls

Excel mangles your data. Spreadsheets are the number-one source of CSV corruption:

• Leading zeros vanish. 00100 becomes 100. To force text, some teams prefix with a tab or wrap as ="00100", though that is Excel-specific and pollutes the file for other readers.
• Long numbers go scientific. A 16-digit ID renders as 1.23457E+15 and loses precision. Keep large identifiers as strings.
• Dates get reformatted to the local locale, so 2026-02-22 may reappear as 22/02/2026. Store dates as ISO strings and verify after any Excel round trip.
• Encoding. Save and read as UTF-8. Without a BOM, Excel may misread accented characters; with the wrong encoding you get mojibake.

Inconsistent objects. If objects in an array have different keys, the CSV header must be the union of all keys, with empty cells where a record lacks a field. Dropping keys that appear only in later records is a frequent bug.

Trailing newlines and empty rows at the end of a file can produce a phantom object full of empty strings when parsed back. Trim before converting.

A clean round trip

Putting it together: flatten with dot notation, choose and document an array strategy, escape per RFC 4180, control type inference on the way back, and treat Excel as a hostile environment for IDs and zeros. When both directions use the same conventions, JSON to CSV to JSON returns the data you started with.

Conclusion

CSV is deceptively easy to get wrong, but the failure modes are well known: unescaped delimiters, lost nesting, and overeager type inference. Handle those deliberately and your conversions stay lossless. Drop your data into the JSON to CSV tool to flatten and export, and use CSV to JSON to bring it back with the type handling you choose. Browse the full set of tools on JSONPost for the rest of your data pipeline.