JSON to CSV

About JSON to CSV Converter

JSON to CSV Converter turns JSON (JavaScript Object Notation) and JSON Lines into CSV (comma-separated values), the plain table that Excel, Google Sheets and databases open. Paste your JSON and the CSV shows as you type. Copy it, or download it as a .csv file.

The converter reads your JSON with a strict parser. When something is wrong, it rings the exact spot, gives the line and column, and says how to fix it. Every number stays as you wrote it, so 19.0 stays 19.0 and a 20-digit ID keeps every digit.

How to use it

1. Paste JSON or JSON Lines, drop a file, or pick an example.
2. Look at the CSV, or open Table to see it as rows and columns.
3. Open Columns to leave out columns, or to pick the list the rows come from. Each column shows a sample value.
4. Choose the delimiter in the Result header. Open Options for arrays, nested keys, the header row, null values, line ends and Excel.
5. Press Copy or Download in the Result pane. Both take the CSV, in every view.

How JSON becomes a table

Each object is a row

An array of objects is the usual input. Each object becomes a row, and each key becomes a column. A key that only some objects have still gets a column; the other rows get an empty cell, and Notes says how many rows have it.

[
  { "id": 1, "name": "Ada", "role": "admin" },
  { "id": 2, "name": "Alan" }
]

id,name,role
1,Ada,admin
2,Alan,

Nested objects become columns

Keys inside a nested object are joined to the key around them with a dot: address.city. Under Nested keys in Options, you can join them with / or _ instead.

[
  { "id": 1, "address": { "city": "London", "zip": "W1" } }
]

id,address.city,address.zip
1,London,W1

Arrays, three ways

By default each array item gets a column with its position: tags.0, tags.1. Choose JSON text to keep the whole array in one cell, or One row per item to give each item in the array its own row. One row per item works on lists of objects and on lists of plain values, such as tags:

[
  { "order": "1001", "items": [ { "sku": "MUG", "qty": 2 }, { "sku": "TEE", "qty": 1 } ] }
]

order,items.sku,items.qty
1001,MUG,2
1001,TEE,1

API responses

Many APIs wrap the records in an object, such as { "page": 1, "data": [ ... ] }. The converter finds the biggest list of objects and makes the rows from it. Pick another list in the Columns view, or choose the whole object as one row. When you paste new JSON, the converter picks the list again.

JSON Lines

JSON Lines (also called JSONL or NDJSON) has one JSON value on each line. Logs and data exports often use it. Each line becomes a row:

{"level":"info","msg":"started","port":3000}
{"level":"warn","msg":"slow query","ms":912}

level,msg,port,ms
info,started,3000,
warn,slow query,,912

Text with commas, quotes and line breaks

When a value holds the delimiter, a double quote or a line break, the converter puts it in double quotes and doubles each double quote inside it, as RFC 4180 says. Every spreadsheet reads it back as one cell.

sku,title,note
A-1,"Chair, oak","He said ""sit"""

A key used twice

When one object has the same key twice, the CSV keeps the last value, like most programs that read JSON. Notes shows where the key repeats.

Open the CSV in Excel or Google Sheets

Excel

1. Turn on Excel in Options, then press Download. The file starts with a byte order mark, so Excel shows å, é and emoji right. Text that starts with = + - or @ gets a ' in front, so Excel does not run it as a formula.
2. A double-click opens the file. To choose how each column reads, open Excel and go to Data > From Text/CSV instead.
3. In that import, set a column to Text when it holds zip codes with a leading 0, IDs longer than 15 digits, or values such as 1-2. Otherwise Excel drops the 0, turns the last digits into zeros, or makes a date. Notes tells you which columns need this.
4. Where Excel uses a comma for decimals, choose Semicolon as the delimiter in the Result header.

Microsoft explains both steps in Opening CSV UTF-8 files correctly in Excel and Import or export text files.

Google Sheets

Download the CSV. In Google Sheets, go to File > Import, upload the file, and choose an import option. Leave the separator on Detect automatically, or pick the one you chose here. See Google's Import data sets and spreadsheets.

Common errors

A missing comma

Two values in a row need a comma between them. The converter rings the value that comes after the missing comma.

{ "id": 1, "name": "Ada" "role": "admin" }

{ "id": 1, "name": "Ada", "role": "admin" }

A comma before a closing bracket

JSON does not allow a comma after the last item. JavaScript does, so this error is common in JSON copied from code.

[
  { "id": 1 },
  { "id": 2 },
]

[
  { "id": 1 },
  { "id": 2 }
]

Objects one after the other

Several objects with no list around them are neither JSON nor JSON Lines. Put each object on one line for JSON Lines, or put them in [ ] with commas between them.

{
  "id": 1
}
{
  "id": 2
}

{"id": 1}
{"id": 2}

Single quotes

JSON needs double quotes around keys and text. Single quotes come from Python or JavaScript objects.

[{ 'id': 1, 'name': 'Ada' }]

[{ "id": 1, "name": "Ada" }]

Behind the scenes

Our own strict parser reads the JSON, the same one as in the JSON Validator. It keeps each number as written, so no digit is lost on the way to the CSV. The CSV follows RFC 4180. A background thread converts big files, so the page does not freeze. To go the other way, paste a CSV into the CSV Formatter and open its JSON view.