[jhoyos]

Hi, 

I have been trying to export my movie catalog into a Excel spreadsheet, using templato of TMM as a base to increase the avaible information we have access to.

However, even following these instructions (Wiki Export Templates), I haven't been able to get them.  At some point, the export process fails and I am not having any file result.

Does anyone have a template to export ALL avaible fields to be exported?  Once I have them, I can easly get just the ones I am interested in.

Thanks in advanced.

[jhoyos]

Hi, 

I have been able to do some advences to export all the fields to excel.  Right now, I have a template to export basically all information for "movies", but I always get an error when introducing some field from "Mediafile" and I don't know how to export both "movies" and "mediafile" variables to an excel file.

Here is my template:

Code:


<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urnchemas-microsoft-com:officepreadsheet"
xmlns:o="urnchemas-microsoft-com:office:office"
xmlns:x="urnchemas-microsoft-com:office:excel"
xmlnss="urnchemas-microsoft-com:officepreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">

<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s22">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
<Style ss:ID="s23">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<NumberFormat ss:Format="Short Date"/>
</Style>
</Styles>

<Worksheet ss:Name="movielist">
<Table x:FullColumns="1" x:FullRows="1" ssefaultColumnWidth="60">

<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="70.00" ss:StyleID="s23" />
<Column ss:Width="70.00" ss:StyleID="s23" />
<Column ss:Width="70.00" ss:StyleID="s23" />
<Column ss:Width="300.00"/>
<Column ss:Width="300.00"/>
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="200.00"/>
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="200.00"/>
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="33.00" ss:StyleID="s22" />
<Column ss:Width="200.00"/>

${foreach movies movie}
<Row>
<Cell><Data ss:Type="String">${movie.title}</Data></Cell>
<Cell><Data ss:Type="String">${movie.originalTitle}</Data></Cell>
<Cell><Data ss:Type="String">${movie.tagline}</Data></Cell>
<Cell><Data ss:Type="String">${movie.plot}</Data></Cell>
<Cell><Data ss:Type="Number">${movie.year}</Data></Cell>
<Cell><Data ss:Type="String">${movie.imdbId}</Data></Cell>
<Cell><Data ss:Type="DateTime">${movie.dateAdded;date(yyyy-MM-dd)}T00:00:00.000</Data></Cell>
<Cell><Data ss:Type="String">${movie.path}</Data></Cell>
<Cell><Data ss:Type="String">${movie.releaseDateAsString}</Data></Cell>
<Cell><Data ss:Type="String">${movie.movieSet}</Data></Cell>
<Cell><Data ss:Type="String">${movie.movieSetTitle}</Data></Cell>
<Cell><Data ss:Type="String">${movie.certification}</Data></Cell>
<Cell><Data ss:Type="String">${movie.country}</Data></Cell>
<Cell><Data ss:Type="String">${movie.genresAsString}</Data></Cell>
<Cell><Data ss:Type="String">${movie.rating}</Data></Cell>
<Cell><Data ss:Type="String">${movie.tmdbId}</Data></Cell>
<Cell><Data ss:Type="String">${movie.spokenLanguages}</Data></Cell>
<Cell><Data ss:Type="String">${movie.tagsAsString}</Data></Cell>
<Cell><Data ss:Type="String">${movie.mediaInfoAudioCodecAndChannels}</Data></Cell>
<Cell><Data ss:Type="String">${movie.mediaInfoVideoCodec}</Data></Cell>
<Cell><Data ss:Type="String">${movie.mediaInfoVideoFormat}</Data></Cell>
<Cell><Data ss:Type="String">${movie.mediaInfoVideoResolution}</Data></Cell>
<Cell><Data ss:Type="String">${movie.mediaInfoVideoBitrate}</Data></Cell>
<Cell><Data ss:Type="String">${movie.hasMetadata}</Data></Cell>
<Cell><Data ss:Type="String">${movie.duplicate}</Data></Cell>
<Cell><Data ss:Type="String">${movie.hasImages}</Data></Cell>
<Cell><Data ss:Type="String">${movie.hasNfoFile}</Data></Cell>
<Cell><Data ss:Type="String">${movie.disc}</Data></Cell>
<Cell><Data ss:Type="String">${movie.hasRating}</Data></Cell>
<Cell><Data ss:Type="String">${movie.hasTrailer}</Data></Cell>
<Cell><Data ss:Type="String">${movie.runtimeFromMediaFiles}</Data></Cell>
<Cell><Data ss:Type="String">${movie.runtimeFromMediaFilesInMinutes}</Data></Cell>
<Cell><Data ss:Type="String">${movie.runtime}</Data></Cell>

