SQLite - Import Data from a CSV File

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.

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> .tables
Albums   Albums2  Artists  Catalog

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

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 Catalog
CREATE 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,Rock
2,Country
3,Pop
4,Comedy
5,Jazz
6,Blues
7,Techno

Create the new table:

Check that it's there:

sqlite> .tables
Albums   Albums2  Artists  Catalog  Genres

Now import the CSV file:

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

sqlite> .mode column
sqlite> SELECT * FROM Genres;
GenreId   Genre               
--------  --------------------
1         Rock                
2         Country             
3         Pop                 
4         Comedy              
5         Jazz                
6         Blues               
7         Techno