You can import data from a CSV file into an SQLite database.

To import data into SQLite, use the .import command. This command accepts a file name, and a table name.

The file name is the file from which the data is read, the table name is the table that the data will be imported into. If the table doesn't exist, it will be created automatically, based on the data in the CSV file.

CSV to New Table

This example imports the contents of a CSV file to a table that doesn't currently exist. Therefore, a new table will be created based on the data in the CSV file.

We'll use the same CSV file that we exported previously.

.mode csv

.import /Users/quackit/sqlite/dumps/catalog.csv Catalog

Be sure to use .mode csv before the .import as this prevents the command-line utility from trying to interpret the input file text as some other format.

After we run that, we can run a .tables command to review our tables:

sqlite> .tablesAlbums   Albums2  Artists  Catalog

The new table (Catalog) has been created. Let's do a SELECT on it to check the contents:

sqlite> .mode column

sqlite> SELECT * FROM Catalog;

AlbumId   AlbumName             ArtistName  

--------  --------------------  ------------

1         Killers               Iron Maiden 

2         Powerslave            Iron Maiden 

12        Somewhere in Time     Iron Maiden 

3         Surfing with the Ali  Joe Satriani

10        Flying in a Blue Dre  Joe Satriani

11        Black Swans and Worm  Joe Satriani

6         Out of the Loop       Mr Percival 

7         Suck on This          Primus      

8         Pork Soda             Primus      

9         Sailing the Seas of   Primus

You might also want to change the mode back to column (or whichever other mode you were using) as I did here.

And let's also check the schema:

sqlite> .schema CatalogCREATE TABLE Catalog("AlbumId" TEXT,"AlbumName" TEXT,"ArtistName" TEXT);

Existing Table

You can also import the CSV file into an existing table. Just create the table with the appropriate definitions, then run the import.

However, be sure to remove any headers from the CSV file first. When you import to an existing table, every line in the CSV file is read in as a row in the table. So if you include the header row, it will become the first line of data in your table.

We will now create a new table called Genres and populate it from a CSV file.

Here are the contents of the CSV file:

1,Rock2,Country3,Pop4,Comedy5,Jazz6,Blues7,Techno

Create the new table:

CREATE TABLE Genres(

GenreId    INTEGER PRIMARY KEY, 

Genre      TEXT NOT NULL

);

Check that it's there:

sqlite> .tablesAlbums   Albums2  Artists  Catalog  Genres

Now import the CSV file:

.mode csv

.import /Users/quackit/sqlite/dumps/genres.csv Genres

Now verify that the data went in (and change back to column mode):

sqlite> .mode columnsqlite> SELECT * FROM Genres;GenreId   Genre--------  --------------------1         Rock2         Country3         Pop4         Comedy5         Jazz6         Blues7         Techno