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

Advertisements

5 Responses to “Using sqlite to convert a csv file to a database, and viceversa!”

  1. Rick Says:

    This is a useful bit of info! Thanks for posting it – keep up the good work!

  2. MB Says:

    Thanks, simple and useful!

  3. murod Says:

    Hi, how about Windows?
    I have AddressBookImages.sqlitedb backed up.
    My device is not jailbroken, so how can I get my Contacts back?
    I have 3g, new 4.2.1 NOT JAILBROKEN
    before that, I had 3g, 3.0 JAILBROKED, where I backed up addbook from
    any help would be great

    • florespino Says:

      sqlite runs under Windows. The example was intended to convert between csv format and sqlite db. I am not familiar with the term jailbroken but if you have the data in format sqlite you can convert it to csv and look into your contacts, modify them and revert them to sqlite.
      Hope it helps.

  4. Chris Says:

    Hello. I tried your commands and I only get the first line from my CSV file to import. Any suggestions?
    Chris

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: