SQLite3导入CSV&排除/跳过标题 [英] SQLite3 Import CSV & exclude/skip header

问题描述

我正在尝试将数据文件(大约有十几个)放入SQLite的表中.每个文件都有一个标头，来年我会收到几次，所以我想:

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:

1. 接收到文件时，请避免编辑每个文件以删除标题；
2. 避免使用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?

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

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.

推荐答案

patid是列名.
"patid"是带引号的列名.
'patid'是字符串.

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

条件WHERE patid = "patid"将patid列中的值与其自身进行比较.

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

(为了与MySQL兼容，SQLite允许使用双引号引起来的字符串，但仅在字符串不能与表/列名混淆的情况下使用.)

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

这篇关于SQLite3导入CSV&排除/跳过标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！