</Row>
${end}

</Table>
</Worksheet>
</Workbook>


 

What do I have to write in order to export, let's say... Filename or extensión?
Do I have to do another "foreach..."? Or I can add another line below "movie.runtime" in order to get the filename? In this last case... how? I get an error if I just write this sentence bellow...

    <Cell><Data ss:Type="String">${movie.filename}</Data></Cell>

Thanks in advanced.

[mlaggner]

a movie can contain many media files (basically all files which can be assigned to a movie).
I think what you want to achieve is to get the "main" video file.

if you are on v3, you can directly access that: {movie.mainVideoFile}; on v2 you can get the first video file (basically the same as the main video file from v3 in 99% of all cases): {movie.firstVideoFile}
with that you can access all data from MediaFiles

[jhoyos]

Thanks for your response, but I don't know how to get the information I am looking for with your answer.

Could you tell what code lines do I have to write bellow my code?

[jhoyos]

Hi, finally from you tip "{movie.mainVideoFile}" I was able to extract MediaFile information.

Just for the record, in case anyone else have the same problem, I solved with this line bellow my old code:

    <Cell><Data ss:Type="String">${movie.mainVideoFile.filename}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.extension}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.durationHM}</Data></Cell>

Now... If I want to add besides, just the name of all actors in the movie, how do I have to do it?

If I write this I have a bunch of data that I dont want, as character, name, role and thumburl.

<Cell><Data ss:Type="String">${movie.actors}</Data></Cell>

How can I get JUST name of movie actors?

I tried this but It didn't work:

<Cell><Data ss:Type="String">${movie.actors.name}</Data></Cell>

Any tips?

[mlaggner]

actors is another array of Persons; this might help (but produces a trailing comma):

Code:


<Cell><Data ss:Type="String">${foreach movie.actors actor}${actor.name}, ${end}</Data></Cell>


[jhoyos]

Thanks a lot!  It worked perfect!

And the last thing... 

Is there any way to put "header name" into the result?

Anything I can add to this line to have a header in the excel file that says "Title" above the results?

    <Cell><Data ss:Type="String">${movie.title}</Data></Cell>

[jhoyos]

Please.. .another question...  I am trying to get a list of Audio Streams and their codec, as It does Plex Export Complement, that outputs this data:
 Audio Stream CodecAudio Stream ChannelsAudio Stream Bitrateac3 - ac36 - 6448 - 448ac3 - ac36 - 6448 - 384dca - dca - ac36 - 8 - 21536 - 1536 - 192
I wrote this line below my code, as with the actors.

    <Cell><Data ss:Type="String">${foreach video.audioStreams audio}${audio.codec}, ${end}</Data></Cell>

But nothing happened.  It output the xml file, but it leaves the last column (audio codecs) in blank.

How should I write that line in order to have an output similar as exposed?

Thanks in advanced for all your help.  I will write here the template once is finished in case it is useful for anyone else.

[jhoyos]

Hi, 

I have been able to figure it out how to do about Audio information.

I have inserted these lines into my code:

Code:


    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.codec},
${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.channels},
${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.bitrateInKbps},
${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.channelsAsInt},
${end}${end}</Data></Cell>


Basically the output works fine, but I am having problem regarding Audio Channels, that are not exported, eventhough the information is there in TMM.



As you can see in the Output file, either way using channels or channelsAsInt, I get in one case an empty value, and in other a 0 value, where I should read 2 or 6 channels.



Any help in this?  Why it is not showing channels information?

[jhoyos]

