Template to export ALL avaible fields into Excel
#1
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.
Reply
#2
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="urnConfusedchemas-microsoft-com:officeConfusedpreadsheet"
xmlns:o="urnConfusedchemas-microsoft-com:office:office"
xmlns:x="urnConfusedchemas-microsoft-com:office:excel"
xmlnsConfuseds="urnConfusedchemas-microsoft-com:officeConfusedpreadsheet"
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" ssBig GrinefaultColumnWidth="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.
Reply
#3
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
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
Image
Reply
#4
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?
Reply
#5
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?
Reply
#6
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>
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
Image
Reply
#7
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>
Reply
#8
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.
Reply
#9
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.

Image

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.

Image

Any help in this?  Why it is not showing channels information?
Reply
#10
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,
Reply
#11
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)
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
Image
Reply
#12
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.
Reply
#13
thanks.
Am I allowed to package this along with tmm?
tinyMediaManager - THE media manager of your choice - available for Windows, Mac OSX and Linux
Help us translate tinyMediaManager Weblate
Image
Reply
#14
(2019-06-05, 07:50)mlaggner Wrote: 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,
Reply
 
Thread Rating:
  • 0 Vote(s) - 0 Average



Logout Mark Read Team Forum Stats Members Help
Template to export ALL avaible fields into Excel00