CharleyVarrick 277 Posted April 1, 2017 Author Share Posted April 1, 2017 (edited) 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! Edited April 1, 2017 by jlr19 1 Link to comment Share on other sites More sharing options...
PenkethBoy 2063 Posted April 1, 2017 Share Posted April 1, 2017 Are you going to teleport it over.... yes the translators are a bit variable it was supposed to be "Life Universe and everything" 1 Link to comment Share on other sites More sharing options...
Guest topbanana Posted April 1, 2017 Share Posted April 1, 2017 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 More sharing options...
lexisdude 133 Posted April 2, 2017 Share Posted April 2, 2017 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 More sharing options...
CharleyVarrick 277 Posted April 5, 2017 Author Share Posted April 5, 2017 (edited) 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 April 5, 2017 by jlr19 1 Link to comment Share on other sites More sharing options...
lexisdude 133 Posted April 6, 2017 Share Posted April 6, 2017 (edited) 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 Edited April 6, 2017 by lexisdude Link to comment Share on other sites More sharing options...
PenkethBoy 2063 Posted April 6, 2017 Share Posted April 6, 2017 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 1 Link to comment Share on other sites More sharing options...
CharleyVarrick 277 Posted April 6, 2017 Author Share Posted April 6, 2017 (edited) @@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. Edited April 6, 2017 by jlr19 Link to comment Share on other sites More sharing options...
PenkethBoy 2063 Posted April 6, 2017 Share Posted April 6, 2017 @@jlr19 could you PM me a sample file as i think we can sort this with a bit of Excel foo 1 Link to comment Share on other sites More sharing options...
CharleyVarrick 277 Posted April 6, 2017 Author Share Posted April 6, 2017 PenkethBoy for prez! 1 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now