Changing the Delimiter when dumping the database

Any trouble you encounter with the Pedias, here's the place to ask for help.
Post Reply
DriverJC
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Changing the Delimiter when dumping the database

Post by 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
Addicted to Bruji
Addicted to Bruji
Posts: 40
Joined: Mon Feb 12, 2007 10:48 am

Re: Changing the Delimiter when dumping the database

Post by 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
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Re: Changing the Delimiter when dumping the database

Post by 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
Addicted to Bruji
Addicted to Bruji
Posts: 40
Joined: Mon Feb 12, 2007 10:48 am

Re: Changing the Delimiter when dumping the database

Post by 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
Bruji Friend
Bruji Friend
Posts: 16
Joined: Mon Aug 02, 2010 9:00 pm

Re: Changing the Delimiter when dumping the database

Post by 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
Post Reply