Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[vbphil]

I need to compare the audio album content of several different MC25 Libraries to do some organizational cleanup. I thought this might be something quick and easy given the Library XML output of MC25 and the XML Import in Microsoft Access 2016. With the data in Access I'll be able to do all the comparing I need. But, as is always the case, it doesn't work. Access doesn't like the structure of the XML file from MC25.

Has anybody been there and done that? I probably need a transformation file for Access.

Thanks,   -phil

[JimH]

Try copy and paste between MC and MS Access.

[vbphil]

Not sure copy paste is the way to go.
MC25's XML format is like this

<MPL Version="2.0" Title="Library" PathSeparator="\">
   <Item>
      <Field Name="Filename">F:\Music\A Touch Of Classics\21 Piano Miniatures - Lars Roos\Gipsy Dance.mp3</Field>
      <Field Name="Name">Gipsy Dance</Field>
      <Field Name="Artist">A Touch Of Classics</Field>
      <Field Name="Album">21 Piano Miniatures - Lars Roos</Field>
      <Field Name="Genre">Classical</Field>
      <Field Name="Date (readable)">1979</Field>
      <Field Name="Date">28856</Field>
      <Field Name="Bitrate">128</Field>
      <Field Name="Image File">INTERNAL</Field>
      <Field Name="Rating">4</Field>
      <Field Name="Duration">154.0440000000000111</Field>
      <Field Name="Track #">11</Field>
      <Field Name="Media Type">Audio</Field>
      <Field Name="Album Artist">A Touch Of Classics</Field>
      <Field Name="Date Imported">1386771174</Field>
      <Field Name="File Type">mp3</Field>
      <Field Name="File Size">2484224</Field>
      <Field Name="Date Created">1306711965</Field>
      <Field Name="Date Modified">1332351648</Field>
      <Field Name="Compression">CBR (MPEG-1 Layer 3)</Field>
      <Field Name="Composer">Lars Roos</Field>
      <Field Name="Sample Rate">44100</Field>
      <Field Name="Channels">2</Field>
      <Field Name="Bit Depth">16</Field>
      <Field Name="Storage">Box10</Field>
      <Field Name="Length In PCM Blocks">6792815</Field>
      <Field Name="Date Tagged">1569503732</Field>
      <Field Name="HDCD">0</Field>
   </Item>
   
What Access wants is a structure like this:

<MPL Version="2.0" Title="Library" PathSeparator="\">
   <Item>
      <Filename>F:\Music\A Touch Of Classics\21 Piano Miniatures - Lars Roos\Gipsy Dance.mp3</Filename>
      <SongName>Gipsy Dance</SongName>
      <Artist>A Touch Of Classics</Artist>
      <Album>21 Piano Miniatures - Lars Roos</Album>
      <Genre>Classical</Genre>
      <Datereadable>1979</Datereadable>
      <DateValue>28856</DateValue>
      <Bitrate>128</Bitrate>
      <ImageFile>INTERNAL</ImageFile>
      <Rating>4</Rating>
      <Duration>154.0440000000000111</Duration>
      <Track>11</Track>
      <MediaType>Audio</MediaType>
      <AlbumArtist>A Touch Of Classics</AlbumArtist>
      <DateImported>1386771174</DateImported>
      <FileType>mp3</FileType>
      <FileSize>2484224</FileSize>
      <DateCreated>1306711965</DateCreated>
      <DateModified>1332351648</DateModified>
      <Compression>CBR (MPEG-1 Layer 3)</Compression>
      <Composer>Lars Roos</Composer>
      <SampleRate>44100</SampleRate>
      <Channels>2</Channels>
      <BitDepth>16</BitDepth>
      <Storage>Box10</Storage>
      <LengthInPCMBlocks>6792815</LengthInPCMBlocks>
      <DateTagged>1569503732</DateTagged>
      <HDCD>0</HDCD>
   </Item>
</MPL>   

[RoderickGI]

Based on that XML you posted, it looks like you are just exporting a Playlist in MPL format. Is that really what you want?

The MC format should work, although I haven't tested with Access. The format used is just defining the fields as well and the content. That may be an Access setting or configuration, for imports.

But by far the easiest method is to:

1. Create a View of the data you want to share.
2. Add all the fields you want to use for the comparison into that View, in the sequence you want to use (left to right), and sorted how you want them sorted.
3. Select All (Ctrl+A)
4. Copy (Ctrl C)
5. Go to Access, select a starting point, and Paste the data (Ctrl+V).
Done.


