pyTivo Discussion Forum

[dlfl]

MetaToExcel scans all the pyTivo metadata files in a folder and creates an Excel spreadsheet with whatever meta items you configure inserted across the columns.

You can download it ***here***

The MetaToExcel.conf configuration file must be in the run time folder.
Edit the .conf file as desired. I think the format is obvious from the example included in the download. You will be able to change the Excel file and metadata folder in the GUI and they will be saved in the .conf file. The remaining lines must match pyTivo metadata item names and they are case sensitive. They determine what goes into the spreadsheet and the order of the columns.

For multiple fields (eg vActor), all the items are collected in one string with comma separators, and names with '|' in them are unscrambled.

Important note: This program requires .NET 2.0 on your computer and Excel 2003. It most likely won't work with any other version of Excel.

You should download and install the ***Primary Interop Assemblies for Office 2003***. Although they may already be installed, the best bet is to try to install them - it won't do any harm and should ensure you have the latest versions - and it's a very simple process.

Enjoy!

[jimpmc]

Thanks for the tool.

I tried using it, but just got a blank spreadsheet as the result. The program ran OK and it created an xls file, but it did not populate any results. This was using the default fields in the conf file.

Using Excel 2003, Net 2.0 and installed the latest interop from your link.

[dlfl]

jimpmc,

Did you set the Metadata folder to an existing folder on your system that contained valid pyTivo metadata files?

The furnished .conf file is not a default -- just an example. The metadata folder and excel path will probably only work on my test system.

Also are you running XP or Vista? Has only been tested on XP.

[jcthorne]

Too Cool. Works like a champ!

Do you know if there is a way to sort in Excel and ignore leading 'The' and "A" on titles? Or could these be imported with this moved to the end of the title ie:

Bug's Life, A

I was able to open the xls file with Office 2010 without difficulty.

If you are looking to improve it, perhaps some basic formatting options? Column widths, word wrap, title row etc.

Also conversion from the metadata format star ratings of X1 to X7 to stars (mpaa rating *.749 then rounded up to the next half star)

Conversion of the mpaaRating to US standard MPAA ratings
G1 = G
P2 = PG
P3 = PG13
R4 = R
X5 = X
N6 = NC17

Just ideas. I can't be the only person with enough movies to need some way of keeping up with them.

[dlfl]

I think at least some of your suggestions can be done.

Did you download/install the Primary Interop Assemblies for Office 2003 linked in the first post, or did it work without doing that? Are you running Win7 ?

That program was developed on an XP PC and I haven't even used it for over 2 years. I'm now running Win7 and don't even have a version of Office installed -- I'm using Open Office. Could be interesting.

If you happen to be a C# programmer, I might just turn the code over to you if you wish.

[jcthorne]

Don't spend too much of your time on this if I am the only one using it. Using it I can collect the data, just a bit more work. Unfortunatly I am not a C# programmer. Or any language really. I have done a bit of python and batch script editing but nothing much since college.

I am using Office 2010 running on Windows XP (Our current corporate standard and its thier laptop). I did not download the Office add on, it worked as is.

One last item on the wish list if you do decide to go in to the code a bit. It would be nice if it could recurse subdirectories beneath the one where started.

[dlfl]

I can't even run, or build, MetaToExcel on my current PC because I don't have any version of Office installed, and I can't install the Primary Interop Assemblies for Office 2003 (because installation requires Office to be installed first).

However, how about this:
The program can easily be converted to produce .csv (comma separated variables) files that will load into any version of Excel (and into the Open Office spreadsheet program). I think the main reason I programmed a direct Excel output was just the novelty of programming directly into Excel spreadsheets.

Is there any interest in having it crawl subdirectories and pick up all the metadata files in a folder tree?

It could also have an "append" option where new data is added to an existing (.csv) file.

[jcthorne]

For me, the csv file would be just as useful now, and in the future, perhaps useful in other ways. Direct to excel is really not necessary.

[dlfl]

OK, give the attached MetaToCSV Ver. 1.00 a try.

Can optionally process all subfolders of selected folder.
If specified output .csv already exists, prompts whether to replace or append.

Note the .conf file is named differently and has a different format for one item (for obvious reasons).

I will work on the other enhancements you mentioned. It would also be easy to enter a maximum field width (either in GUI or .conf file) and have each ouput string truncated to that length. If a different length for each column (meta tag) was desired it could be included in the .conf file, e.g.
title 50
with absence of a number meaning don't truncate.

Obviously setting column widths, etc. in the spreadsheet is out of scope!

[jcthorne]

Thanks!

Seems to work fine.

[dlfl]

Is a length-truncation feature of any value?

[jcthorne]

Not something I need. I just tell excel to word wrap the cell and even paragraphs fit fine.

[jcthorne]

I still use this tool quite often for keeping records of what is in our collection.

Dealing more with episode TV shows lately and have a request if you have the time.

Would it be possible to store the seasonNumber information from the file name as it does not appear in the metadata. episodeNumber only contains the episode within a season.

Or, would it be better to have metagenerator add a tag to the metadata seasonNumber even though Tivo does not use it? Perhaps it would be useful in videomgr.

Also, some discriptions contain commas and seem to corrupt the data line when brought into excel. Same is true for lists of actors but its not every comma, not sure why.

[dlfl]

Each text value is output enclosed in double-quotes. The only mechanism I can imagine to cause corruption is if the text value has a double-quote AND a comma within it. Thus the attached version 1.02 replaces double-quotes ( " ) with two single quotes ( '' ) before writing the value out. See if that fixes the "comma" problem.

I'm not sure about the season number request. Are you suggesting parse it from the file name? I seem to remember TiVo episode numbers typically being a concatenation of season and episode number, e.g.: 507 would mean Season 5, Episode 7. That could be unraveled to yield two items if that would suffice. Having Metagenerator output the additional season number tag would be possible, I think, but for TiVo compatibility the Episode Number has to remain a concatenation in pyTivo metadata files. MetaToCSV could parse out the last two digits as episode number I guess.

[jcthorne]

Thanks, will give this a try.

I did notice some of my tv episodes had episode numbers of that form SSEE but not all, did not know metagenerator was already doing that. If I update all the shows not currently stored that way, things will sort fine as is so nothing to do. Thanks for the information and the fix!