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:

.schema

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