Import CSV file into Sqlite3 Database in command-line or via Batch File

I'd recommend doing your importation from a flat file, which will create your schema followed with the actual importation:

Like so:

sqlite3.exe test.sqlite < import.sql

Where the content of import.sql is:

CREATE TABLE test (name varchar(255) not null, blah varchar(255) not null);.separator ,.import output.csv test

One other approach which you might not have considered is the ATTACH command. You can attach a new database, create the table in it, and import to its table, so you don't have the extra step of exporting to CSV then reparsing. It can be from a CREATE TABLE ... AS SELECT ... query or just an INSERT.

So basically, you'd run (from your PHP Page):

"ATTACH 'c:\directory\to\database\test.db' as TESTDB;""CREATE TABLE TESTDB.test AS SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections  WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;"

Or:

"ATTACH 'c:\directory\to\database\test.db' as TESTDB;""CREATE TABLE TESTDB.test (name varchar(255) not null, blah varchar(255) not null);""IMPORT INTO TESTDB.test SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections  WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;"

A single-file command to import a file via bash that worked for me:

sqlite3 inventory.sqlite.db << EOFdelete from audit;.separator "\t".import audit-sorted-uniq.tsv auditEOF

Hope that helps.

For large CSV files it may be more efficient to use the sqlite3 shell's .import command, rather than parse the file in Python and insert rows with sqlite3 module. It can be done via os.system (on Linux, Unix or Mac OS X, or Cygwin on Windows):

cmd = '(echo .separator ,; echo .import ' + csv_file + ' ' + table + ')'cmd += '| sqlite3 ' + db_nameos.system(cmd)