Using sqlite to convert a csv file to a database, and viceversa!

This short post shows the commands needed to:

1. Convert from a csv file to a database and

2. Convert from a database to a csv file.

1. Csv file to sqlite db.

1.1 suppose your data is in a text file names.txt in the format:

1,name1,lastName1

2,name,lastName2

Then the first thing is to create a table with such structure (http://www.sqlite.org/sqlite.html)

sqlite3 mydb   –note: this creates the sqlite db ‘mydb’

sqlite>create table mytable(id integer, name text, lastname text);

sqlite>.tables

mytable

sqlite> .mode list
sqlite> .separator ,
sqlite> .import names.txt mytable   –note: DON’T use ; after mytable
sqlite> select * from mytable;

1,Omar,Flores
2,Mike,Flores

Now for the second part:

2. table to csv file.

sqlite> .mode list   –if you entered these before, no need to enter again.
sqlite> .separator ,

sqlite> .output namesexported.txt
sqlite> select * from mytable;
sqlite> .exit

edit the file namesexported.txt. It should show:

1,Omar,Flores
2,Mike,Flores

That is it. Simple and fast.

Omar