JSON — Trusty CSV User Guide
Opening a JSON file
Open a .json, .jsonl or .ndjson file like any other (Ctrl + O``⌘ O, or drop it on the window), and it opens as a table:
- An array of objects gives a row per object, and their keys become the header row, in the order they first appear. A row that lacks one of the keys has that cell empty.
- Objects inside an object become columns named by their path,
address.city, next to each other. - An array of arrays gives a row per array, without a header row of its own: Trusty CSV works out whether the first row is one, as it does for a CSV file.
- JSON Lines gives a row per line.
- Rows inside an object, as many web services send them,
{"total": 120, "data": [...]}: the rows are the longest list of objects in the file, and a message says which list it was. The rest of the file is left out. - An object of objects,
{"ES": {...}, "FR": {...}}, gives a row per key, with the key in a first column namedkey.
Values come in as they are written: text as it is, and numbers with the digits in the file, so 1.50 stays 1.50 and a long id keeps all its digits. true and false come in as words, and null as an empty cell.
Lists in a value
A list of words or numbers, [ "red", "blue" ], comes in as red; blue, ready for Rows › Split Values into Rows… if each one should be a row of its own.
If a list in the same column holds anything else (objects, other lists, empty values), or a value in that column has a semicolon in it, every list in that column stays as JSON, [{"sku":"A1","qty":2}], so that nothing is lost. After opening the file, a message says which columns have lists, and how they came in.
Saving
The tab is a copy of the file as CSV, named after it, and the JSON file is never overwritten. As with an Excel workbook, Ctrl + S``⌘ S does a Save As, and the dialog says so. For orders.json, it suggests orders.csv, or orders (edited).json if you choose JSON as the format.
Not everything comes back the same way: a JSON file can hold things a table can’t, so objects inside the rows are columns now and lists are text.
Exporting as JSON
File › Download as › JSON (.json) and File › Download as › JSON Lines (.jsonl) write the tab as JSON (in the desktop app, from File › Export as). They are also formats of Ctrl + Shift + S``⇧ ⌘ S.
What the JSON looks like
With a header row, each row becomes an object, its values keyed by the names of the columns:
[
{"id":1,"name":"Ada Lovelace","city":"London","joined":"2024-03-01"},
{"id":2,"name":"Grace Hopper","city":"New York","joined":"2024-03-09"}
]
Each row is on its own line, so that the file can be read and compared line by line. Without a header row, each row becomes an array of its values.
- Numbers. In a column of numbers (one that Trusty CSV lines up as numbers), plain numbers become JSON numbers, with a decimal comma read as such, and empty or missing values (
NA,null,-…) becomenull. Anything else stays text:00123keeps its zeros, and$12or12 kgstay as they are written. - Text. Everything else is a string, exactly as in the file.
- Names. A column with no name becomes
column3(its number); a name used twice gets a number after it:name,name_2. - A row with fewer values than there are columns gets
nullfor the ones it lacks. - Empty lines are left out, and the file is UTF-8.
JSON Lines
JSON Lines (.jsonl) writes the same objects, one per line, with no brackets around them: the format that tools for logs, data pipelines and machine learning read one record at a time.
Exporting doesn’t change the tab: it stays a CSV file, and unsaved changes stay unsaved.