Query CSV file with SQLite

Published: | 2 min read

Updated on October 13, 2024.

Intro

The other day I had CSV file on my hands, and pondered what would be the easiest way to run queries against it. Importing to Google Sheet, Numbers, Excel etc. is naturally one option, but then again I like writing SQL much much more than random functions in Sheets / Numbers / Excel.

This lead to an idea to import the data to a temporary SQLite database, in order to cater this. After googling around for a bit, I found a perfect solution, which does not even require the creation of a temporary SQLite database file. Enter in-memory databases!

Running a single query against CSV data

The data I had looks like this:

Date,Temperature (°C),Humidity (%),Pressure (hPa),RSSI (dBm),Acceleration X (g),Acceleration Y (g),Acceleration Z (g),Voltage (V),Movement Counter (movements),Measurement Sequence Number,TX Power (dBm)
2023-12-10 14:03:31,-11.42,83.57,1008.44,-73,-0.04,0.012,1.036,2.689,154,1,4
2023-12-10 14:18:28,-11.47,83.57,1008.41,-68,-0.052,0.016,1.036,2.693,154,2,4
2023-12-10 14:33:30,-11.48,83.55,1008.39,-67,-0.044,0.016,1.032,2.686,154,3,4

Since you asked, the data comes from my Ruuvi sensors.

I wanted to aggregate the temperature for each day, and since the first column containing the date also contained the time, I needed to use substr function to get just the date.

In order to create a new in-memory SQLite database, import my CSV file to it, and to run simple aggregation over it:

sqlite3 :memory: -cmd '.mode csv' -cmd '.import "Ruuvi_data.csv" ruuvi' -cmd '.mode column' \
'SELECT substr(Date,1,10), COUNT(*), AVG("Temperature (°C)") FROM ruuvi GROUP BY substr(Date,1,10)'

And that’s it! Could not be much more simple, and no need to manage temporary files.

Load data to in-memory DB and open SQLite shell

When I need to run multiple queries, it is faster to load the data once, and run multiple queries with SQLite shell:

# load data to memory and open SQLite shell
sqlite3 :memory: -cmd '.mode csv' -cmd '.import "Ruuvi_data.csv" ruuvi' -cmd '.mode column'

# run queries:
SELECT COUNT(*) FROM ruuvi;
SELECT COUNT(*) FROM ruuvi GROUP BY substr(Date,1,10);

# exit shell
.exit

As a reference, SQLite version was 3.43.2 at the time of writing.

Further reading