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.

Solution 1:

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>

Solution 2:

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.

Solution 3:

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>

Solution 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.

Solution 5:

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").