SQLite CSV import examples

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.

Back to top

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:

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.”
Back to top

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.

Back to top

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.

Back to top

Comments

Permalink

Thanks for the code. I know this is an old thread but I thought I would answer Alvins question.

In any database engine such as MySQL or SQLServer if you don't want the overhead of using code to find the last primary key and so avoid duplicating it, by creating a temporary table with no primary key and then merging it into the main table the primary keys are automatically created by the database engine (assuming that 'autoincrement' is enabled).

Hope this helps.

CJ ...

Permalink

If you use the implicit defined column ROWID (that every table has in SQLite), you can import data without a primary key in the data.

Add new comment

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.