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

Written by Jamie Tanna's profile image Jamie Tanna on , and last updated on .

Content for this article is shared under the terms of the Creative Commons Attribution Non Commercial Share Alike 4.0 International, and code is shared under the Apache License 2.0.

#blogumentation #csv #sqlite.

This post was filed under articles.

Related Posts

Other posts you may be interested in:

Interactions with this post

Interactions with this post

Below you can find the interactions that this page has had using WebMention.

Have you written a response to this post? Let me know the URL:

Do you not have a website set up with WebMention capabilities? You can use Comment Parade.