Anything about Audio Channels?  The information seems to be there, but I don't know why, TMM doesn't export it...  (see image in previous post).

Thanks in advanced.

PS:  I have solved about column titles.  Once I resolve Audio Channels, I will put here the hole template.

Regards,

[mlaggner]

sorry, was busy releasing v3..

channels/channelsAsInt is internally deprecated and imho no more filled in v3; could you try audioChannels ? (will adopt the wiki here)

[jhoyos]

Hi @mlaggner 

I have tried audioChannels and it worked perfect, so thank you very much for all your help since finally, I have movies template as I wanted, exporting all the fields that could be useful either for the movie information as per the video / audio resolution.

As I said, find attached my complete template.  I hope it can help other people since I have been looking for something similar for quite a long time with no success.

Code:


<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s22">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
  </Style>
  <Style ss:ID="s23">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 
 <Worksheet ss:Name="movielist">
  <Table x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">

   <Column ss:Width="200.00"/>
   <Column ss:Width="33.00" ss:StyleID="s22"/>
   <Column ss:Width="200.00"/>
   <Column ss:Width="200.00"/>
   <Column ss:Width="200.00"/>
   <Column ss:Width="200.00"/>
   <Column ss:Width="100.00"/>
   <Column ss:Width="100.00" ss:StyleID="s23" />
   <Column ss:Width="60.00" ss:StyleID="s22" />
   <Column ss:Width="60.00" ss:StyleID="s22" />
   <Column ss:Width="120.00"/>
   <Column ss:Width="70.00" ss:StyleID="s22" />
   <Column ss:Width="50.00" ss:StyleID="s22" />
   <Column ss:Width="50.00" ss:StyleID="s22" />
   <Column ss:Width="200.00"/>
   <Column ss:Width="260.00"/>
   <Column ss:Width="300.00"/>
   <Column ss:Width="70.00"/>
   <Column ss:Width="70.00" ss:StyleID="s23" />
   <Column ss:Width="100.00"/>
   <Column ss:Width="40.00"/>
   <Column ss:Width="40.00"/>
   <Column ss:Width="40.00"/>
   <Column ss:Width="70.00" ss:StyleID="s22" />
   <Column ss:Width="50.00" ss:StyleID="s22" />
   <Column ss:Width="50.00"/>
   <Column ss:Width="50.00"/>
   <Column ss:Width="50.00"/>
   <Column ss:Width="70.00"/>
   <Column ss:Width="33.00" ss:StyleID="s22" />
   <Column ss:Width="33.00" ss:StyleID="s22" />
   <Column ss:Width="33.00" ss:StyleID="s22" />
   <Column ss:Width="33.00" ss:StyleID="s22" />
   <Column ss:Width="33.00" ss:StyleID="s22" />
   <Column ss:Width="300.00"/>
   <Column ss:Width="100.00"/>
   <Column ss:Width="100.00"/>
   <Column ss:Width="100.00"/>


