Using Bookpedia, SQLite, Book+ and PowerShell to Organize Your e-Book Collection

This setup is so unlikely, I doubt anyone on the ‘net will be looking for this solution but: if you’re an eBook loving Windows developer who uses a Macbook Pro for your primary workstation, this may appeal to you.

My Macbook Pro runs Windows 7 within Parallels. It’s fast and slick and I <3 it. My eBook reader of choice is the Book+ iPad App. I recently switched from GoodReader to Book+ because of its support for Smart Folders. And I back this all up to SugarSync which I picked for the price, privacy policy and Book+ integration.

My eBook organizer is Bookpedia. If you’re unfamiliar with this (MacOS only) program, it’s basically a really advanced eBook library/organizer. What I love most about it is that it easily populates your eBook’s metadata using various web services – including Amazon! – then stores it all to a SQLite database.

My eBook collection looked nice enough in Bookpedia: Initially, I dragged ebooks onto the dock to import, named them properly and populated their metadata using Amazon. The problem was that my files were all over the place and practically unsyncable with SugarSync and ultimately, Book+. Look how awesome:

I’m only a perfectionist when it comes to some things, and ebook organization is one of them. I want my eBooks’ naming convention to be as obvious as possible: Amazon’s full book title – Publisher – ISBN. I finallly accomplished this using a number of tools and a few hours of coding. Here’s my setup:

  • Windows 7 in Parallels with Z: mapped to my Home directory on my Mac.
  • Amazon developer account
  • eBooks that are scattered all over my home directory but organized nicely in BookPedia using Amazon.com data
  • SQLite Studio for Windows
  • Desired single directory destination: ~/Books in Mac or Z:\Books in Windows

First thing you wanna do is make a backup of your Bookpedia database.

cp ~/Library/Application\ Support/Bookpedia/Database.bookpd ~/Library/Application\ Support/Bookpedia/backup.bookpd

Next, load it up into SQLite. Databases -> Add Database -> Z:\Library\Application Support\Bookpedia\backup.bookpd.

CREATE TABLE export (
    title     TEXT,
    filename    TEXT,
    pubdate TEXT,
    publisher TEXT,
    isbn TEXT,
    asin TEXT
    
);

insert into export
select a.ztitle, b.zurl, strftime('%Y',date(zreleasedate,'unixepoch'))+31 as pubdate,
a.zpublisher, a.zisbn, a.zasin
from zentry a join zlink b on a.z_pk = b.zentry
--where b.zurl not like '%YOUR MAC DESTINATION DIR%'

This script creates a new table because that’s the only way SQLiteStudio exports data into tsv (tab separated values, which I generally prefer to CSVs) format. Once the table is created, I use a right click to export the it to C:\bookpedia.tsv, making sure to check the “column names as first row” option and ensuring the output is ascii encoded.

Now that we’ve got our TSV file, we’ll run it against the PowerShell script below. Copy this code and save it as whatever.ps1

PowerShell Code

# winbasedir = Parallels mapped drive
$winbasedir = "Z:\"
$windestination = "Z:\books\"
$macusername = "chrissylemaire"
$csvfile = "C:\newstructure.csv"
$macdir = "file:///Users/$macusername/"

# import csv file that contains two columns, directory and filename
$csv = Import-Csv "c:\bookpedia.tsv" -Delimiter "`t"
$newcsv = @()

foreach ($item in $csv)
{  
  if ($item.filename) {
    $source = $item.filename.Replace($macdir,$winbasedir)
    $source = $source.Replace("/","\")
    $extension = $source.substring($source.length - 4, 4)
    $title = $item.title

    # Set the file's new name. In my case, I wanted Book Title - Year - Publisher - ISBN
    if ($item.pubdate) { $title = $title + " - " + $item.pubdate}
    if ($item.publisher) { $title = $title + " - " + $item.publisher}  
    if ($item.isbn) { $title = $title + " - " + $item.isbn }

    # No clue why GetInvalidFilenameChars doesn't do this right...
    # Copy-Item will not allow the characters below
    $newname = $title.Replace(":"," -")
    $newname = $newname.Replace("  "," ")
    $newname = $newname.Replace("\","-")
    $newname = $newname.Replace("/","-")
    $newname = $newname.Replace("?","") # ascii encoding changes weird chars to "?"
    $newname = $newname.Replace("[","")
    $newname = $newname.Replace("]","")
    $newname = $newname.Replace("*","-")
      
    $target = $windestination+$newname+$extension

    if ((test-path($source)) -and !(test-path($target)))
    {
      Copy-Item -path $source -destination $target -whatif

      # If the copy succeeded, add it to a CSV file that
      # Bookpedia will use to import the new clean structure
      if (test-path($target))
      {
        $newline = New-Object System.Object
        $mactarget = $target.Replace($windestination,$macdir+"Books/")
        $mactarget = $mactarget.Replace("\","/")
        Add-Member -inputobject $newline -name link -value $mactarget -MemberType NoteProperty
        Add-Member -inputobject $newline -name title -value $item.title.Replace("?","") -MemberType NoteProperty
        Add-Member -inputobject $newline -name isbn -value $item.isbn -MemberType NoteProperty
        Add-Member -inputobject $newline -name asin -value $item.asin -MemberType NoteProperty
        $newcsv += $newline
      }
    }
  }
}

$newcsv | export-csv $csvfile -noType -Force
mv $csvfile $winbasedir -Force

One thing to note: I did write this script so that it can be run multiple times. So each time you’ve got a batch of books that need to be cleaned up, you can just run it again without destroying your library.

So once the script has finished running, the fruits of its labor will look something beautiful like this:

Now that we’ve got the files copied (I don’t recommend moving them, just in case something happens) and the new csv import file has been created, we’ll open it in Bookpedia. First, though, I made another backup of this Bookpedia database then deleted my library. Now it’s time to import the “clean” collection. Bookpedia -> File -> Import Collection.

The columns will automatically map properly because the script and Bookpedia are awesome like that.

Now Bookpedia has enough information to populate the metadata. As I said before, I chose the Amazon option for this.

So now I use SugarSync to sync up that Books directory and then use Book+ to keep that directory in sync on my iPad. What’s super great about Book+, too, are the Smart Folders. Say SugarSync syncs a new PowerShell book, it will automatically appear in my PowerShell Smart Folder. Whaaaat!

Ladies and Gentlemen… the prodigiousness:

Anyone wanna rewrite this in AppleScript for me?

Posted in OS X & iDevices, PowerShell
2 comments on “Using Bookpedia, SQLite, Book+ and PowerShell to Organize Your e-Book Collection
  1. Conor says:

    Thank you for the kind words on Bookpedia. It's been ten years of work to get to this level of sophistication. This is why we love open formats, it allows users to take Bookpedia places that we never would have.

    If you're running your script often you could even wrap it in a plugin and it would become a menu command inside Bookpedia. Everyday there's something new and I haven't had time to update the documentation on the plugins, so it's better to work from the sample code for other menu plugins. http://bruji.com/articles/2013/07/21/a-bag-of-ran…. Although I am not sure you would be able to automate the SQLite Studio steps.

    • Oh, hey! The author of one of my favorite apps, ever. Thank you so much for Bookpedia!

      Writing a plugin is a great option. I wouldn't need to automate the SQLite part if I can rename the files using metadata. I just downloaded the plugin source, but it looks like Xcode, which I don't know. It's on my list, though! Once I learn it, I'll take another look.

Add Comment Register



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">