How to import a tsv file with SQLite3

csv sqlite tsv import-from-csv
bsuire picture bsuire · Sep 26, 2014 · Viewed 27.6k times · Source

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

adius picture adius · Feb 17, 2016

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

Related questions

Importing a CSV file into a sqlite3 database table using Python

I have a CSV file and I want to bulk-import this file into my sqlite3 database using Python. the command is ".import .....". but it seems that it cannot work like this. Can anyone give me an example of how to …

Read More
SQLite: How do I save the result of a query as a CSV file?

I'm new to SQLite. Is there a way I can export the results of a query into a CSV file?

Read More
Export from sqlite to csv using shell script

I'm making a shell script to export a sqlite query to a csv file, just like this: #!/bin/bash ./bin/sqlite3 ./sys/xserve_sqlite.db ".headers on" ./bin/sqlite3 ./sys/xserve_sqlite.db ".mode csv" ./bin/sqlite3 ./sys/xserve_sqlite.…

Read More