使用自动增量主键将csv导入sqlite [英] Import csv into sqlite with autoincrementing primary key

问题描述

我试图在sqlite中创建一个表，它从csv文件获取数据，并向第一列添加自动增量主键。
下面是我要插入数据的表：

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));

这是.csv文件中的数据

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

这是我收到的错误： / p>

This is the error I recieve:

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

我收到相同的错误，如果我有null每行中的第一个逗号。
当我在每行第一个逗号之前添加随机数时，我没有得到任何错误。但是，我需要使用的实际数据集可能更大，因此，我不能简单地手动添加每个条目的唯一主键。我真的很感谢您对此有一些帮助

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

推荐答案

CSV文件中的空字段只是一个空字符串，不是对于 INTEGER PRIMARY KEY 列有效。

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;

这篇关于使用自动增量主键将csv导入sqlite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！