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:
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
We'll need to work with the uncompressed version, so lets uncompress the
.gz file using
Run our query to output CSV data.
Finally, we can use the
sqlite3 binary to work with our sqlite file to generate a CSV.
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
tell it which database to run the query on:
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
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.
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.