SQLite3 Import CSV & exclude/skip header

I'm trying to get my data files (of which there are a dozen or so) into tables within SQLite. Each file has a header and I'll be receiving them a few times over the coming year so I'd like to:

我正在尝试将我的数据文件（其中有十几个）放入SQLite中的表中。每个文件都有一个标题，我将在接下来的几年里收到它们几次，所以我想：

Avoid editing each file to remove the header when I receive them;
我收到它们时，避免编辑每个文件以删除标题;
Avoid falling back on shell scripts or Python to do this.
避免使用shell脚本或Python来执行此操作。

I define my table and import data...

我定义我的表并导入数据......

> .separator "\t"
> .headers on
> CREATE TABLE clinical(
       patid      VARCHAR(20),
       eventdate  CHAR(10),
       sysdate    CHAR(10),
       constype   INT,
       consid     INT,
       medcode    INT,
       staffid    VARCHAR(20),
       textid     INT,
       episode    INT,
       enttype    INT,
       adid           INT);
> .import "Sample_Clinical001.txt" clinical
> SELECT * FROM clinical LIMIT 10;
patid   eventdate   sysdate constype    consid  medcode staffid textid  episode enttype adid
patid   eventdate   sysdate constype    consid  medcode staffid textid  episode enttype adid
471001  30/01/1997  09/03/1997  4   68093   180 0   0   0   20  11484
471001  30/01/1997  09/03/1997  2   68093   60  0   0   0   4   11485

My first thought was to DELETE the offending row, but that didn't work as expected, instead it deleted the whole table...

我的第一个想法是删除违规行，但这没有按预期工作，而是删除了整个表...

> DELETE FROM clinical WHERE patid = "patid";
> SELECT * FROM clinical LIMIT 3;
>

Did I get the syntax for testing equality wrong? I'm not sure; the docs don't seem to distinguish between the two. I thought I'd try again ...

我是否将测试等式的语法错误了？我不确定;文档似乎没有区分这两者。我以为我会再试一次......

> .import "Sample_Clinical001.txt" clinical
> SELECT * FROM clinical LIMIT 3;
patid   eventdate   sysdate constype    consid  medcode staffid textid  episode enttype adid
patid   eventdate   sysdate constype    consid  medcode staffid textid  episode enttype adid
471001  30/01/1997  09/03/1997  4   68093   180 0   0   0   20  11484
471001  30/01/1997  09/03/1997  2   68093   60  0   0   0   4   11485
> DELETE FROM clinical WHERE patid == "patid";
> SELECT * FROM clinical LIMIT 3;
>

Am I even on the correct track here or am I doing something stupid?

我甚至在这里正确的轨道上还是我做了一些愚蠢的事情？

I would have expected there to be an easy option to skip the header row when calling .import as having header rows in text files is a fairly common situation.

我希望在调用.import时有一个简单的选项可以跳过标题行，因为在文本文件中有标题行是一种相当常见的情况。

1 个解决方案

#1

patid is a column name.
"patid" is a quoted column name.
'patid' is a string.

patid是一个列名。 “patid”是引用的列名。 'patid'是一个字符串。

The condition WHERE patid = "patid" compares the value in the patid column with itself.

条件WHERE patid =“patid”将patid列中的值与其自身进行比较。

(SQLite allows strings with double quotes for compatibility with MySQL, but only where a string cannot be confused with a table/column name.)

（SQLite允许带双引号的字符串与MySQL兼容，但仅限于字符串不能与表/列名混淆的情况。）

#1