Can I have a CSV of that SQLite file please?

If you have a sqlite file, you can easily get a CSV file from a table using a single one liner snippet of code.

First, fetch the file

We need to get the file from somewhere. wget or something similar should work on most platforms. I'm using a notebook here, which lets you call common unix commands by adding ! at the beginning of the line:

! wget https://tgwf-green-domains-live.s3.nl-ams.scw.cloud/green_urls_2022-05-06.db.gz
2.9s

This should give us our file on a local file system.

Next get it ready to run a query to output data as CSV

Look for the file named green_urls_2022-05-06.db.gz when we call ls:

!ls
1.3s

We'll need to work with the uncompressed version, so lets uncompress the .gz file using gunzip

! gunzip green_urls_2022-05-06.db.gz
3.7s

Run our query to output CSV data.

Finally, we can use the sqlite3 binary to work with our sqlite file to generate a CSV.

We:

  1. pass in a couple of flags to say we want the output to be in CSV format, and to give us the headers too: sqlite3 -csv -header

  2. tell it which database to run the query on: green_urls_2022-05-06.db

  3. provide the query we want: "select * from greendomain limit 10" We limit the query for this example but you'd likely remove the LIMIT clause if you want the full table

  4. tell it where to pipe the output with > results/output.csv. In this case Nextjournal, the platform we're using has a nice feature where if you pipe output in a directory called results, then it'll render it using the appropriate viewer. You can click on three dot menu in the code cell below to change the result view to provide a link to a file to download as well.

! sqlite3 -csv -header green_urls_2022-05-06.db "select * from greendomain limit 10"  > results/output.csv
1.0s

Adapt for your own purposes

If you fork this notebook (you'll likely need to sign up, sorry), you can run the command and remove the LIMIT clause to generate a CSV, or change where you're downloading the file to fetch new versions.

You can also download this as a jupyter notebook .ipynb file, to run anywhere you can run a jupyter notebook.

Runtimes (1)