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, 2016 at 22:09

2 Answers 2


I found that sqlite3 custom init script can have meta-command as well as SQL statement:



# create temporary init script
cat <<EOF > $commandfile
.mode csv tablename
.import /dev/stdin tablename

# 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. May 28, 2017 at 9:30

If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this:

cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin mycsvtable'

SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. This is one of the fastest way to insert data into a SQLite database, comparable to prepared insert statements with pragma journal_mode=off.

If your CSV file has more than 999 columns, then you need to recompile SQLite and change SQLITE_MAX_VARIABLE_NUMBER from 999 to a higher number. This is because internally, SQLite uses a prepared statement bound by that limit.

  • couldn't get -separator arg to work on MacOSX, so used gzcat my_table.tsv.gz | tr '\t' ',' | sqlite3 -csv my_table.db '.import /dev/stdin my_table'
    – user553965
    Sep 13, 2020 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.