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:
sqlite-utils memorycommand 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