Quick query access to CSV data using sqlite virtual tables

Created: Sep 16, 2010 by Pradeep Gowda Updated: May 22, 2020 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;
123324234|124342|342342
232|fsdfsdfsd|erwe32
sqlite> .tables
tbl
sqlite> .schema tbl
CREATE VIRTUAL TABLE tbl using csvfile(./test.csv);

Note

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.