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 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,4Since 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.
As a reference, SQLite version was 3.43.2 at the time of writing.