Directly import a csv gzip’ed file into SQLite 3

csvimportsqlite

I'd like to import a 15GB file comma delimited gzip compressed file into Sqlite 3 without having to use temporary files.

I'd like to run a command like: 

zcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite ".import /dev/stdin surgical_code"

This decompresses the file, skips the header and tries to import.

The problem is that I am not able to specify .mode cvs and .separator "," on the same quoted command to SQlite3.

Any ideas?

Using $(echo -e 'line1\nline2') didn't work for me:

gzcat input/surgical_code.csv.gz | tail -n +2 | sqlite3 db.sqlite $(echo -e '.mode csv \n .separator \",\"\n.import /dev/stdin')

Error: mode should be one of: ascii column csv html insert line list tabs tcl

Best Answer

I found that sqlite3 custom init script can have meta-command as well as SQL statement:

#!/bin/sh

commandfile=$(mktemp)

# create temporary init script
cat <<EOF > $commandfile
.mode csv tablename
.import /dev/stdin tablename
EOF

# import
bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db
Related Question