Changing the Delimiter when dumping the database

[DriverJC]

At present I dump the database with the following script
sqlite3 /Users/joel/Library/Application\ Support/DVDpedia/Database.dvdpd '.dump ZENTRY'

My question: is there a way to modify this script so that it outputs a "|" as the delimiter for the fields?

The problem I'm running into is that if there is a comma in any of the fields the script then mistakes those as field delimiters, gets kind of irritating after two hunted files or so.

Thank you for an Awesome Product
Joel

[noisyscott]

Sure, you can specify a delimiter with the .separator declaration in sqlite. You can also do the following to export as tab separated (which I find to be a more robust output format when doing further text processing):

Code: Select all

	.mode tab
	.output zentry.txt
	.header on
	select * from zentry
	.exit

More reference is here: http://www.sqlite.org/sqlite.html

[DriverJC]

Great. How do I go about putting those into a bash script to run when I run the converting script I use?

I've tried replacng the .dump with each of those and can't get it to work.

[noisyscott]

Sorry, I have no experience with that, I just perform those commands manually inside sqlite3. However, the internet seems to indicate that what you are looking for is possible. It will just take a little work for you to figure it all out.

http://snippets.dzone.com/posts/show/3080
http://stackoverflow.com/questions/5741 ... -in-ubuntu

Good luck!

[DriverJC]

Apperently if you change the delimiter in sqlite3 using the .mode command (which I did) it keeps that setting even after closing the program and rebooting the computer.

All I have to do now is use the command

Code: Select all

sqlite3 /Users/joel/Library/Application\ Support/DVDpedia/Database.dvdpd "select * from zEntry " > test.txt ;

and it outputs the query into test.txt with "|" as the separator.

Thank you for pointing me in the right direction.

Joel