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