将CSV导入SQLite并自动递增主键

Question

我正在尝试在sqlite中创建一张表格，从csv文件中提取数据，并在第一列添加一个自增的主键。这是我要插入数据的表格：

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

这是.csv文件中的数据。

, 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

我收到的错误信息如下:

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

如果在每行的第一个逗号之前有“ null”，我会收到相同的错误。当我在每行的第一个逗号之前添加随机数时，就不会出现任何错误。但是，我需要处理的实际数据集可能会更大，因此，我无法简单地手动添加唯一的主键以供每个条目使用。我真的很感激得到一些帮助。

- Jawwad Zakaria

---

如果您正在使用第三方程序，您可能有选项来设置表示CSV文件中NULL值的文本。SQLiteStudio是其中之一。 - Vassilis

@VassilisGr 你所说的代表NULL值的文本是什么意思？ - pouya

@pouya，如果你有一个CSV文件，其中一行是pouya, 26, NULL，你可以设置在你的CSV文件中，NULL值由字符串"NULL"表示。对于pouya, 26,,的情况，你应该将NULL设置为空字符串""。或者对于pouya, 26, 0，在CSV文件中，NULL值为"0"！ - Vassilis

@VassilisGr 那么SQLiteStudio或其他软件如何在csv文件中定义NULL值？ - pouya

@pouya，这是关于字符串到数据库特定格式和数据库特定格式到字符串的问题，其中字符串是空字符串表示法，它可以是"NULL"或空字符串，正如我所提到的。在这样的应用程序中，这应该是用户的首选。就这些。 - Vassilis

@VassilisGr 感谢你的提示，即使 SQLite Studio 已经设定了“;;”，我还是需要明确指定 NULL。 - buddybubble

Answer 1

CSV文件中的空字段只是一个空字符串，在 INTEGER PRIMARY KEY 列中不被认为有效。

导入到一个没有该列的临时表，然后使用以下命令复制数据：

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

- CL.

---

我在使用这个解决方案时遇到了一个错误，我不得不在SELECT列表中添加一个空字段。INSERT INTO Allegiance(CharacterID, Title) SELECT NULL,Title FROM TempTable; - Gimbo

谢谢，Giorgio。这对于列大小不匹配的问题很有效。我在temptable中有33列，在所需的表模式中有34列，其中包括唯一的自动ID列。使用“_SELECT NULL，* FROM TempTable_”让我节省了很多时间，因为我不必写34列。 - LFMekz

Answer 2

你可以在文本文件中添加主键列，然后导入它。 注意：如果你在第一列遇到错误，请确保你的文件没有BOM（文件开头的3个字节）。如果有BOM，它将在第一行失败。

1：插入失败：数据类型不匹配。

>     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

- live-love