Library export
Library export
Hi Bernd.
When exporting library to CSV format and then importing to excel under duration/playtime/length I get numbers in format which does not correspond to hh:mm:ss. How can I adjust this, please? I need to have it in this format in order to send some legal reports.
How can I for example convert value of 197691 from coloumn playtime to format hh:mm:ss?
Thank you.
When exporting library to CSV format and then importing to excel under duration/playtime/length I get numbers in format which does not correspond to hh:mm:ss. How can I adjust this, please? I need to have it in this format in order to send some legal reports.
How can I for example convert value of 197691 from coloumn playtime to format hh:mm:ss?
Thank you.
Peter
Re: Library export
Thank you very much Bernd. How can I change for example macro for "playtime" field so PF exports it like hh:mm:ss and not hh:mm? Because when I import csv into excel I see for example:
4:04
but in the cell is value of 4:04:00
So it takes it like the tracks has playtime of 4 horus and 4 minutes and 4 minutes and 4 seconds.
4:04
but in the cell is value of 4:04:00
So it takes it like the tracks has playtime of 4 horus and 4 minutes and 4 minutes and 4 seconds.
Peter
Re: Library export
That is already [HH:]MM:SS !
"4:04" means 4 minutes and 4 seconds.
The hour part is omitted, if not present (as it is optional and only present, if needed).
"4:04" means 4 minutes and 4 seconds.
The hour part is omitted, if not present (as it is optional and only present, if needed).
Bernd - radio42
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
Re: Library export
This is freely configurable
When exporting a library or media entries etc. you select an 'Export Format' (e.g. "Default CSV").
This export format is defined in a file called "ProppFrexx.exportformats" - which can be found in your ProppFrexx installation folder.
You might (if needed) edit this file (make a backup copy first!) - it is a standard XML file.
Note, that you need admin rights to edit a file contained in the installation folder.
The export format definition file contains multiple "<format ...>" sections - which define one particular export format.
Underneath you find a group called "<fields>" containing all fields to export ("<field name=...>${macro}</field>").
The macros in the above fields list define which track macro to resolve with it (see the User Manual Appendix for details and a complete list of available macros).
The "Default CSV" export format for example looks like this:
As you might see, the "Length" field for example uses the ${lengthsec} macro - which resolves to the track’s total length (duration) in seconds (incl. 3 decimal places).
The "All Fields CSV" export format does for example use the following:
Here, the "Length" field uses the ${lengthms} macro - which resolves to the track’s total length (duration) in whole milliseconds.
I assume, you are using this format in your example - as such "197691" are full milliseconds or "197.691"sec.?!
Instead you might want to use the "${length}" macro - which resolves to the track’s total length (duration) as a string ([HH:]MM:SS).
So just replace the used ${...} field macros by the macros you intend to use - that's it.
For example:
When exporting a library or media entries etc. you select an 'Export Format' (e.g. "Default CSV").
This export format is defined in a file called "ProppFrexx.exportformats" - which can be found in your ProppFrexx installation folder.
You might (if needed) edit this file (make a backup copy first!) - it is a standard XML file.
Note, that you need admin rights to edit a file contained in the installation folder.
The export format definition file contains multiple "<format ...>" sections - which define one particular export format.
Underneath you find a group called "<fields>" containing all fields to export ("<field name=...>${macro}</field>").
The macros in the above fields list define which track macro to resolve with it (see the User Manual Appendix for details and a complete list of available macros).
The "Default CSV" export format for example looks like this:
Code: Select all
<format name="Default CSV" type="CSV" extension=".csv" delimiter="," useHeader="True" quote=""" quoteEsc=""" quoteHeader="False" trim="True" encoding="latin1">
<fields>
<field name="Filename" start="0" length="0" align="left" pad=" ">${filenameandpath}</field>
<field name="Title" start="0" length="0" align="left" pad=" ">${title}</field>
<field name="Artist" start="0" length="0" align="left" pad=" ">${artist}</field>
<field name="Album" start="0" length="0" align="left" pad=" ">${album}</field>
<field name="Year" start="0" length="0" align="left" pad=" ">${year}</field>
<field name="Genre" start="0" length="0" align="left" pad=" ">${genre}</field>
<field name="Grouping" start="0" length="0" align="left" pad=" ">${grouping}</field>
<field name="Mood" start="0" length="0" align="left" pad=" ">${mood}</field>
<field name="ISRC" start="0" length="0" align="left" pad=" ">${isrc}</field>
<field name="Rating" start="0" length="0" align="left" pad=" ">${rating}</field>
<field name="BPM" start="0" length="0" align="left" pad=" ">${bpm}</field>
<field name="TrackType" start="0" length="0" align="left" pad=" ">${tracktype}</field>
<field name="AlbumArtist" start="0" length="0" align="left" pad=" ">${albumartist}</field>
<field name="Composer" start="0" length="0" align="left" pad=" ">${composer}</field>
<field name="Copyright" start="0" length="0" align="left" pad=" ">${copyright}</field>
<field name="EncodedBy" start="0" length="0" align="left" pad=" ">${encodedby}</field>
<field name="Publisher" start="0" length="0" align="left" pad=" ">${publisher}</field>
<field name="Conductor" start="0" length="0" align="left" pad=" ">${conductor}</field>
<field name="Lyricist" start="0" length="0" align="left" pad=" ">${lyricist}</field>
<field name="Remixer" start="0" length="0" align="left" pad=" ">${remixer}</field>
<field name="Producer" start="0" length="0" align="left" pad=" ">${producer}</field>
<field name="Length" start="0" length="0" align="left" pad=" ">${lengthsec}</field>
</fields>
</format>
The "All Fields CSV" export format does for example use the following:
Code: Select all
...
<field name="Length" start="0" length="0" align="left" pad=" ">${lengthms}</field>
<field name="Duration" start="0" length="0" align="left" pad=" ">${durationms}</field>
...
I assume, you are using this format in your example - as such "197691" are full milliseconds or "197.691"sec.?!
Instead you might want to use the "${length}" macro - which resolves to the track’s total length (duration) as a string ([HH:]MM:SS).
So just replace the used ${...} field macros by the macros you intend to use - that's it.
For example:
Code: Select all
...
<field name="Length" start="0" length="0" align="left" pad=" ">${length}</field>
<field name="Duration" start="0" length="0" align="left" pad=" ">${duration}</field>
...
Bernd - radio42
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
Re: Library export
Yes, but after import to excel it is imported as 4 hours and 4 minutes. Could you advice how to import it correctly or what to set in excel?
Peter
Re: Library export
That's an excel thing, not a ProppFrexx issue. I am not an excel expert.
However, when trying to import a test file it simply shows here as "4:04" - so I am not sure what you are doing to import your csv file.
Maybe simply adjust the column as a text column in the excel import assistant.
Else, please try a little research in the internet.
However, when trying to import a test file it simply shows here as "4:04" - so I am not sure what you are doing to import your csv file.
Maybe simply adjust the column as a text column in the excel import assistant.
Else, please try a little research in the internet.
Bernd - radio42
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
Re: Library export
Bernd, it shows 4:04 but try to edit the cell itself, if you press F2 and you will edit this cell you will see excel takes it as 4:04:00, i.e. 4 hours and 4 minutes. But of course, maybe there is something I have to do when importing, I will try tomorrow.
Peter
Re: Library export
Hi Bernd. Did you try it? Unfortunately I have to rewrite around 200 playtimes now because excel takes it as hh:mm and not mm:ss.
Peter
Re: Library export
I did exactly that. Hm, see attached csv and then imported file. Please, have a look on coloumns B and C and formula in coloumn C. Maybe then it will be much clearer what I want to achieve.
- Attachments
-
- PF_report.zip
- (12.42 KiB) Downloaded 393 times
Peter
Re: Library export
When converting a text to a datetime within Excel you can not simply take the text value and 'add' that up.
You first need to convert that to a DATETIME value, e.g. using the excel "=TIME(...)" or "=DATE(...)" function.
The TIME function for example expects 3 parameters: hour, minute and seconds.
These parameter values needs to be extracted from the text cell value first! E.g. using the LEFT(), MID() or RIGHT() etc. function.
This way you can easily 'parse' and 'format' your values.
For more details try a little research in the internet and consult the Excel help (as I did).
You first need to convert that to a DATETIME value, e.g. using the excel "=TIME(...)" or "=DATE(...)" function.
The TIME function for example expects 3 parameters: hour, minute and seconds.
These parameter values needs to be extracted from the text cell value first! E.g. using the LEFT(), MID() or RIGHT() etc. function.
This way you can easily 'parse' and 'format' your values.
For more details try a little research in the internet and consult the Excel help (as I did).
Bernd - radio42
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution
ProppFrexx ONAIR - The Playout and Broadcast Automation Solution