Quick query access to CSV data using sqlite virtual tables

Published: 2010-09-16 by Pradeep Gowda.
Tagged: code, sqlite

You want to run quick query on a CSV file, but you are not thrilled at having to run through the rigmarole of creating a table definition using CREATE TABLE and then loading the data into the database etc., then sqlite’s virtual table is handy.

The virtual table functionality is available as a SQLite extension.

Download CSVFile extension.

Build the extension:

$ unzip csvfile.zip && cd csvfile
$ make

Test the extension:

$ cd csvfile
$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load ./csvfile/virtcsv.so
sqlite> create virtual table tbl using csvfile(./test.csv);
sqlite> select * from tbl;
sqlite> .tables
sqlite> .schema tbl
CREATE VIRTUAL TABLE tbl using csvfile(./test.csv);


Next time when you start sqlite3, don’t forget to load the extension first. I recommend copying virtcsv.so to a more permanent location like /usr/local/lib.