Can I import a .gzip file into sqlite / Can I skip some column when importing?
If you don't want to use named pipes, you can also:
zcat $YOURFILE.gz | sqlite3 $YOURDB.sqlite ".import /dev/stdin $TABLENAME"
If you need to change the material prior to import, you can use perl
(or awk
, sed
whatever) between the teams zcat and sqlite.
For example, if your file already uses the pipe character as a delimiter and you only want to import 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"
a source to share
$ mkfifo tempfile $ zcat my_records.csv.gz > tempfile
It works like magic!
Although mkfifo
it does create a temporary file, the size of this file is 0 bytes. Running this command $ zcat my_records.csv.gz > tempfile
will stop it at the command line. This allows you to run
sqlite3> .import tempfile db_table
When sqlite3
the named pipe has finished importing, the command ends zcat
. Then you can remove the named pipe.
$ rm -f tempfile
a source to share
You can create a named pipe. It will act like a regular file, but it will be unpacked on the fly. SQLite won't know anything about this.
It turns out that the example on wikipedia with gzip. http://en.wikipedia.org/wiki/Named_pipe
a source to share
You can write a data parser that transforms it into a series of SQL statements. Perl is a good language for this. It can even handle gzip'd files .
Are you running this on * Nix OS? If so, you can create a temporary file to store the unpacked data:
tf="$(mktemp)" &&
zcat <my_records.csv.gz >"$tf"
sqlite3 /path/to/database.sqlite3 ".import $tf"
rm -f "$tf"
a source to share