Bootstrap FreeKB - SQLite - Import CSV
SQLite - Import CSV

Updated:   |  SQLite articles

Let's say you have a table named users that contains an ID column set as the primary key.

~]$ sqlite3 example.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.

sqlite> .tables
users

sqlite> .schema users
CREATE TABLE users (
        id INTEGER NOT NULL,
        date DATETIME,
        username VARCHAR(100) NOT NULL,
        password VARCHAR(100) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (id),
        UNIQUE (username)
);

 

And you have the following CSV file.

~]$ cat example.csv
2022-12-20,john.doe,itsasecret
2023-01-01,jane.doe,itsasecret

 

Attempting to import example.csv into the users table returns datatype mismatch.

sqlite> .mode csv
sqlite> .import example.csv users
example.csv:1: expected 4 columns but found 3 - filling the rest with NULL
example.csv:1: INSERT failed: datatype mismatch
example.csv:2: expected 4 columns but found 3 - filling the rest with NULL
example.csv:2: INSERT failed: datatype mismatch

 

Create a temporary table that has the same columns as the users table except the temporary table does not have the ID column. You should be able to import the CSV file without getting "datatype mistmatch"

sqlite> create table temp (date DATETIME,username VARCHAR(100) NOT NULL,password VARCHAR(100) NOT NULL, UNIQUE (username));
sqlite> .mode csv
sqlite> .import example.csv temp
sqlite> select * from temp;
date,username,password
2023-01-01,john.doe,itsasecret

 

The temp table should contain the records from the CSV column without the ID column.

sqlite> .headers ON
sqlite> .mode columns
sqlite> select * from temp;
date        username  password
----------  --------  ----------
2022-12-20  john.doe  itsasecret
2023-01-01  jane.doe  itsasecret

 

And you should now be able to copy the records from the temp table into the users table.

sqlite> insert into users (date, username, password) select * from temp;
sqlite> select * from users;
id  date        username  password
--  ----------  --------  ----------
1   2022-12-20  john.doe  itsasecret
2   2023-01-01  jane.doe  itsasecret

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter 8c3725 in the box below so that we can be sure you are a human.