JSON
Open a JSON or JSON Lines file as a table, with its numbers as written, and export any file as JSON, an array of rows keyed by the column names, or as JSON Lines.
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 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
and write the tab as JSON (in the desktop app, from ). 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.