Querying and Interacting with CSV Files More Easily with SQLite
I've recently been interacting with a number of CSV files as a data source, and I wanted to fairly quickly query and sort through the data.
As I was looking to write a Ruby script (as it's my primary scripting language) to do I what I wanted, I wondered if I could convert it to an SQLite database for easier querying.
It turns out, absolutely - it's a feature of SQLite and is super straightforward!
Let's say that we have a CSV file called
links.csv, and we want to import them.
Let's boot up SQLite by running
sqlite3 and then executing:
.mode csv .import /path/to/links.csv table_name
(Note that you need to provide a CSV header, so SQLite imports each field in the correctly named column)
You can then see what the table gets created as:
This then produces an in-memory (but can be dumped to file, so you don't have to keep re-importing) SQL database that you can now query much more easily, i.e.:
SELECT COUNT(*) FROM table_name; -- 192