How to automate a process with the sqlite3.exe command line tool?

Solution 1:

Create a text file with the lines you want to enter into the sqlite command line program, like this:

CREATE TABLE log_entry (  );
.separator "\t"
.import logfile.log log_entry

and then just call sqlite3 database.db < commands.txt

Solution 2:

Alternatively you can put everything in one shell script file (thus simplifying maintenance) using heredoc import.sh :

#!/bin/bash --
sqlite3 -batch $1 <<"EOF"
CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import logfile.log log_entry
EOF

...and run it:

import.sh database.db

It makes it easier to maintain just one script file. By the way, if you need to run it under Windows, Power Shell also features heredoc

In addition this approach helps to deal with lacking script parameter support. You can use bash variables:

#!/bin/bash --

table_name=log_entry

sqlite3 -batch $1 <<EOF
CREATE TABLE ${table_name} ( <snip> );
.separator "\t"
.import logfile.log ${table_name}
EOF

Or even do a trick like this:

#!/bin/bash --

table_name=$2

sqlite3 -batch $1 <<EOF
CREATE TABLE ${table_name} ( <snip> );
.separator "\t"
.import logfile.log ${table_name}
EOF

...and run it: import.sh database.db log_entry

Solution 3:

Create a separate text file containing all the commands you would normally type into the sqlite3 shell app:

CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import /path/to/logfile.log log_entry

Save it as, say, impscript.sql.

Create a batch file which calls the sqlite3 shell with that script:

sqlite3.exe yourdatabase.db < /path/to/impscript.sql

Call the batch file.

On a side note - when importing, make sure to wrap the INSERTs in a transaction! That will give you an instant 10.000% speedup.

Solution 4:

I just recently had a similar problem while converting Firefox' cookies.sqlite to a text file (for some downloading tool) and stumbled across this question.

I wanted to do that with a single shell line and that would be my solution applied to the above mentioned problem:

echo -e ".mode tabs\n.import logfile.log log_entry" | sqlite3 database.db

But I haven't tested that line yet. But it worked fine with the Firefox problem I mentioned above (btw via Bash on Mac OSX ):

echo -e ".mode tabs\nselect host, case when host glob '.*' then 'TRUE' else 'FALSE' end, path, case when isSecure then 'TRUE' else 'FALSE' end, expiry, name, value from moz_cookies;" | sqlite3 cookies.sqlite

Solution 5:

sqlite3 abc.db ".read scriptname.sql"