Efficient tag management??

[Patrick Derwael]

Hi folks,

I would like to clean up the mess in my tags (4565 different ones)
For that I want to export the tags, along with all the related books, in order to analyse this further in Excel

I know I can export a catalog, but then all the tags are on one single field, comma separated, which is not quite usable

Ideally, I would like something like
tag1 - book1
tag1 - book2
...
tag2 - book1
tag2 - book2
...

Any clue on how to do this?
Or any other brilliant idea about efficient tag management?

Thanks!

[Sirtel]

The tag browser should do exactly what you describe.

[Patrick Derwael]

Sure, I know that, but this is not what I call "tag management"

I want to analyse the existing tags on order to improve their quality and accuracy

By exporting to Excel, I want to be able to easily identify (for example)
- synonymous tags which could be combined together
- series with very different tags (which should normally happen)
- typos in tags
- books without tags (or potentially too few/too many) tags
- etc...

[Sirtel]

Originally Posted by Patrick Derwael

Sure, I know that, but this is not what I call "tag management"

I want to analyse the existing tags on order to improve their quality and accuracy

By exporting to Excel, I want to be able to easily identify (for example)
- synonymous tags which could be combined together
- series with very different tags (which should normally happen)
- typos in tags
- books without tags (or potentially too few/too many) tags
- etc...

I've found the easiest way to manage tags is to delete all tags from new books immediately after import and add my own tags later when cleaning up the metadata. To try to make sense of the mess the default tags bring with them is a waste of time and energy.

[kovidgoyal]

Create a csv catalog and import that to excel

[Patrick Derwael]

Originally Posted by Sirtel

I've found the easiest way to manage tags is to delete all tags from new books immediately after import and add my own tags later when cleaning up the metadata. To try to make sense of the mess the default tags bring with them is a waste of time and energy.

Everyone has his own method; there is nothing like "the one and only" method
I'm through that "waste of time" up to the letter V
Having almost finished, what I'm looking for is a not-too-complicated way to perform cross checks (after I will have finished obviously)

[Patrick Derwael]

Originally Posted by kovidgoyal

Create a csv catalog and import that to excel

Sure, but all the tags are then comma separated in one single cell
In other words, I have a relationship book --> tags
What I need is a relationship tag --> books

[kovidgoyal]

I dont use excel, but I would be amazed if it didnt have an easy way to split up comma separated data into multiple cells

[theducks]

I use the Manage option for each section in the Tag Browser and good old Eyeball Mk I.
The advantage is I can check NOW, what books are associated with that tag.
Especially with Series, Duplicates or Singles (books ONLY have the same Series name), the Manage Series PI helps (I add the Authors initials in paren to a series to break a tie: Magic (AN)

The only tool I wish it had, is a 'compound Tag splitter' (takes the Tag and allows commas to be inserted which makes that into the multiple tags)
OK another tool : Hierarchical flipper. (I don't type well, so any help is good)
Hardboiled Detective I add the dot (Hardboiled.Detective) then flip -> Detective.Hardboiled

[Patrick Derwael]

Originally Posted by kovidgoyal

I dont use excel, but I would be amazed if it didnt have an easy way to split up comma separated data into multiple cells

Excel can indeed easily split a list of tags into several values. That is absolutely not an issue
The complex part (at least with my knowledge of Excel) is then to keep the relationship between each tag and all the related books

This is why, instead of a "can do" situation, I'm looking for a straightforward (i.e. application provided) solution

[Patrick Derwael]

Originally Posted by theducks

I use the Manage option for each section in the Tag Browser and good old Eyeball Mk I.
The advantage is I can check NOW, what books are associated with that tag.
Especially with Series, Duplicates or Singles (books ONLY have the same Series name), the Manage Series PI helps (I add the Authors initials in paren to a series to break a tie: Magic (AN)

I was not aware of the Manage tags option. Thanks for the pointer
Still this does not have the flexibility (and analysis power) of a proper "catalog by tags" export to Excel

[Difflugia]

Originally Posted by Patrick Derwael

Ideally, I would like something like
tag1 - book1
tag1 - book2
...
tag2 - book1
tag2 - book2
...

Any clue on how to do this?

The tag information is stored in the SQLite database file metadata.db. If you're comfortable using the command line and willing to access the database file directly, you can get exactly what you want.

First, here's the query in case you don't need the rest of the explanation:

SELECT a.name, b.author_sort, b.title
FROM tags a, books b, books_tags_link c
WHERE a.id = c.tag AND b.id = c.book
ORDER BY a.name, b.author_sort, b.title;

The command line tool for can be downloaded from the SQLite website here. Under Precompiled Binaries for Windows, you want "sqlite-tools-win32-x86-3330000.zip". The zip file contains three executables, one of which is named "sqlite3.exe".

My suggestion is that you create a separate working directory for this. First, make a copy of the "metadata.db" file from your Calibre directory in the new directory (the tool will let you make irreversible changes to your Calibre database, so work from a copy). Then copy the "sqlite3.exe" file into the same directory. Finally, create a text file named "tags.sql" in the directory and paste the above query into it.

Open a Windows command line and cd to the directory you created. Type "sqlite3 metadata.db" and you should see the following:

SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
sqlite>

The command ".read some_file" will execute SQLite commands from some_file. The command ".output some_other_file" will redirect all output to the file instead of the screen. Type or paste the following commands at the sqlite> prompt to create a "tags_output.csv" file that you can import into Excel.

.output tags_output.csv
.read tags.sql
.quit

Unfortunately, the field separator isn't a comma, but a vertical bar. I guess that means it's not actually a CSV file, but if you're comfortable importing files into Excel, I assume you can find the spot in the dialog that lets you pick a different delimiter.

[Patrick Derwael]

Originally Posted by Difflugia

The tag information is stored in the SQLite database file metadata.db. If you're comfortable using the command line and willing to access the database file directly, you can get exactly what you want.

Hi
Thanks a bunch, this is EXACTLY what I wanted
Knowing now this is SQLite, I was wondering: is the DB structure documented somewhere?
That might be extremely useful for all sorts of data analysis (I'm a big fan of such exercise )

Thanks again