MySQL Forums
Forum List  »  SQLite

output to SQLite 3 to csv
Posted by: alexey petrovsky
Date: May 28, 2006 01:31AM

1) You need to examine your text fields and narrow your search for a possible separator.

2) Connect to sqlite database:

# sqlite3.exe {yourdatabase.db}

Say, you decided to use "\t" (tab delimeter) as your separator. Be sure that your text data have no tabs inside.

3) output the schema of your tables:


Copy & paste to safe location, you may find it useful later. You can use .output command as well:

sqlite>.output mytables_schema.txt

4) Assign your separator, test your settings:

sqlite> .separator "\t"

- assign your separator

sqlite> SELECT * FROM {your_database} LIMIT 5;

- that gives you first 5 entries (may be lengthy, depends on your datasize, so limit to 1);

sqlite> .header ON

- IF you need a header output too.

5) You get yourself familir with your possible output and decided to go further:

sqlite> .mode csv
sqlite> .separator "\t"


sqlite> .mode list


sqlite> .mode tabs

- play with settings once more, with above SELECT.


sqlite> .output yourtable.csv

Until the sqlite> prompt appears, wait. You can return back to onscreen output by .output stdout command. .help for your help.

That's all.

sqlite> .quit

or Control-C.

Here quick solution provided:

sqlite> .mode tabs
sqlite> .output yourtable.csv

Bingo. Don't forget about .schema, it maybe useful since output to .csv erase the schema (beside the fact Sqlite is typeless engine).

If you have any pitfalls, please provide more information about your database schema and entries.


Options: ReplyQuote

Written By
February 22, 2006 11:49PM
output to SQLite 3 to csv
May 28, 2006 01:31AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.