How to convert JSON to CSV without losing data

Published

JSON is a tree. CSV is a grid. Converting between them is easy for simple data and surprisingly easy to get wrong for real data. These are the four problems that come up most often.

1. Nested objects

A record like this has no obvious column layout:

{ "id": 1, "address": { "city": "London", "country": "UK" } }

The usual answer is flattening: each nested field becomes a column named with a dotted path, such as address.city and address.country. Arrays are harder, because a list can have any length. Writing the array as JSON text in a single cell keeps the data intact and lets you convert it back.

2. Records with different keys

APIs often omit empty fields, so the first record may not have every column. A good converter collects the keys from every record, not just the first one, and leaves cells empty where a record has no value.

3. Numbers that change

JavaScript stores numbers as 64-bit floating point, so integers above 9,007,199,254,740,991 are rounded. An ID like 12345678901234567890 silently becomes 12345678901234567000 in many tools. Keep such values as text, or use a converter that preserves the original digits.

4. Leading zeros and codes

Going the other way, a CSV value of 02115 (a US ZIP code) looks like a number. Converting it to 2115 destroys it. Only treat a value as a number if it reads back exactly the same, and leave everything else as text.

Quoting rules

In CSV, a value containing the delimiter, a double quote or a line break must be wrapped in double quotes, and quotes inside it are doubled: "She said ""hi""". Spreadsheet apps follow these rules (RFC 4180), so hand-written CSV that ignores them often opens with shifted columns.

Try it

The JSON to CSV and CSV to JSON converters on this site handle all four problems, and run entirely in your browser.