4

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

  • Oh well. echo ... | sqlite3 ... would work, but then you'd need to get the import file from a fifo. – CL. Feb 14 '16 at 22:09
5

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
  • It might be better to create table explicitly (add CREATE TABLE … ; statement at the beginning of init script, and then also skip first line of csv file with tail -n +2), otherwise sqlite3 will make all columns of type TEXT which might be not the best result, and also lack of PRIMARY KEY makes the file larger too. Second improvement is rm $commandfile at the end of the script, because otherwise it might be never deleted on some systems. – Sarge Borsch May 28 '17 at 9:30
0

If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this:

cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin mycsvtable'

SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. I suspect this is the fastest way to insert data into a SQLite database, faster even than prepared insert statements with pragma journal_mode=off.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.