Working with CSV in SQLite

Recently I came across a csv with some reports I needed to work with, and since
it was quite a lot of data I wanted some sane way to query it, so I remembered
a neat little trick I discovered together with
@leif time ago.

Without much hassle you can make sqlite import csv

get the name for the columns, this can be done in most cases simply via

    $ head -n1 mycsv.csv
    > foo,bar,baz

create a table in sqlite to import into, the column names are just copy and
paste (for persistence pass a db file name, but this is optional)

    $ sqlite3 mydb.db
    >> create table mycsv(foo,bar,baz);

Still in sqlite import the csv

    >> .separator ',' 
    >> .import mycsv.csv mycsv

That’s it, since sqlite actually does not care about the types unless you want
it to this works for about any csv. Now you can use the power of SQL to work
with the data from the csv.

Just a nice little trick to make working with csv data just a little bit nicer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s