SQLite CSV FAQ: How do I import a CSV file into a SQLite database table?
If you’re not using an autoincrement (primary key) field in your database table, importing CSV file data into a SQLite database is straightforward, though you may have to do some work to clean up your data first. Let’s take a look at how this works.
A SQLite CSV import example
As a first example, assume that I have a SQLite database table defined like this:
CREATE TABLE salespeople ( id INTEGER, first_name TEXT NOT NULL, last_name TEXT NOT NULL, commission_rate REAL NOT NULL );
and there is existing data in the table that looks like this:
sqlite> select * from salespeople; 1|Fred|Flinstone|10.0 2|Barney|Rubble|10.0
If I now have a CSV data file named people.txt that looks like this:
I can import the CSV data into my SQLite table with these two commands:
sqlite> .separator ',' sqlite> .import people.txt salespeople
There are a few important things to note about this example:
- The fields in my text file are separated by commas, so I specify the SQLite
- There are no blank spaces in the CSV file. When I first ran these commands the CSV file had blank spaces, and the blanks ended up in my columns, which I did not want, so I manually removed them. By this I mean that there were blank spaces before words like “John” and “Jane.”
Importing CSV data into a SQLite table with a primary key
I don’t know if the following approach is correct or not, but if you have a SQLite database table declared with an autoincrement
PRIMARY KEY field, like this:
CREATE TABLE salespeople ( id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, commission_rate REAL NOT NULL );
there is no way to skip that column with a SQLite
import command, so the CSV file you import should have primary key values that match your existing table data. For instance, in my previous example, if I already had twenty people in my
salespeople database table, and I wanted to import two more, the CSV file I want to import should begin with number
21, like this:
This is definitely a hack, but it seems to work.
Another SQLite CSV import approach
Another approach is to:
- Create a new database table without a primary key
- Import the CSV data into that SQLite table
- Use the
SELECT INTOcommand to import the data from your temporary table into your actual table
- Delete your temporary table
To be clear, you would do this instead of manually adding numeric values to your CSV file, as I did in the previous example.