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}
sqlite>

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:

sqlite>.schema

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"

OR:

sqlite> .mode list

OR

sqlite> .mode tabs

- play with settings once more, with above SELECT.

FINALLY:

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.

Goodluck.

Options: ReplyQuote


Subject
Views
Written By
Posted
18975
February 22, 2006 11:49PM
output to SQLite 3 to csv
22610
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.