Is it possible to import .gzip file into sqlite / Could I skip some column while importing?

Question

I tried to play around with .import but it seems to limited with csv and delimited file. Is it possible to import gzip file ? or at least, pipe from command line ?

Also, could I skip some un-wanted column like mysql "LOAD DATA INFILE" ?

Answer 1

If you don't want to use named pipes, you could also:

zcat $YOURFILE.gz | sqlite3 $YOURDB.sqlite ".import /dev/stdin $TABLENAME"

If you need to modify stuff before import, you could use perl (or awk, sed, whatever) between the zcat and sqlite commands.

For example, if your file already uses the pipe character as a delimiter and you would like to import only columns 0 to 3 and 5 to 6:

zcat $YOURFILE.gz | perl -F'\|' -anle 'print join("|", @F[0..3,5..6])' | sqlite3 $YOURDB.sqlite ".import /dev/stdin $TABLENAME"

Answer 2

$ mkfifo tempfile
$ zcat my_records.csv.gz > tempfile

This works like magic!

Although the mkfifo does create temporary file, the size of this file is 0 byte. When running this command $ zcat my_records.csv.gz > tempfile, it will halt at the command prompt. This allows you to run

sqlite3> .import tempfile db_table

After sqlite3 finished importing the named pipe, zcat command will also finish running. You can then remove the named pipe.

$ rm -f tempfile