How to import a tsv file with SQLite3

Question

I have a tsv (tab separated file) that I would like to import with sqlite3. Does someone know a clear way to do it?

I have installed sqlite3, but not created any database or tables yet.

I've tried the command

.import /path/filename.tsv my_new_table

but it gives me the error: no such table: my_new_table.

However, from what I'd read it should create the table automatically if it does't exist. Does it mean I need to create and use a database first, or is there another trick to importing a .tsv file into sqlite?

score 106 · Answer 1 · answered Feb 17 '16 at 10:29

106

There is actually a dedicated mode for importing tab separated files:

sqlite> .mode tabs
sqlite> .import data.tsv people

Also if you include a header row in your tsv file, you can let sqlite automatically create the table. Just use an unused table-name during import and change the tsv file to:

name    param1  param2
Bob 30  1000
Wendy   20  900

answered Feb 17 '16 at 10:29

adius

13,685
7
45
46

5

don't put a semi-colon at the end of the import statement, like I did :) – Neil McGuigan Aug 22 '16 at 22:02
`Error: no such table: people` – user5359531 Feb 01 '21 at 19:59
1

`.help .mode` says its job is to “set output mode,” but `.help .import` clarifies in a note: “If neither `--csv` or `--ascii` are used, the input mode is derived from the `.mode` output mode” – andrewdotn Jul 02 '21 at 21:48

score 39 · Accepted Answer · edited Feb 13 '23 at 11:31

39

You should create the table, set a separator and import the data (sqlite docs).

Example for TSV:

data.tsv (tab as a separator):

Bob 30  1000
Wendy   20  900

Create a table and set TAB as a separator:

sqlite> create table people (name text, param1 int, param2 int);
sqlite> .separator "\t"

Import data:
```
sqlite> .import data.tsv people
```

And the result is:

sqlite> select * from people;
Bob 30  1000
Wendy   20  900

edited Feb 13 '23 at 11:31

xbello

7,223
3
28
41

answered Sep 26 '14 at 19:57

Grigorii Chudnov

3,046
1
25
23

6

Note that using `.separator "\t"` means that sqlite will still use csv-style rules for interpreting quotation marks, which probably isn't what you want. A better option is to use the `tabs` mode described in adius's answer. – Miles Jun 19 '17 at 08:28
Seems like there is some logic on the names. After .separator "\t", I also had to rename from .csv to .tsv – Punnerud Aug 07 '18 at 16:38
No need to create a table. The import will do it for you. If you are happy for every column's type to be chosen for you. I did this and everything was 'text'. This might be OK for situations where you are manually exploring data to throw away later. – Martin Capodici Aug 01 '19 at 23:15

How to import a tsv file with SQLite3

2 Answers2

Linked