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

batch-filescriptingsqlite

I'm trying to bulk load a lot of data ( 5.5 million rows ) into an SQLite database file.
Loading via INSERTs seems to be far too slow, so I'm trying to use the sqlite3 command line tool and the .import command.

It works perfectly if I enter the commands by hand, but I can't for the life of me work out how to automate it from a script ( .bat file or python script; I'm working on a Windows machine ).

The commands I issue at the command line are these:

> sqlite3 database.db
sqlite> CREATE TABLE log_entry ( <snip> );
sqlite> .separator "\t"
sqlite> .import logfile.log log_entry

But nothing I try will get this to work from a bat file or python script.

I've been trying things like:

sqlite3 "database.db" .separator "\t" .import logfile.log log_entry

echo '.separator "\t" .import logfile.log log_entry' | sqlite3 database.db

Surely I can do this somehow?

Best Solution

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

Related Solutions

Windows – How to you find and replace text in a file using the Windows command-line environment

A lot of the answers here helped point me in the right direction, however none were suitable for me, so I am posting my solution.

I have Windows 7, which comes with PowerShell built-in. Here is the script I used to find/replace all instances of text in a file:

powershell -Command "(gc myFile.txt) -replace 'foo', 'bar' | Out-File -encoding ASCII myFile.txt"

To explain it:

powershell starts up powershell.exe, which is included in Windows 7
-Command "... " is a command line arg for powershell.exe containing the command to run
(gc myFile.txt) reads the content of myFile.txt (gc is short for the Get-Content command)
-replace 'foo', 'bar' simply runs the replace command to replace foo with bar
| Out-File myFile.txt pipes the output to the file myFile.txt
-encoding ASCII prevents transcribing the output file to unicode, as the comments point out

Powershell.exe should be part of your PATH statement already, but if not you can add it. The location of it on my machine is C:\WINDOWS\system32\WindowsPowerShell\v1.0

Update
Apparently modern windows systems have PowerShell built in allowing you to access this directly using

(Get-Content myFile.txt) -replace 'foo', 'bar' | Out-File -encoding ASCII myFile.txt

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

There are a few steps to see the tables in an SQLite database:

List the tables in your database:
```
.tables
```
List how the table looks:
```
.schema tablename
```
Print the entire table:
```
SELECT * FROM tablename;
```
List all of the available SQLite prompt commands:
```
.help
```

Best Solution

Related Solutions

Windows – How to you find and replace text in a file using the Windows command-line environment

Sql – How to list the tables in a SQLite database file that was opened with ATTACH

Related Question