sqlite - 是否可以将 CSV 文件导入现有表而不包含标题？

Question

我正在尝试将 CSV 文件导入到 SQLite 数据库中已存在的空表中。例如:

sqlite> CREATE TABLE data (...);
sqlite> .mode csv
sqlite> .import mydata.csv data

我已经提前创建了表，因为我想指定主键、数据类型和外键约束。此过程按预期工作，但遗憾的是它在表中包含了 CSV 文件中的标题行。

以下是我从 SQLite 文档中了解到的有关 CSV 导入的内容:

There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.

In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

所以基本上，我获得了额外的数据，因为我已经提前创建了表格。是否有一个标志可以改变这种行为？如果没有，最好的解决方法是什么？

Answer 1

sqlite3 命令行 shell 没有这样的标志。

如果您有足够先进的操作系统，则可以使用外部工具来拆分第一行:

sqlite> .import "|tail -n +2 mydata.csv" data