Library export

You have a question or need an advice about how to do something? Ask it here!
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Library export

Post 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.
Peter
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Re: Library export

Post 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.
Peter
User avatar
radio42
Site Admin
Posts: 8295
Joined: 05 Apr 2012 16:26
Location: Hamburg, Germany
Contact:
Re: Library export

Post 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).
User avatar
radio42
Site Admin
Posts: 8295
Joined: 05 Apr 2012 16:26
Location: Hamburg, Germany
Contact:
Re: Library export

Post 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>
...
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Re: Library export

Post 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?
Peter
User avatar
radio42
Site Admin
Posts: 8295
Joined: 05 Apr 2012 16:26
Location: Hamburg, Germany
Contact:
Re: Library export

Post 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.
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Re: Library export

Post 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.
Peter
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Re: Library export

Post 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.
Peter
Farbo
Posts: 349
Joined: 11 Apr 2012 15:04
Re: Library export

Post 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.
Attachments
PF_report.zip
(12.42 KiB) Downloaded 393 times
Peter
User avatar
radio42
Site Admin
Posts: 8295
Joined: 05 Apr 2012 16:26
Location: Hamburg, Germany
Contact:
Re: Library export

Post 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).

Post Reply