(Utility) Excel Template to import MMD file contents

English Scripts
1

[Attachment removed—updated version in post below!]

OK, here it is. This is written as an Excel template (.xlt): the idea being that you create a new workbook for each directory or whatever using File, New, From Template, On my computer. You then import the files and save the result as a new XLS. This also prevents you unintentionally saving data in the ‘master’ template file. :wink:

You will need to Enable Macros in Excel to use this template.

Save the template on your computer in:
C:\Documents and Settings[i]user.machine[/i]\Application Data\Microsoft\Templates.
I think you should also be able to just Open it in Excel and immediately Save As… an Excel Template. Then CLOSE it!

To import files, first click File, New, then under From Templates, click On my computer. In the dialog, click Import MMD Files.

To use the import function, click the button (duh!) then select the directory of MMD files you want to import from, and whether you want to process (import) MMD files in all its subdirctories as well (this is an ‘all-or-nothing’ choice, you can’t select specific subdirs to process-or-not!). Remember (unless you have Excel 2007) there is a limit of 65,500-odd lines, so choose your directory wisely, but hopefully that won’t be actually be an issue for anyone (?).

The Excel screen is then ‘frozen’ to improve performance, so DON’T PANIC!

The Excel status bar will show a count, and you’ll see the individual MMDs being opened/closed on your Taskbar. The import is writing to the main sheet all this time, but displaying it ‘doing that’ would just slow it down. :wink: If you’re really paranoid, you can comment out the Application.ScreenUpdating = False in the code.

When all the files have been processed, the ‘curtain is opened’ and you’ll see all the info. in the sheet. :o
At this point, you should immediately SAVE the workbook under a sensible name (e.g. Drive F music - oldies.xls). You might also want to Delete the big ‘go’ button so you can’t click it by accident. ;D Then SAVE again.

If you want to import from another drive or directory, close the workbook and open another New one from the Template. Don’t re-use the same workbook!

The Template imports almost everything in the MMD except Comments and Actions (anyone really need those?). Times are stored as seconds and thousandths; fade duration as a whole number (of milliseconds); fixed time (if any) as a string. Up to four alternate cue points can be stored for each cue point, for example it will store a CueIn plus up to four alternate CueIn values. Any fifth (or more) Alternative for the same cue point is ignored.

Suggestions welcome. Exporting is a whole different job: I want to be certain that this one is 100% AOK before I start even considering whether that is sensible/possible.

Enjoy!

BFN
CAD

2

Well it looked nice, but I can’t see an Import Button - I can load the template OK and see the worksheet, but I if I try to access it via the Macro menu, I can navitate to a folder (to import MMD) but I get the following error. I have tried my Macro security on both Medium (ask) and Low, yet to no avail.

It’s a Vista machine, SP1 with Office XP - I also have Visual Studio 6 (the C++ and VB suite) installed.

3

Cheers CAD.

I too can’t see the import button (running XP with SP2 and Office 2000 on this PC) and running the macro gives the attached error.

Ron.

Error1.png

4

[Attachment removed—updated version in post below!]

Charlie: The OpenXML statement I use requires Excel 2003 or later. Please try the new version anyway and if it blows up at OpenXML, change the statement from OpenXML to Open (which should work the same though I haven’t tried it).

I’ve fixed the other bugs and given you options for time formats in the workbook:

  • Minutes, seconds, thousandths (TEXT value in m:ss.ttt format)
  • Seconds and thousandths (TEXT value in sss.ttt format)
  • Excel Time value (number in Excel date serial format, like 0.nnnnnnn)
    (Principal reason to use option 3 is if you plan to do any arithmetic later with—for example—Durations.)

The Time option (as it says in the dialog) affects Duration and cue points. Fixed Time is always stored as a TEXT (string) value; Fade Duration is always stored as a whole number (of milliseconds).

Sorry about the ‘false start’ last time: the updated version is in the attachment.
[Attachment removed—updated version in post below!]

BFN
CAD

5

Thanks, Cad - that works like a charm :wink:

6

Excellent! Charlie: delighted to help. Hopefully you can follow the code, and even make use of it in other projects?

Two tiny caveats to the Import macro, revealed during my testing at home.

  1. VERY old (ca. late 2006/early 2007) MMD files store cue point info on a single line, like this:

<Ramp><Value>112096842</Value></Ramp>The macro will not import these correctly because it expects to find the Value on a separate line, like this (standard layout for current MMD files):

