SQLite3 Import CSV & exclude/skip header

Question

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:

Avoid editing each file to remove the header when I receive them;
Avoid falling back on shell scripts or Python to do this.

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.

You shouldn't have this problem... the first row of your csv should end up with a `INSERT failed: datatype mismatch` automatically since you have `INT` fields. — Varun Mathur, Nov 30 '20 at 12:32

score 15 · Answer 1 · answered Dec 14 '20 at 22:22

15

This worked for me:

.read schema.sql
.mode csv
.import --skip 1 artist_t.csv artist_t

or if you just have one file to import, you can do it like this:

.import --csv --skip 1 artist_t.csv artist_t

https://sqlite.org/cli.html#importing_csv_files

answered Dec 14 '20 at 22:22

Zombo

1
62
391
407

score 14 · Accepted Answer · answered Nov 27 '12 at 15:27

14

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

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

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

answered Nov 27 '12 at 15:27

CL.

173,858
17
217
259

Ah, ha! Thanks I hadn't noticed this subtle nuance. – slackline Nov 27 '12 at 15:42
Just to be sure, the condition that would do the correct deletion should be WHERE patid = " 'patid' ", correct? – W7GVR Apr 28 '17 at 12:11
@gvrocha No, `" '...' "` does not make sense. – CL. Apr 28 '17 at 13:46
Is there still not an import option that can handle files with headers? – martin May 02 '18 at 02:24
@martin Only when the table does not yet exist. – CL. May 02 '18 at 04:47
I found it in the documentation [here](http://www.sqlite.org/cli.html#csv_import). It looks like for this particular question, since the data would be coming into a table from multiple csv files, deleting the header is still the only option, but in other situations, the solution could just be to skip the `CREATE TABLE` step. – martin May 02 '18 at 08:43
correct syntax should be like this:WHERE "patid"='patid' – Carsten Jul 01 '19 at 11:27

score 1 · Answer 3 · answered Sep 29 '21 at 06:38

1

A alternative response to @steven-penny

You can also use a bash command during sqlite import

.mode csv
.import '| tail -n +2 artist_t.csv' artist_t

answered Sep 29 '21 at 06:38

Bruno Adelé

1,087
13
16

score -3 · Answer 4 · answered Jan 10 '19 at 07:27

-3

import the csv to a new table and copy the new table's data to original target table, will that work?

answered Jan 10 '19 at 07:27

薛淞之

30
3

It might but I'm no longer working for the same people, let alone the same project any more! – slackline Jan 10 '19 at 09:32

SQLite3 Import CSV & exclude/skip header

4 Answers4

Linked