Jump to content

SERVER - Compare library name with file/folder path name


CharleyVarrick
Go to solution Solved by PenkethBoy,

Recommended Posts

CharleyVarrick

My knowledge of Excel just doubled from 0.02% to 0.04%

Amazing, while Topbanana likely knew how to do the Excel texttocolumns-thing with his eyes closed, I sure didn't

 

Romulan Ale for everyone, my treat!

58dfbca19c9bb_1.jpg

Edited by jlr19
  • Like 1
Link to comment
Share on other sites

PenkethBoy

Are you going to teleport it over.... :P

 

yes the translators are a bit variable it was supposed to be "Life Universe and everything" :)

  • Like 1
Link to comment
Share on other sites

Guest topbanana

 

Guys

 

There is a quicker way that will give you a list of what you need

 

The Emby DB is SQLite - so you can download the free "DB Browser for SQLite" and install it.

 

Make a copy of your library.db file (with emby shutdown so its up to date) and point the DB Browser at the copy of the db file

 

Then go to the Execute SQL tab and run the following

 

 

select path, name, sortname, cleanname, slugname from TypedBaseItems where TypedBaseItems.type = "MediaBrowser.Controller.Entities.Movies.Movie" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.TV.Episode" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.MusicVideo" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.Video"

 

 

This will give you a list of "video" files with their path and the various name fields within the database - virtually instantly :)

 

Its a very simple sql query that you can modify to add or subtract db fields to show more or less info

 

example (run on my test server which has the same file used in the various library types)

 

PATH                                                                                          NAME                                                       SORTNAME                                                     CLEANNAME                                        SLUGNAME

"F:\EmbyTest\MovieTesting\Testing\testing.mp4"                       "TED: Chinaka Hodge (2016 Women)"    "ted: chinaka hodge (0000002016 women)"    "ted: chinaka hodge (2016 women)"     "TED: Chinaka Hodge (2016 Women)"
"F:\EmbyTest\Unset\testing.mp4"                                               "TED: Chinaka Hodge (2016 Women)"    "ted: chinaka hodge (0000002016 women)"    "ted: chinaka hodge (2016 women)"     "TED: Chinaka Hodge (2016 Women)"
"F:\EmbyTest\MusicVid\Foo Fighters\testing.mp4"                     "TED: Chinaka Hodge (2016 Women)"    "ted: chinaka hodge (0000002016 women)"    "ted: chinaka hodge (2016 women)"     "TED: Chinaka Hodge (2016 Women)"
"F:\EmbyTest\TVTesting\Black Adder\Season 1\testing.mp4"    "TED: Chinaka Hodge (2016 Women)"     "001 - TED: Chinaka Hodge (2016 Women)"   "ted: chinaka hodge (2016 women)"   "TED: Chinaka Hodge (2016 Women)"
"F:\EmbyTest\HomeVid\testing.mp4"                                          "TED: Chinaka Hodge (2016 Women)"     "ted: chinaka hodge (0000002016 women)"    "ted: chinaka hodge (2016 women)"    "TED: Chinaka Hodge (2016 Women)"
 
If you want to sort the list just add "Order By TypedBaseItems.Path" to the end of the sql query above.
 
Have fun :)

 

Wow, this saves me heaps of time compared to my manual method!

 

As you've suggested, i've added the extra bits and removed the 'unneeded' columns, so here i just get Path, Name and Year:

 

select path, name, productionyear from TypedBaseItems where TypedBaseItems.type = "MediaBrowser.Controller.Entities.Movies.Movie" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.TV.Episode" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.MusicVideo" or TypedBaseItems.type = "MediaBrowser.Controller.Entities.Video" Order By TypedBaseItems.Path

Simply copying & pasting into Excel make it then easy to reorder, re-sort, etc, etc.

 

Reordering by year, allowed me to very easily scan thru the block of, say, 2013 films spotting any which are 2012 or 2014 for whatever reason...

Tho it then meant i discovered the Release Date/Time bug, which means EVERY movie's release date is a day out if you live east of the UTC timezone...ffs  ....  which doesn't matter much really...  BUT, it'll mean that any movie with a 1st January release day will then be wrongly labelled as the year before, as the date have been interpreted as 29th December.  These tend to be old moves where i guess the exact release date isn't know, so 1/1 is used.

 

Cheers again PenkethBoy for you sql skills!

Link to comment
Share on other sites

lexisdude

The report generation on emby is definitely something that needs some TLC. We need versatility when it comes to what we can tick and pull into a spreadsheet to view. Right now the reporting is more geared towards a "final report" - and that's fine if you are exporting the data to web pages or doing a quick check - but since these are server side tools it also needs have the flavor towards media/database administration

Link to comment
Share on other sites

CharleyVarrick

I have to come back and again thank PenkethBoy again for his workaround in getting side by side those 4 infos (path, filename, library name, library year).

But...

Having a relatively large collection, the problem is I still have to go through 23k Excel lines, because everything is listed, correct and incorrect. I spent 2 hours checking on 2017-2016-2015 movies, which represent maybe 3% of my collection.

 

I still wish I could get a report, preferably straight from Emby, with only entries where path/filename differ from library name/year, a kind of a "your attention might be needed here" feature.

Edited by jlr19
  • Like 1
Link to comment
Share on other sites

lexisdude

Honestly from a programming perspective - giving us a + to allow us to add as many columns in the report that we want to generate; and have them defined from a drop down list of friendly database names, operators (><=!=) , and possibly user string input should be diabolically simple. I could create my own front end in access in about 20 minutes to do just that. However; as you said - it should already be in the program since the fundamentals are already in place. Plus I am really big on using the least amount of programs to accomplish my needs. I already use a host of programs on the back end - why in the world would I want to use another :D

 

Edited by lexisdude
Link to comment
Share on other sites

PenkethBoy

Yes agree the reporting needs significant improvement including user(admin) defined reports.

 

@@jlr19 what comparison are you trying to do? i.e. how are you determining when comparing a filename with the name(s) in Emby that a "movie" is correctly identified

 

if its a name to name comparison then that would be easy to do in excel

  • Like 1
Link to comment
Share on other sites

CharleyVarrick

@@PenkethBoy

Quite a few things I compare

 

1) Pathname to filename, that would be to find my own mistakes in either names or year (both should be exact match)

2) While in there, I make sure I dont have a duplicate file inside a same folder (eg: Seraphim Falls.mkv and Seraphim Falls.mp4

3) Pathname and file name to library name

4) Path/file (year) to library year

 

Nothing very complicated, its just tremendously long to do it all manually.

Most likely a basic feature of Excel, but again, I am such an Excel newb its sad.

 

I am guessing I need to use the "texttocolumns" a few more times more to separate file name from year and file extension.

I would then need be able to compare apples with apples

Then there's the year in parenthesis vs plain year

Then its how to ask Excel to compare and single-out what differs.

 

58e654bc5eabc_Capture.png

Edited by jlr19
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...