<Ramp> <Value>112096842</Value> </Ramp>If you have MMDs which contain cue point values that don’t import into the worksheet, this may be the reason. I don’t know whether opening and re-saving the MMD (in File Tagger or main program) will re-write the file in ‘current’ format or not [FX (voice on PA): Paging Dhr. Weibert! Dhr. Weibert to the SCRIPTS forum, please!].

  1. Similarly ancient MMDs may also contain a tag with values between them (precursor of current Attributes). Once again, any such tags are ignored by the Import macro.

Folks: please let me know if you need anything else from within an MMD that I’m not already importing; or indeed any more bugs you unearth in the code.

PS to Torben: there’s a one-line change already built-in near the top of the Excel code to allow decimals to be stored as (e.g.) 0,9 instead of 0.9 just in case anyone in the German fora is looking for an ‘MMD import’ into an Excel workbook.

BFN
CAD

7

I don’t think the line breaks are the problem here - in terms of XML, you can have a line break or not between the tags, they’re just ignored.

Perhaps the MMD files are from a version of mAirList which did not support alternative cue points yet? Then the MMD would look like this:

<Ramp>12096842</Ramp>

Charlie, can you please post one of the non-working files to clarify this?

8

Well I’ve had a few MMD files throw-up an error… here’s a recent one:

[code]
Kim Wilde

Never Trust A Stranger 2450000000 4128458 2375516893 Album The very best of Kim Wilde Year 1988 C:\mAirList\icons\Icon - Song.ico [/code]

and just as I was typing:

[code]
Kylie Minogue

On A Night Like This 2090000000 t C:\mAirList\icons\Icon - Song.ico [/code]

Opening the song in mAirList and just re-saving the MMD file appears to work :slight_smile: Although looking at the new MMD file on that 1st example, it looks the same :-\ I’m still having a tinker with the importer and will let you know if I spot a pattern. I managed to do A-J (about 5500 files) without any hassle…

9

Hmmm… I’m getting random errors now - It just stopped on a James Brown track, but before, it had stopped on Kylie Minogue… I’m going to load up all my J-Z songs and update the MMDs and see what the Importer does after that.

Here’s before and after (note the rounding-down of the original duration value, which my James Brown track also had)

Kylie Minogue

On A Night Like This 2090000000 t C:\mAirList\icons\Icon - Song.ico Kylie Minogue On A Night Like This 2096326530 t C:\mAirList\icons\Icon - Song.ico
10

OK - I took a chance and left the Import to it (J files onwards) and it worked - WITHOUT a re-save of MMDs… I suspect that me typing on the keyboard probably stopped the import process especially since the taskbar moves around with extra windows :wink:

11

YES… NOT a good idea to do ANYthing else whilst the importer is working, because you could easily Screw Up things. I switch the ScreenUpdating OFF solely and simply to speed up the processing. Disturbing it in any way whilst it is working by performing Any Other Acivity is Not Advised—as Charlie has discovered the hard way! :-\ My advice? Go make a coffee or use some of the items for sale in Torben’s student station’s site shop :wink: for a few minutes, then go back after a few minutes to gawp open-mouthed at the carnage! ;D

Pleased to hear it’s working! On my now-wheezy 1.4GHz box with 2GB of RAM, it munches about 300 files a minute: what import speeds do others get? Please let me know your system RAM and processor speed: just interested!

Torben: the ‘problem’ with single-line cue-point tags is because I’m effectively parsing the MMD as a ‘flat’ file. If you look at the code you will understand :o but probably not approve! I messed around with the Excel built-in XML handling but without an appropriate XSL (or ‘XML Map’ as Excel calls it) it’s useless: hence my previous question to you (‘do you have an XSL file for MMD files?’). :wink: So I gave up on that idea, and went back to good old-fashioned vars for ‘open’ tags, line-by-line (or should that be row-by-row?) parsing, and so on.

So to sum up, it’s purely because my admittedly somewhat kludgy code is ‘expecting’ and on lines on their own, with a … pair on another line between them, that my code doesn’t (currently!) ‘understand’ lines like:
…

I could certainly add that functionality (handle ‘old school’ single-line cue points) in a future version (it’s just time-consuming :D): what do the panel think of that notion? Worth the few hours it will take to implement and test?

BFN
CAD

12

Well done, Cad - this is a very neat script. After some playing around, it works on my 12,000+ folder of MMD files and shows everything as expected. A simply “Paste Special/Multiply” (enter 1000 into a spare cell, copy it to the clipboard) gets my Cue points into milli-seconds :wink: The bottom-line, as you say, do not touch the keyboard as ANY key cancels the import cycle… The mouse, however, is OK to use :slight_smile:

I’ll do another import run and let you know how long it takes my mammoth folder of files to appear!

13

