Cleaning my library

[mike_bike_kite]

Is there anything that can scan through my library and work out

• where the author and title are swapped over
• where there might be comments after the book title in brackets
• where the title and author appear in the same field
• where I have a million different copies of the same book
• etc

Cheers

Mike

[unboggling]

• where the author and title are swapped over <- human eyeball, edit metadata, swap.
• where there might be comments after the book title in brackets <- bulk search/replace
• where the title and author appear in the same field <- human eyeball
• where I have a million different copies of the same book <- find duplicates plugin

[Rbneader]

The Find Duplicates plugin is excellent and I highly recommend it.

For the swapped title and author, reading through the library is probably the best bet unfortunately. I wonder if there is some way to sort on title length (probably an indicator of title and author in the same field)?

I'm going through and cleaning up my libraries right now too so I feel your pain.

[Adoby]

You are in luck!

There is a thing called "user" that is very helpful in these circumstances. It has amazing pattern recognition and ability to distinguish whether a string of characters is the name of an author, a book title or something else. And the "user" can even fix problems! Usually you find the "user" mounted on the chair in front of your computer.

There are some plugin that can be helpful and let the "user" work slightly more efficient.

Quality check.
Find duplicates.
Extract ISBN.

Extract ISBN is interesting. I haven't tried it, but I assume that you can let it find ISBN and after that easily download metadata for that exact book, no guessing involved.

[mbovenka]

Originally Posted by Adoby

Extract ISBN is interesting. I haven't tried it, but I assume that you can let it find ISBN and after that easily download metadata for that exact book, no guessing involved.

Yep. It digs through the ebook(s) in question for numbers that match an ISBN format and updates your metadata with them. In my experience it finds ISBNs in about 50% of the ISBN-less ebooks I've used it on.

Very useful (because, as you say, it makes 'Download Metadata' lots more accurate if you have an ISBN).

[gabby98]

Originally Posted by Rbneader

The Find Duplicates plugin is excellent and I highly recommend it.

For the swapped title and author, reading through the library is probably the best bet unfortunately. I wonder if there is some way to sort on title length (probably an indicator of title and author in the same field)?

I'm going through and cleaning up my libraries right now too so I feel your pain.

You can temporarily create a custom column that contains the length of the title (or author) and maybe use that to get you started....I used this to determine which of my entries may or may not need updating with the original description.

When cleaning up my library I also added a custom check column "checked", so that when I know something was all set I set it to yes. That way I would not waste time rechecking that column when I came back to resume working...

I love the find duplicates plugin!

[mike_bike_kite]

With a 100 books that might be possible but unfortunately I have a few more than that

I'd of thought it would be straightforward (perhaps not simple but at least straightforward) to write something that would see if the title field appears as a valid author field. I guess if a book has a rating then it's a valid author. You'd also have to be careful of combinations like "Richard Dawkins", "Dawkins, Richard", "R Dawkins" etc.

I guess there are lot's of common patterns that can be removed ie (YYYY) etc.

It seems like this is something that could be automated. Obviously the program wouldn't have access to a complete book database but it might be enough just to look at other books the user has.

Some books also have the series name shoved in there at random positions which might be hard to spot by program.

Is there an accessible SQL database that holds the users library? I'd be happy to write the SQL to cover these sorts of issues and then perhaps someone more knowledgeable with Calibre could add it into a small option.

Mike

[Adoby]

Metadata.db is a sqlite 3 database.

http://www.sqlite.org/

A "cleanup" plugin would be most welcome!

[mike_bike_kite]

Well that wasn't too hard. I've installed SQLite, I found the Calibre database and I'm now trying out some queries. It might take me a week or so to learn SQLite and write the queries etc but it seems doable.

If I change the database, say perhaps swap a book title and an author name then will this show up cleanly in Calibre or do I need to do more? Can I just copy the metadata.db file to keep a backup of everything?

Mike

[mike_bike_kite]

Actually here's my first success

select   b.title, a.name
from    books b, books_authors_link al, authors a
where  al.book = b.id
             and a.id = al.author
             and b.id not in ( select book from books_ratings_link )
            and b.title in (
                      SELECT  distinct name
                     FROM     books_ratings_link rl, books_authors_link  al, authors a
                     where      al.book = rl.book
                                      and a.id = al.author
                    )

It shows all the entries where the author and title are definitely swapped over ie the book title appears as an author in an entry where that entry has a rating etc. This obviously doesn't do anything clever where the titles and author names are a little messed up but it's a good first step. This found nearly 500 matches in my little database!

Mike

[Adoby]

Yes, just copy metadata.db to make a backup.

The books are stored in folders with paths that are constructed from the author and book title, so it is NOT enough to just swap values. When you update authors or title for a book in calibre, the folder for that book is updated as well. That is why a plugin might be better, it would allow moving the books as well.

I think it would be better to update some custom column with suggested action. Perhaps several custom columns with suggested new values for title, author and series. Then that can be reviewed and updated in calibre.

It could also be possible to make a script that creates a list of actions like swap author/title, and then use the command line tool calibredb to execute those changes from the same script that finds the actions to perform.

http://manual.calibre-ebook.com/cli/calibredb.html

[itimpi]

Originally Posted by mike_bike_kite

Well that wasn't too hard. I've installed SQLite, I found the Calibre database and I'm now trying out some queries. It might take me a week or so to learn SQLite and write the queries etc but it seems doable.

If I change the database, say perhaps swap a book title and an author name then will this show up cleanly in Calibre or do I need to do more? Can I just copy the metadata.db file to keep a backup of everything?

Mike

I think you can only use this to identify the titles that need changing, but still need to do the changes through the Calibre GUI. The folder/filenames used to store the eBooks are constructed from the author/title fields so changing them only in the database is likely to mean that Calibre loses track of the actual underlying eBook files.

[BetterRed]

@mike_bike_kite - I've done something similar as yourself - interrogated the database to identify potential 'problems', I used the output to create a .csv containing author and title

Then I pushed the csv into the Import List plugin, amongst other things the plugin can creating a Reading List (another plug in). So I ended up with Reading Lists like SwapAuthorTitle, RubbishInTitle etc.

Then I did the changes using standard Calibre facilities - many of which can be done in bulk - eg swap author & title. This addresses the issues Adoby & itimpi have raised - of ensuring the database & folders remain in synch

I find this technique quite useful - I do something similar with the results from Windows and Spotlight content searches

BR

[cybmole]

comments after the book title in brackets
should be easy to detect with a bit of regex, from within calibre ? Find title strings which contain (

[mike_bike_kite]

At the moment I'm just writing a SQL script that looks for common issues and outputs the book's id, and the new title and author. It's progressing quite well as roughly half the books that couldn't be found are now found (we're talking 1000's).

Regexp : I can't quite see how to use regexp with sqlite - it seems I have to write it myself which seems a bit tough. This is a shame as I could write some quite fancy checks that might clear another few 100 items.

When I'm finished I'll just pass on the script and hopefully someone else will be able to put it into an add-on etc.