JSON to CSV Converter

Convert JSON to CSV, even when the records are wrapped

Most JSON that people want as a spreadsheet does not arrive as a clean array. It comes from an API that wraps the rows in data, items or results next to paging fields. This JSON to CSV converter looks inside the document for that list of records, turns each record into a row and each field into a column, and writes CSV that Excel, Google Sheets, pandas or a database import can read.

Example: a paginated API response

{
  "page": 1,
  "total": 2,
  "results": [
    { "id": 1, "name": "Ava, Jr.", "tags": ["pro", "beta"], "address": { "city": "Denver" } },
    { "id": 2, "name": "Noah Kim", "email": "noah@example.com", "tags": ["free"], "address": { "city": "Seoul" } }
  ]
}

With the default options the output is:

id,name,tags.0,tags.1,address.city,email
1,"Ava, Jr.",pro,beta,Denver,
2,Noah Kim,free,,Seoul,noah@example.com

Notice what happened. The converter picked results as the table and left out page and total, because those are not rows. The name containing a comma was quoted. The email column appears last because it was first seen in the second record, and Ava's cell for it is empty.

How the table is found

• A top-level array is used as it is.
• An object with a single key is unwrapped, repeatedly, so {"data": {"users": [ … ]}} reaches the users.
• An object with one array of objects plus plain values (strings, numbers, null) uses that array. The plain values are dropped.
• Anything else, such as an object with two arrays, becomes a single row.

Columns are the union of the keys of all records, in first-seen order, so records with different shapes still line up. An array of plain values produces one column named value. An array of arrays is treated as a grid, and when Include header row is on and the first inner array contains only strings, that array becomes the header.

Flattening nested JSON

Flatten nested objects is on by default. Each nested object contributes one column per leaf, joined with dots (address.city), and each array element gets its position as the last segment (tags.0, tags.1). The widest record decides how many positional columns exist.

With flattening off, the columns are only the top-level keys and any object or array is written as compact JSON inside the cell. With a semicolon delimiter the first record of the example becomes:

id;name;tags;address;email
1;Ava, Jr.;"[""pro"",""beta""]";"{""city"":""Denver""}";

That layout keeps the column count fixed, which is handy when arrays vary wildly in length or when a later step will parse the JSON column again.

Delimiter and quoting options

• Delimiter – comma, semicolon, pipe or tab. Spreadsheet apps set to European locales often expect semicolons, because the comma is their decimal separator.
• Include header row – untick it to append rows to an existing file that already has headers.
• Quote all fields – wraps every cell in double quotes, including numbers and empty cells. Without it, only cells containing the delimiter, a double quote or a line break are quoted. Quotes inside values are always doubled, so Noah "NK" Kim is written as "Noah ""NK"" Kim".

Edge cases to check before you import

• Empty arrays and null objects. A record with "tags": [] or "address": null adds a separate tags or address column with an empty cell, next to the tags.0 or address.city columns from other records. Delete that column if your import does not want it.
• Line breaks in values stay inside a quoted cell. That is valid CSV, but tools that read one line per row will split the record.
• Large integers lose precision when the JSON is parsed: 12345678901234567890 is written as 12345678901234567000. Send 64-bit IDs as strings.
• Leading zeros in strings such as "007" are kept in the CSV, but a spreadsheet may drop them when it opens the file. If that matters, JSON to Excel writes such values as text cells.
• Accented characters are written as UTF-8 without a byte order mark. Some Excel versions assume a legacy encoding when you double-click a CSV, so José may show up garbled. Import the file through Data › From Text/CSV and pick UTF-8, and the text displays correctly.
• Booleans and nulls become the words true and false and an empty cell. CSV has no types, so the program that reads the file decides what they mean.

Pulling data straight from an API

You do not need to save a response to disk first. Load URL fetches a public JSON endpoint into the editor, the converter unwraps the records and Download saves json-to-csv.csv. Endpoints that require an authorization header or a login cookie cannot be loaded that way; copy the response from your browser's developer tools or from curl and paste it instead.

To check the result as a grid, open it in the CSV viewer. For the reverse direction, CSV to JSON rebuilds records from the rows.

Privacy

Parsing and conversion run in your browser, and the JSON is not uploaded. Load URL fetches the address through our server, and Save & Share stores the input so the link works; nothing else is sent.

Convert JSON to CSV in Python

Load the array with json.loads and write it with csv.DictWriter. The writer needs the full list of column names up front, and this is where scripts break: if you take the keys of the first object only, a later object with an extra key raises ValueError: dict contains fields not in fieldnames. Collect the keys from every record first; missing values are then written as empty fields. For a real file, open it with newline="".

import csv
import io
import json

data = '[{"id": 1, "name": "Ann"}, {"id": 2, "name": "Bob, Jr.", "city": "Rome"}]'
rows = json.loads(data)

fields = []                      # every key, in first-seen order
for row in rows:
    fields += [key for key in row if key not in fields]

out = io.StringIO()              # for a file: open("out.csv", "w", newline="")
writer = csv.DictWriter(out, fieldnames=fields, lineterminator="\n")
writer.writeheader()
writer.writerows(rows)
print(out.getvalue())

The converter above also flattens nested objects into dotted column names, a quick way to check the header your script should produce.