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

user_866800 0
Viewed 23056

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

import csv batch-file sqlite windows
6 Answers

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.

user_92337810

First you need correct real csv file with comma separator and header line. I used the dump tool from phpstorm for this.

put all files into a folder.

open cmd.exe

create import statements where %~na is the filename without extension as tablename

for /R %a in (*.csv) do @echo .import %~na.csv %~na >> import.txt

start sqlite with a dummy db 

sqlite test.sqlite

set import mode to csv

.mode csv

run commands from file

.read import.txt

list all tables

.tables

quit sqlite

.q
user_22198660
Related