Move a JSON record set into CSV without rounding IDs
Admin ·
A CSV export needs one header row even when records have different keys. Inspect the inferred columns before deciding what the receiving application needs.
Choose the columns for an export
Try [{"id":9007199254740993,"info":{"code":"A"}},{"id":"0007","note":"=1+2"}]. The header is id, info, note. The first ID keeps all sixteen digits. The nested object occupies one cell. The second row has an empty info cell.
Enter 3,1 in Columns to export and convert again. The first column is now note and the second is id. The text =1+2 stays literal in the download. Import this column as text in spreadsheet software that evaluates formulas.
RFC 8259 — JSON data interchange formatRFC 4180 — CSV format and media type
Read the data back
Switch to CSV to JSON and supply the CSV. Each cell becomes a string. The number 7 and the string "7" both become the text 7 in CSV, so their original JSON types cannot be recovered from that table alone.
Preserve a postal code across a spreadsheet handoff
Convert this array of objects to CSV. Both postal codes must be written with their initial zero. Open the exported file in a text editor before opening it in a spreadsheet.
[{"town":"Newton","postal":"02458"},{"town":"Boston","postal":"02108"}]- If the raw CSV contains 02458 but the spreadsheet shows 2458, choose Text for the postal column during import. Quoting a CSV value alone does not tell every spreadsheet which type to assign.
Common questions
How is JSON null represented?
It becomes a cell containing the four letters null. A missing key produces an empty field. A JSON string containing null has the same CSV spelling as the JSON literal.
Can a record contain a repeated key?
Repeated keys at the top level of a record are rejected to prevent one value from silently replacing another. Nested objects are preserved as JSON text.
Sources
Put it into practice.
- JSON and CSV Converter
- NDJSON to CSV Converter
- CSV Viewer and HTML Table Exporter
- CSV and TSV Validator
Related guides
- Reading JSON Lines as a table
- Browsing and exporting a CSV table
- Checking delimited records before import
- Choosing separators for delimited text
- Keeping types when converting JSON and YAML
- Interpret a PDF form’s JSON field data
- Repair a Markdown Table with Uneven Rows
- Sort Exact IDs in Nested JSON Records
- Check a UK Address Card and Its CSV
- Map the Columns for a Market Stall Chart
- Extract the Second Note Column