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

Question

I would like to inquire on whether is there anyway to import a csv file that contains output of my select statements in SQLite3 into a new database? Following are the codes i have done thus far:

sqlite3.exe -csv logsql.sqlite "SELECT local_port AS port, COUNT(local_port) AS hitcount FROM connections  WHERE connection_type = 'accept' GROUP BY local_port ORDER BY hitcount DESC;" > output.csv
sqlite3.exe -csv test.sqlite "CREATE TABLE test (name varchar(255) not null, blah varchar(255) not null);" .import ./output.csv test

as you can see my first code was to dump out the queries made.

the second line of code i'm attempting to make a new database and attemptign to import the csv file into the table "test"

thanks for any help made in advance! :D

Answer 1

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

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

Hope that helps.

Answer 2

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;"