How can I 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

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.

Answer 2

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>

Answer 3

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

• Get Sqlite from the website. If you cant do this, give up.
• at a command prompt run sqlite3 *It will be created and 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 ( , );

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

.mode csv .import

That will do it.

Answer 4

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.