files and formats

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 named key.

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, -…) become null. Anything else stays text: 00123 keeps its zeros, and $12 or 12 kg stay 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 null for 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.