TIL how to scan, search, filter and rejigger CSV & JSON data using `sqlite-utils` 💻

sqlite-utils is a handy little CLI tool to work with CSV and JSON data. Its v3.10 update introduced a new memory command that makes it trivially easy to pass raw data into the tool without extra steps:

The new sqlite-utils memory command can import CSV and JSON data directly into an in-memory SQLite database, combine and query it using SQL and output the results as CSV, JSON or various other formats of plain text tables.

Here’s an example — fetching a JSON file of public RECUP locations, then printing a table of whose name contains “cafe”, sorted by ZIP code:

curl -s "https://partner.recup.de/files/export/locations/location_exports.json" \
  | sqlite-utils memory - '
      SELECT zipcode, city, name
      FROM stdin
      WHERE name LIKE "%cafe%"
      ORDER BY zipcode
      LIMIT 10
    ' -t

In his announcement blog post for v3.10, author Simon Willison also showed how to combine different sources of data, i.e. different files, using sqlite-utils. Pretty exciting stuff. 🤯

I’m going to have fun with this!

Tags: #til #sqlite #json #csv