I have used this method with Excel, and it works great. If the above doesn't work in Access, do your comparison in Excel. It would probably be even easier.

You could even create the View in one Library, Save it, move the saved file to another Library and Load that View. That way you know you would have identical Views for the Copy from each Library and Paste into Access/Excel.

[vbphil]

The first XML snippet I posted is what you get when running the Library Export, there's a button for that in MC25 on the Library page. I'm only showing one record, the actual file has 8727 records in it. It's dumping the full Library not just a playlist.

The XML export doesn't load in Excel either from the MC25 XML file.

I'd rather stick with using XML files and not Copy/Paste. I'm sort of a purest that way with using files or data connections to move data around. Also, easier for me to set up a process that can be easily duplicated.

I'm working on a XSLT transformation file that will instruct Access, during the load, how to read the MC25 file. Pretty basic stuff I just hadn't done one in years and was hoping someone had already produced one. I'll post it here so maybe it will help the other guy out there wanting to do the same thing.

[RoderickGI]

Fair enough to use XML, and probably script the whole process, if you are going to repeat the process regularly.

The Copy/Paste method is just very quick and simple, and pretty flexible, for once off requirements.

[vbphil]

Here's the transformation code that works with Microsoft Access when importing an MC25 Library XML Export.
Save it as a XLST file and make any field changes to fit your Library export.
Notice that some of the field names from MC have to be changed because they are reserved names in Access or I just didn't like them. You can make them fit whatever table column names in Access you are using.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:transform version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:template match="/MPL">
<root>
<xsl:for-each select="Item">
<Music>
   <Filename><xsl:value-of select="Field[@Name='Filename']"/></Filename>
   <SongName><xsl:value-of select="Field[@Name='Name']"/></SongName>
   <Artist><xsl:value-of select="Field[@Name='Artist']"/></Artist>
   <Album><xsl:value-of select="Field[@Name='Album']"/></Album>
   <Genre><xsl:value-of select="Field[@Name='Genre']"/></Genre>
   <Datereadable><xsl:value-of select="Field[@Name='Date (readable)']"/></Datereadable>
   <DateValue><xsl:value-of select="Field[@Name='Date']"/></DateValue>
   <Bitrate><xsl:value-of select="Field[@Name='Bitrate']"/></Bitrate>
   <ImageFile><xsl:value-of select="Field[@Name='Image File']"/></ImageFile>
   <Rating><xsl:value-of select="Field[@Name='Rating']"/></Rating>
   <Duration><xsl:value-of select="Field[@Name='Duration']"/></Duration>
   <Track><xsl:value-of select="Field[@Name='Track']"/></Track>
   <MediaType><xsl:value-of select="Field[@Name='Media Type']"/></MediaType>
   <AlbumArtist><xsl:value-of select="Field[@Name='Album Artist']"/></AlbumArtist>
   <DateImported><xsl:value-of select="Field[@Name='Date Imported']"/></DateImported>
   <FileType><xsl:value-of select="Field[@Name='File Type']"/></FileType>
   <FileSize><xsl:value-of select="Field[@Name='File Size']"/></FileSize>
   <DateCreated><xsl:value-of select="Field[@Name='Date Created']"/></DateCreated>
   <DateModified><xsl:value-of select="Field[@Name='Date Modified']"/></DateModified>
   <Compression><xsl:value-of select="Field[@Name='Compression']"/></Compression>
   <Composer><xsl:value-of select="Field[@Name='Composer']"/></Composer>
   <SampleRate><xsl:value-of select="Field[@Name='Sample Rate']"/></SampleRate>
   <Channels><xsl:value-of select="Field[@Name='Channels']"/></Channels>
   <BitDepth><xsl:value-of select="Field[@Name='Bit Depth']"/></BitDepth>
   <Storage><xsl:value-of select="Field[@Name='Storage']"/></Storage>
   <LengthInPCMBlocks><xsl:value-of select="Field[@Name='Length In PCM Blocks']"/></LengthInPCMBlocks>
   <DateTagged><xsl:value-of select="Field[@Name='Date Tagged']"/></DateTagged>
   <HDCD><xsl:value-of select="Field[@Name='HDCD']"/></HDCD>
</Music>
</xsl:for-each>
</root>
</xsl:template>
</xsl:transform>

[jcw]

Many thanks to VBPhil for the XSLT. it works a treat.
I've been using Access to bulk search for things like duplicate copies with different formats.  There is probably a clever way to do that within Media Centre but I'm better at SQL