Import csv into sqlite with autoincrementing primary key

Question

I am trying to create a table in sqlite that takes data from a csv file and adds an autoincrementing primary key to the first column. Here is the table I am trying to insert data into:

DROP TABLE IF EXISTS Allegiance;
CREATE TABLE Allegiance (
  AllegianceID INTEGER PRIMARY KEY AUTOINCREMENT,
  CharacterID INTEGER,
  Title TEXT,
  FOREIGN KEY (CharacterID) REFERENCES Characters(CharacterID));

Here is the data in the .csv file

, 3, King of the North
, 14, King of the Andals and the First Men
, 15, Lord of Dragonstone
, 26, Khaleesi
, 35, Lord Reaper of Pyke

This is the error I recieve:

sqlite> .mode csv
sqlite> import allegiances.csv Allegiance;
Error: datatype mismatch

I receive the same error if I have "null" before the first comma in each line. When I add random numbers before the first comma in each line, I do not get any errors. However, the actual dataset I need to work with may be much larger and therefore, I can't simply manually add in a unique primary key for each entry. I'd really appreciate some help with this

Answer 1

An empty field in a CSV file is just an empty string, which is not valid for an INTEGER PRIMARY KEY column.

Import into a temporary table without that column, then copy the data over with:

INSERT INTO Allegiance(CharacterID, Title) SELECT * FROM TempTable;

Answer 2

You can add a column with the primary keys in your text file, then import it. Note: If you get that error on the first column, make sure your file does not have the BOM. If it has the BOM (3 bytes at the beginning of the file), it will fail on the first row.

1: INSERT failed: datatype mismatch

>     drop table words ;
>     CREATE TABLE words (id INTEGER PRIMARY KEY AUTOINCREMENT, name COLLATE NOCASE, rank INT);
>     .import file.txt words

1|a|0
2|b|0
3|c|0
4|d|0
5|e|0
6|f|0
7|g|0