Summary text replacement?

[TreverT]

I just bought your program - It's terrific! For the last couple of years I've been clinging on to the Windows version of Book Collector in a virtual machine because their Mac version was not so hot, but I finally made the jump when I saw all that Bookpedia could do. I managed to export my old Book Collector database (2322 books) into a CSV file, including some custom user text fields I'd created, and then imported them into Bookpedia OK. BUT, there is one nagging issue - A lot of the Summary field descriptions include HTML code tags as part of the text - A typical example will look like this:

Who would have thought there would be a <i>murder</i> at the old house?<br><br>

I've looked at some of the other forum posts about using SQLlite to find and replace database text, but I'm not sure that applies here (I know absolutely zip about databases). Is there an easy way to have it go through and, for example, yank out all occurrences of <br> and replace them with nothing? (Or even better, an actual line skip)

Also, some of these are audiobooks with a custom field created for file size. It imported all my old values correctly, but for some bizarre reason it added a " after every value, so 432 meg became 432 meg"
I'd love to be able to swap out all those meg" values for meg.

Thanks in advance for any help you can provide!

[Conor]

Hi Trever,

Glad you managed to make the switch. Seem like most of the trouble is with the original export file containing all this code. You could clean up the CSV export and then reimport it. To do it directly in Bookpedia will require the SQLite access, not as daunting as it looks. Make a backup of you database file in your home folder ~/Library/Application Support/Bookpedia/Database.pediadata. Select it and use the duplicate command.

Now look for the program called "Terminal" in spotlight and run it. In that new window type in the following commands (you can copy/paste all the lines at the same time). You might need to copy to a text editor first and update the last lines "custom1" field to match the field where you file size is stored, e.i. custom5 or custom3. You can also add more of the sumamry replace lines to add more HTML to strip out instead of just: <br>, <i> and </i>

Code: Select all

sqlite3 ~/Library/Application\ Support/Bookpedia/Database.pediadata
update zEntry set zSummary = replace(zSummary, '<br>', x'0D');
update zEntry set zSummary = replace(zSummary, '<i>', '');
update zEntry set zSummary = replace(zSummary, '</i>', '');
update zEntry set zCustom1 = replace(zCustom1, '"', '');
.exit

If you have problems don't hesitate and sending us your database.pediadata file via email and we can help you with the clean up.

[TreverT]

I got it all sorted, thanks very much for your help! I've skimmed the library and occasionally still find new bits of HTML littered here and there, but have cleaned most of it up now via your SQL instructions. Bookpedia is a terrific program. I also got the database file relocated to our Dropbox folder so my wife and I can both draw on (and update) one central database that auto-mirrors to both our computers and has a backup in cloud storage.
Thanks again!