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?

Answer 1

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 

Answer 2

You should create the table, set a separator and import the data sqlite wiki.

Example for TSV:

data.tsv (tab as a separator):

Bob 30  1000 Wendy   20  900 

1) Create a table and set TAB as a separator:

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

2) Import data:

sqlite> .import data.tsv people 

And the result is:

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