SQLite CSVFAQ: 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 filedata into a SQLite database is fairly 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 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
);

with 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 this 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.
• There are no blank spaces in the CSV file. When I ran these commands when the CSV file had blank spaces, the blanks ended up in my columns, which I did not want.

Importing CSV data into a SQLite table with a primary key

I don't know if this is the correct approach 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 with a SQLite import command to skip that column, 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.

You can do this, but at the moment, I don't know why that is any better than including the numeric values in the CSV file itself.