[database] How to import load a .sql or .csv file into SQLite?

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.

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.

The sqlite3 .import command won't work for ordinary csv data because it treats any comma as a delimiter even in a quoted string.

This includes trying to re-import a csv file that was created by the shell:

Create table T (F1 integer, F2 varchar);
Insert into T values (1, 'Hey!');
Insert into T values (2, 'Hey, You!');

.mode csv
.output test.csv
select * from T;

Contents of test.csv:
1,Hey!
2,"Hey, You!"

delete from T;

.import test.csv T
Error: test.csv line 2: expected 2 columns of data but found 3

It seems we must transform the csv into a list of Insert statements, or perhaps a different delimiter will work.

Over at SuperUser I saw a suggestion to use LogParser to deal with csv files, I'm going to look into that.

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

if you are using it in windows, be sure to add the path to the db in "" and also to use double slash \ in the path to make sure windows understands it.

Try doing it from the command like:

cat dump.sql | sqlite3 database.db

This will obviously only work with SQL statements in dump.sql. I'm not sure how to import a CSV.

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

If you are happy to use a (python) script then there is a python script that automates this at: https://github.com/rgrp/csv2sqlite

This will auto-create the table for you as well as do some basic type-guessing and data casting for you (so e.g. it will work out something is a number and set the column type to "real").

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.

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

To go from SCRATCH with SQLite DB to importing the CSV into a table:

• Get SQLite from the website.
• At a command prompt run sqlite3 <your_db_file_name> *It will be created as an empty file.
• Make a new table in your new database. The table must match your CSV fields for import.
• You do this by the SQL command: CREATE TABLE <table_Name> (<field_name1> <Type>, <field_name2> <type>);

Once you have the table created and the columns match your data from the file then you can do the above...

.mode csv <table_name>
.import <filename> <table_name>

To import from an SQL file use the following:

sqlite> .read <filename>

To import from a CSV file you will need to specify the file type and destination table:

sqlite> .mode csv <table>
sqlite> .import <filename> <table>