How to import load a .sql or .csv file into SQLite

Question

I need to dump a .sql or .csv file into SQLite (I'm using SQLite3 API). I've only found documentation for importing/loading tables, not entire databases. Right now, when I type:

sqlite3prompt> .import FILENAME TABLE 

I get a syntax error, since it's expecting a table and not an entire DB.

Answer 1

Remember that the default delimiter for SQLite is the pipe "|"

sqlite> .separator ";"

sqlite> .import path/filename.txt tablename 

http://sqlite.awardspace.info/syntax/sqlitepg01.htm#sqlite010

Answer 2

Import your csv or sql to sqlite with phpLiteAdmin, it is excellent.

Answer 3

This is how you can insert into an identity column:

CREATE TABLE my_table (id INTEGER PRIMARY KEY AUTOINCREMENT, name COLLATE NOCASE);
CREATE TABLE temp_table (name COLLATE NOCASE);

.import predefined/myfile.txt temp_table 
insert into my_table (name) select name from temp_table;

myfile.txt is a file in C:\code\db\predefined\

data.db is in C:\code\db\

myfile.txt contains strings separated by newline character.

If you want to add more columns, it's easier to separate them using the pipe character, which is the default.

Answer 4

SQLite is extremely flexible as it also allows the SQLite specific dot commands in the SQL syntax, (although they are interpreted by CLI.) This means that you can do things like this.

Create a sms table like this:

# sqlite3 mycool.db '.schema sms'
CREATE TABLE sms (_id integer primary key autoincrement, Address VARCHAR, Display VARCHAR, Class VARCHAR, ServiceCtr VARCHAR, Message VARCHAR, Timestamp TIMESTAMP NOT NULL DEFAULT current_timestamp);

Then two files:

# echo "1,ADREZZ,DizzPlay,CLAZZ,SMSC,DaTestMessage,2015-01-24 21:00:00">test.csv

# cat test.sql
.mode csv
.header on
.import test.csv sms

To test the import of the CSV file using the SQL file, run:

# sqlite3 -csv -header mycool.db '.read test.sql'

In conclusion, this means that you can use the .import statement in SQLite SQL, just as you can do in any other RDB, like MySQL with LOAD DATA INFILE etc. However, this is not recommended.

Answer 5

Check out termsql. https://gitorious.org/termsql https://gitorious.org/termsql/pages/Home

It converts text to SQL on the command line. (CSV is just text)

Example:

cat textfile | termsql -o sqlite.db

By default the delimiter is whitespace, so to make it work with CSV that is using commata, you'd do it like this:

cat textfile | termsql -d ',' -o sqlite.db

alternatively you can do this:

termsql -i textfile -d ',' -o sqlite.db

By default it will generate column names "COL0", "COL1", if you want it to use the first row for the columns names you do this:

termsql -i textfile -d ',' -1 -o sqlite.db

If you want to set custom column names you do this:

termsql -i textfile -d ',' -c 'id,name,age,color' -o sqlite.db