This is a neat trick in a couple of steps to create a SQLite database from a gzip file.
First, make sure your text file is tab-separated, and has a header row that you would like to use as the table columns.
First, we make a FIFO object and stream the gzip file to that. Note that we background the zcat
command here.
mkfifo tempfile
zcat sprot.uniprot.proc.gz > tempfile &
This doesn’t actually uncompress your file, it just sets a 0 byte file object that will read the uncompressed file.
Now, we open SQLIte3 with the name of our (new or existing) database.
sqlite3 db.sqlite
And then set tabs
mode for the import and read the tempfile
filehandle into a new table.
.mode tabs
.import tempfile newtable
SQLite3 will automatically create the table with column names the same as the headers, and the data will be loaded.
You can check by looking at the .schema