Y’know, it wouldn’t take much effort to create a new fourth Option of times-as-mS if that’s what you’d prefer? These would be whole numbers, but stored as strings (if I didn’t they’d display as something weird like 27th October 1908!). Frankly, it would be the simplest ‘conversion’ of the lot! (think about it :o). This would apply to everything except Fixed Time (!) and Fade Duration (which is in mS anyway). Please say if you would like that.

Incidentally, since much of my working life these days revolves around Excel, I’d recommend creating a new Sheet and a simple Copy/Paste of the whole Import sheet to neatly create a duplicate. Then do fancy stuff like Paste/Multiply, knowing you still have the original intact (you DID Save the imported results already, right? ;)) if anything goes Horribly Wrong at any point. ;D

Don’t forget to let me know roughly the number of files per minute, processor speed, and system RAM.

BFN
CAD

14

Just did a full import of 15,668 MMD files… In the 1st minute, it had done 900 files - it took 17.5mins to do the lot. My system is a P4 3.6GHz, 2GB RAM running Vista Ultimate SP1.

EDIT: Ah, wouldn’t mind mS as an import option - although anybody brave enough to faff about with imports and data like this should know how to convert it :wink:

15

OK. I’ll add an Option to import in milliseconds, and ‘while I’m there’ I’ll also add code to handle pre-alternative-cue-points MMDs (e.g. lines like [font=Courier]2315358485[/font]).

So … I could also import the Comments field but: does anyone use it? I may just add it anyway for completeness, since now I can import MMDs ;), we may use it at LFM in future to note ‘bad’ files (e.g. DO NOT PLAY: GLITCH AT 1:43). I’d be very interested to know whether anyone actually uses file comments and if they do, what kinds of comments they add (highest chart position, random artist fact, weeks at number one, etc.).

This will likely be a ‘weekend’ job, fitted around the qualifying and race for the Brazilian F1 GP (vroom! GO LEWIS!).

BFN
CAD

16

Sorry for the late reply… I actually use Comments where required. As you say: facts about a song/artist/band etc, a warning if the song contains explicit lyrics - although the filename would also show this. I generally use the Comments to give details about a new song or the fact that it may be the next release from the album etc.

17

Hmm … fair enough.

Unfortunately, the way the code works right now, it doesn’t handle multi-line comments, for example:

<Comment>Their first number one.
Stayed at the top for eight weeks in 1975.</Comment>

… or similar. So if you pressed Enter in the Comments box in PFL or File Tagger so that it looks more sensible, you’ve created a multi-line Comment tag in the MMD’s XML.

Very kludgy, I know (I can just seeTorben doing this=> ::slight_smile: as I type!), but I think I’ll need to add a Comments handler which will parse ANY comments, including multi-line comments, as a procedure separate from the main MMD file processing loop. That said, the main loop WILL detect the presence/absence of a Comment, so the ‘comment wrangler’ code will only fire up if there IS a Comment in the MMD being processed, to keep the processing overhead down and the speed UP.

Maybe get time to do that tomorrow. I’ve got the import-as-mS option working, BTW. :wink:

BFN
CAD

18

Right, chaps! The multi-line comments are now handled correctly :), and there’s a little bonus feature in the new version if you use Color [sic] in mAirList to set a background colour for a file.

If you haven’t looked at this yet, it’s now pretty stable and you never know what unexpected use you may find for it, even if it’s just checking that all your MMDs have cue points in them!

Charlie: the Comments field is IIRC column BX or thereabouts (i.e. the final stored field column).

Here’s the new version of the Excel template. Enjoy! ;D

[Template removed: updated Version 5.1 is in the post below.]

BFN
CAD

19

As part of the testing for the companion Export MMD Files Excel template, I discovered and fixed a couple of nasty bugs in the previous version.

The updated Version 5.1 is attached.

[Template removed: updated Version 6.0 is in the post below.]

BFN
CAD

20

Now that mAirList V2.2.2 and later imports CD Track numbers (at my request: thanks, Torben!), I’ve written a new version (V6.0) of the Excel Import template which writes track numbers into the new Track column in the spreadsheet.

Other changes:
[ul][li]Completely numeric titles/artists (e.g. 19 by Paul Hardcastle) are now correctly written into the spreadsheet as TEXT (not numbers).[/li]
[li]The Colour column uses the inverse of the mAirList ‘Color’ value as its Font colour, thus mid-grey colours resulted in ‘invisible’ text. ::slight_smile:
Mid-greys now use a Font colour of black in their cells.[/li][/ul]

–
BFN
CAD

Import MMD Contents V6.0.zip (55.2 KB)