<Row>
     <Cell><Data ss:Type="String">Titulo</Data></Cell>
     <Cell><Data ss:Type="String">Year</Data></Cell>
     <Cell><Data ss:Type="String">Coleccion</Data></Cell>
     <Cell><Data ss:Type="String">Argumento</Data></Cell>
     <Cell><Data ss:Type="String">Titulo Original</Data></Cell>
     <Cell><Data ss:Type="String">Generos</Data></Cell>
     <Cell><Data ss:Type="String">Etiquetas</Data></Cell>
     <Cell><Data ss:Type="String">Fecha Estreno</Data></Cell>
     <Cell><Data ss:Type="String">ID Imdb</Data></Cell>
     <Cell><Data ss:Type="String">ID TMDB</Data></Cell>
     <Cell><Data ss:Type="String">Idiomas Hablados</Data></Cell>
     <Cell><Data ss:Type="String">Certificacion</Data></Cell>
     <Cell><Data ss:Type="String">Duracion Archivo</Data></Cell>
     <Cell><Data ss:Type="String">Duracion Pelicula</Data></Cell>
     <Cell><Data ss:Type="String">Pais</Data></Cell>
     <Cell><Data ss:Type="String">Ruta</Data></Cell>
     <Cell><Data ss:Type="String">Nombre de Archivo</Data></Cell>
     <Cell><Data ss:Type="String">Bytes</Data></Cell>
     <Cell><Data ss:Type="String">Fecha de Inclusion</Data></Cell>
     <Cell><Data ss:Type="String">Extension</Data></Cell>
     <Cell><Data ss:Type="String">Categoria</Data></Cell>
     <Cell><Data ss:Type="String">Codec Video</Data></Cell>
     <Cell><Data ss:Type="String">Formato</Data></Cell>
     <Cell><Data ss:Type="String">Resolucion</Data></Cell>
     <Cell><Data ss:Type="String">Video Bitrate</Data></Cell>
     <Cell><Data ss:Type="String">Aspect Ratio</Data></Cell>
     <Cell><Data ss:Type="String">Frame Rate</Data></Cell>
     <Cell><Data ss:Type="String">HDR</Data></Cell>
     <Cell><Data ss:Type="String">Codecs Audio</Data></Cell>
     <Cell><Data ss:Type="String">Canales Audio</Data></Cell>
     <Cell><Data ss:Type="String">Audio Bitrate</Data></Cell>
     <Cell><Data ss:Type="String">Subtitulos</Data></Cell>
     <Cell><Data ss:Type="String">Metadata</Data></Cell>
     <Cell><Data ss:Type="String">Duplicado</Data></Cell>
     <Cell><Data ss:Type="String">Imagenes</Data></Cell>
     <Cell><Data ss:Type="String">NFO</Data></Cell>
     <Cell><Data ss:Type="String">Trailer</Data></Cell>
     <Cell><Data ss:Type="String">Actores</Data></Cell>
</Row>

${foreach movies movie}
   <Row>
    <Cell><Data ss:Type="String">${movie.title}</Data></Cell>
    <Cell><Data ss:Type="Number">${movie.year}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.movieSet}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.plot}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.originalTitle}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.genresAsString}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.tagsAsString}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.releaseDateAsString}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.imdbId}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.tmdbId}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.spokenLanguages}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.certification}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.runtimeFromMediaFiles}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.runtime}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.country}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.path}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.filename}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.filesize}</Data></Cell>
    <Cell><Data ss:Type="DateTime">${movie.dateAdded;date(yyyy-MM-dd)}T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.extension}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.videoDefinitionCategory}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mediaInfoVideoCodec}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mediaInfoVideoFormat}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mediaInfoVideoResolution}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mediaInfoVideoBitrate}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.aspectRatio}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.frameRate}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.HDR}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.codec}, ${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.audioChannels}, ${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.mediaFiles mediafiles}${foreach mediafiles.audioStreams audio}${audio.bitrateInKbps}, ${end}${end}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.mainVideoFile.subtitlesAsString}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.hasMetadata}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.duplicate}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.hasImages}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.hasNfoFile}</Data></Cell>
    <Cell><Data ss:Type="String">${movie.hasTrailer}</Data></Cell>
    <Cell><Data ss:Type="String">${foreach movie.actors actor}${actor.name}, ${end}</Data></Cell>

   </Row>
${end}

  </Table>
 </Worksheet>
</Workbook>


Now... I will work in a similar template for series..., but in this case into another thread.

[mlaggner]

thanks.
Am I allowed to package this along with tmm?

[jhoyos]

thanks.
Am I allowed to package this along with tmm?

Sure you can.  Probably you would want to translate column header to english, since they are in spanish, but I have no inconvenient to put that template directly as a native template.

I will try to work on a similar template for TV shows.  

By the way... any threard or templeate of your knowledge to begin with?

Thanks a lot for your help.  Regards,

[jhoyos]

Hi mlaggner, 

I guess you haven't had time to include this template per default in TMM v3.

Anycase, I was wondering, since I have become crazy about this, how would I be able to include poster url into this template so I can export it to Excel.

Would you be able to help me in this matter?  It is the only thing I am missing and I have been trying with no success in it.

Regards, 

PS:  I would like to get the imagen url of the poster I have choosen when scrapping with TMM and appears in it.