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:

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.

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.