Page 1 of 2
Library export
Posted: 10 Oct 2012 22:18
by Farbo
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.
Re: Library export
Posted: 11 Oct 2012 15:10
by Farbo
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.
Re: Library export
Posted: 11 Oct 2012 16:11
by radio42
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).
Re: Library export
Posted: 11 Oct 2012 20:18
by radio42
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:
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>
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:
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>
...
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:
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>
...
Re: Library export
Posted: 11 Oct 2012 20:49
by Farbo
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?
Re: Library export
Posted: 11 Oct 2012 21:19
by radio42
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.
Re: Library export
Posted: 11 Oct 2012 21:23
by Farbo
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.
Re: Library export
Posted: 12 Oct 2012 11:04
by Farbo
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.
Re: Library export
Posted: 12 Oct 2012 12:08
by Farbo
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.
Re: Library export
Posted: 12 Oct 2012 13:39
by radio42
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).