4

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" ?

Tg.
  • 5,608
  • 7
  • 39
  • 52

5 Answers5

16

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"
mivk
  • 13,452
  • 5
  • 76
  • 69
11
$ 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
Bhoom Suktitipat
  • 2,147
  • 2
  • 17
  • 11
  • I was new to `mkfifo` when I found this answer and found these answers helpful: http://stackoverflow.com/questions/4113986/example-of-using-named-pipes-in-linux-bash and http://askubuntu.com/questions/449132/why-use-a-named-pipe-instead-of-a-file – cjrieds Jun 22 '16 at 05:14
3
zcat data.gz |\
  cat <(echo -e ".separator ','\n.import /dev/stdin dest_table") - |\
  sqlite3 db.sqlite

works nicely (linux).

nefuson
  • 171
  • 1
  • 4
  • 1
    Thanks nefuson! I couldn't get any of the other suggestion working for my use case (piping out of 7z-encrypted archive), but your brillant hack did it ;-) – lyderic Sep 23 '17 at 14:55
1

You can create a named pipe. It will act like a normal file but decompress on the fly. SQLite will know nothing about it.

It turns out the example on wikipedia is with gzip. http://en.wikipedia.org/wiki/Named_pipe

Louis-Philippe Huberdeau
  • 5,341
  • 1
  • 19
  • 22
  • For some reason, I've always had problems with named pipes; if there's going to be a complicated file operation with a lot off seeking back and forth through it, it's prone to messing up. – amphetamachine Apr 20 '10 at 14:54
1

You could write a parser for data that would convert it to a series of SQL statements. Perl is a good language for that. It can even handle gzip'd files.

Are you running this in a *Nix OS? If so, you could create a temporary file to hold the decompressed data:

tf="$(mktemp)" &&
zcat <my_records.csv.gz >"$tf"
sqlite3 /path/to/database.sqlite3 ".import $tf"
rm -f "$tf"
amphetamachine
  • 27,620
  • 12
  • 60
  • 72
  • Well, I use ubuntu but I prefer "on the fly" instead of creating temporary file, since the data I working with is pretty huge when uncompressed. – Tg. Apr 20 '10 at 15:19