SQLite CSV import examples

SQLite CSV FAQ: How do I import a CSV file into a SQLite database table?

Solution

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.

Example: Importing CSV file data into a SQLite database table

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:

3,John,Doe,5.0
4,Jane,Smith,5.0

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 separator command.
• 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:

21,John,Doe,5.0
22,Jane,Smith,5.0

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 INTO command 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.