Export Category Editor data

[DedTV]

Is there any way to export the data displayed in the Category Editor (particularly tag and series)?

I've been finding things like "brother" and "sister" are each used once as tags but there's 40 books with "siblings". With almost 4k tags in my library, I've been trying to pare things down to more manageable sizes. But things like the example I used don't stick out when they're in one looong list of tags like they are in the Category Editor inside Calibre.

Currently the method I'm using is that I exported the tags using "calibredb catalog tags.csv --fields tags", deduped and fiddled with the list in Excel until I got a master list of tags, then imported them into Access and have been separating them out into themed tables and noting ones that can be merged or deleted. But I keep having to go back to Calibre to check the counts on tags to find out which tag gets kept and which gets pruned and being tethered to my library (which resides on my home NAS) means I can't easily use all the free time I have while away from home (those 3 hour layovers at LAX suck).

So, before I spend too much more useful free time flipping back and forth between access and calibre, I figured I'd see if it's possible to somehow get the data calibre shows outside of Calibre because if I didn't, I'd end up finding out there's some super simple and obvious way to do it that I overlooked after I've spent 100 hours doing it the hard way

[Adoby]

Check out what DoctorOhh did.

https://www.mobileread.com/forums/sho...82#post1522482

I suspect that would be the quickest and easiest way for you to fix your tags.

[DedTV]

Originally Posted by Adoby

Check out what DoctorOhh did.

https://www.mobileread.com/forums/sho...82#post1522482

I suspect that would be the quickest and easiest way for you to fix your tags.

I've been doing that for long while. I even went a bit further than he did in fact and made a json file that maps every genre tag Goodreads has (and I posted the root of that file here). Without that the job would be MUCH bigger than it is.

But many of the books I have do not have tags, or even listings on Goodreads (my library has around 70k books, all legal). So I've pulled tags for those books from numerous other sources. The goodreads plugin can't clean those tags up and there isn't a plugin with the same functionality that works with existing tags or tags from other metadata sources and plugin creation is beyond my skill so a mostly manual solution is the only choice ATM.

[BetterRed]

@DedTV - Suggestion

Have you considered using a copy of the calibre database «metadata.db» as a resource to assist you in getting the job done.

The three tables I thought might be useful are books, tags and books_tags_link.

You can use the SQLite Database Browser to peruse the schema and contents, and execute SQL Commands

You may even be able to use a SQLite ODBC driver to hook the database into Access.

BR

[DedTV]

Originally Posted by BetterRed

@DedTV - Suggestion

Have you considered using a copy of the calibre database «metadata.db» as a resource to assist you in getting the job done.

I used SQLite Admin to dump several tables to clean up titles and authors. I should have also used it to get the list of existing tags as it would have saved me a few minutes of removing the dupes.

If I knew SQL (or databases) better I could probably do a lot more to help myself but the limit of my "expertise" with SQL beyond simple SELECT queries is Googling to find a query that does what I need to do.

I'm even worse with Access and am only using it because I found a step by step guide to sorting a flatfile database into tables which works better than trying to do it with Excel as I was trying to do first.

Edit: Decided to play around with it and I've gotten close. The best I could do is get is (what I think is) the counts via a "select tag, count(*) from books_tags_link group by tag;" query but I haven't figured out how to link that up with a query to link it with the actual tag name.

Although, thinking about it, I could simply just export the tag table and the result of the query and match them up in Excel.
Yup. That worked!

[Adoby]

I suspect that you could use calibre to examine the database. Only have metadata.db in the library, without any of the books. Just make sure that you don't use the column formats. And don't open any books.

I don't think that calibre tries to access the actual books, unless it tries to check what formats are available or if you try to open a book.

[BetterRed]

Originally Posted by DedTV

Edit: Decided to play around with it and I've gotten close. The best I could do is get is (what I think is) the counts via a "select tag, count(*) from books_tags_link group by tag;" query but I haven't figured out how to link that up with a query to link it with the actual tag name.

Although, thinking about it, I could simply just export the tag table and the result of the query and match them up in Excel.
Yup. That worked!

Great

Something like INNER JOIN tags ON books_tags_link.tag=tags.id might have done the trick too, not on calibre system so i can't test.

Glad I could help - sounds like you're in the home straight.

BR