3

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
4

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

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.