INPUT JSON
CSV OUTPUT
Turn a JSON array into CSV you can open in a spreadsheet. The keys of every object together make up the header row. Any value that contains the delimiter, a double quote or a line break is wrapped in quotes with its inner quotes doubled, as RFC 4180 specifies.
[
{
"id": 1,
"name": "John Doe",
"email": "john@example.com",
"roles": ["admin", "editor"],
"active": true
},
{
"id": 2,
"name": "Jane Smith",
"email": "jane@example.com",
"roles": ["user"],
"active": false
}
]id,name,email,roles,active
1,John Doe,john@example.com,"[""admin"",""editor""]",true
2,Jane Smith,jane@example.com,"[""user""]",falsePick Semicolon when the file is for Excel in most of Europe. Excel splits on the list separator of the system locale, so a comma file opens there as a single column. Google Sheets lets you choose the separator on import.
MySQL's LOAD DATA INFILE and PostgreSQL's COPY both read CSV. Neither reads a JSON array directly.
Turn a day of API responses or log lines into a flat file that someone else can sort and filter.
const rows = [
{ id: 1, name: "Ada, Lovelace", city: "London" },
{ id: 2, name: 'Grace "Amazing" Hopper', city: "New York" },
];
const escape = (v) => {
if (v === null || v === undefined) return "";
const s = typeof v === "object" ? JSON.stringify(v) : String(v);
return /[",\r\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
};
const headers = Object.keys(rows[0]);
const csv = [
headers.join(","),
...rows.map((r) => headers.map((h) => escape(r[h])).join(",")),
].join("\r\n");
console.log(csv);For large or irregular data use the papaparse package: Papa.unparse(rows) handles quoting and mixed keys.
<?php
$json = '[{"id":1,"name":"Ada, Lovelace","city":"London"},
{"id":2,"name":"Grace \"Amazing\" Hopper","city":"New York"}]';
$rows = json_decode($json, true, 512, JSON_THROW_ON_ERROR);
$out = fopen('php://output', 'w');
fputcsv($out, array_keys($rows[0]), ',', '"', '\\', "\r\n");
foreach ($rows as $row) {
fputcsv($out, $row, ',', '"', '\\', "\r\n");
}
fclose($out);PHP 8.4 warns if you omit the $escape argument. The sixth argument sets the line ending and needs PHP 8.1. Swap php://output for a file handle to write to disk.
import csv, json, sys
rows = json.loads('[{"id":1,"name":"Ada, Lovelace","city":"London"},'
'{"id":2,"name":"Grace \\"Amazing\\" Hopper","city":"New York"}]')
writer = csv.DictWriter(sys.stdout, fieldnames=rows[0].keys(), lineterminator="\r\n")
writer.writeheader()
writer.writerows(rows)
# To a file instead: open("out.csv", "w", newline="", encoding="utf-8-sig")Pass newline="" when opening the file, or on Windows every line ending comes out doubled.
RFC 4180 is the closest thing CSV has to a standard, and it is short: CRLF line endings, double quotes around any field that contains the delimiter, a quote or a line break, and inner quotes doubled. Most parsers accept LF too. Emit CRLF when the file is headed for Excel, since Windows tools are the strictest about it.
CSV has no types. Every value is text until the reader guesses, and spreadsheets guess badly: Excel turns 1e5 into 100000 and 3-4 into a date. When values must survive the round trip intact, use the JSON to XLSX tool instead.
This tool follows the specifications below. Read them when you need the exact normative wording, for example to settle an edge case between two implementations.
Nested objects and arrays are written as a JSON string inside a single CSV cell, so no data is lost and the table stays valid. Parse that cell again later, or flatten the objects to dotted keys such as address.city first if you want separate columns.
Yes. Choose Custom in the separator dropdown and type any character or string, or pick a preset: comma, semicolon, tab or pipe. Use semicolon for Excel in most European locales.
An array of flat objects works best: each object becomes a row and each key a column. A single object becomes a one-row table. The header is the union of every object's keys, so a key missing from some objects leaves empty cells in those rows.
Yes. A field that contains the delimiter, a double quote or a line break is wrapped in double quotes, and inner quotes are doubled, following RFC 4180. Lines end with LF rather than the CRLF the RFC specifies, which Excel and Google Sheets both read without trouble.
Yes. Download saves the output as a .csv file, and Copy puts it on the clipboard.
CSV to JSON converts in the other direction. When the recipient needs a real spreadsheet file with typed cells, use JSON to Excel. Run input that will not parse through the JSON formatter first.