2008 Winter Scripting Games

Solution to Advanced VBScript Event 8: Making Beautiful Music

VBScript solution to Event 8 in the 2008 Winter Scripting Games.

Solutions are also available for Windows PowerShell and Perl.

Making Beautiful Music

Here’s something you might find hard to believe: the Scripting Guys had absolutely no problems with this event. In fact, the first time they ran the script it worked perfectly, and it continued to work perfectly each time they re-ran.

And yes, it does make you wonder who really wrote the script, doesn’t it?

Well, regardless of who wrote it, the important thing is that this script is able to quickly assemble a burn list for a music CD. And it does so using code no more complicated than this:

Const ForReading = 1

Set objDictionary = CreateObject("Scripting.Dictionary")
Set DataList = CreateObject("System.Collections.ArrayList")

intTotalTime = 0

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\SongList.csv", ForReading)

strContents = objFile.ReadAll

objFile.Close

arrContents = Split(strContents, vbCrLf)

intLowNumber = 0
intHighNumber = UBound(arrContents) - 1

Do While True
    Randomize
    intNumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)

    If objDictionary.Exists(intNumber) Then
    Else
        objDictionary.Add intNumber, intNumber
        strSong = arrContents(intNumber)
        arrSongInfo = Split(strSong, ",")
        strArtist = arrSongInfo(0)
        strTitle = arrSongInfo(1)
        arrTime = Split(arrSongInfo(2), ":")
        intTime = (60 * arrTime(0)) + arrTime(1)
        If (intTotalTime + intTime > 4800) Then
        Else
            If Not objDictionary.Exists(strArtist) Then
                objDictionary.Add strArtist, "1"   
            End If
            If objDictionary.Exists(strArtist) Then
               If objDictionary.Item(strArtist) > 2 Then
               Else
                   objDictionary.Item(strArtist) = objDictionary.Item(strArtist) + 1
                   DataList.Add strArtist & vbTab & strTitle & vbTab & arrSongInfo(2) 
                   intTotalTime = intTotalTime + intTime
               End If
            End If
        End If
        If intTotalTime > 4500 Then
            Exit Do
        End If
    End If
Loop

DataList.Sort()

For Each strItem in DataList
    Wscript.Echo strItem
Next

Wscript.Echo

intTotalSeconds = intTotalTime Mod 60
If Len(intTotalSeconds) = 1 Then
    intTotalSeconds = "0" & intTotalSeconds
End If
intTotalMinutes = intTotalTime \ 60

Wscript.Echo "Total music time: " & intTotalMinutes & ":" & intTotalSeconds

OK, let’s take this script apart piece-by-piece and see if we can figure out what makes it tick. To begin with, we define a constant named ForReading and set the value to 1; we’ll need this constant when we open the text file containing our song information (C:\Scripts\SongList.csv). After defining the constant we create instances of both the Scripting.Dictionary and the System.Collections.ArrayList objects. In case you’re wondering, we’re going to randomly select songs from the song list; each time we do so we’ll store a number corresponding to that song in our Dictionary.

As for the System.Collections.ArrayList object, this is actually a .NET Framework class that’s exposed to VBScript. (For more information about .NET Framework objects accessible to VBScript see our Hey, Scripting Guy! column in TechNet Magazine.) Why did we use this rather than VBScript’s built-in array functionality? One reason and one reason only: this event requires us to keep a list of the songs we’re going to burn to the CD, then asks us to display that list in alphabetical order by artist. Neither the Dictionary object nor VBScript’s built-in array functionality give us an easy way to sort data; the System.Collections.ArrayList object does give us an easy way to sort data. That was good enough for us.

Our next step is to set a counter variable named intTotalTime to 0; we’ll use this variable to keep track of the total amount of music time in our list of songs. After initializing the variable we create an instance of the Scripting.FileSystemObject object, then use the OpenTextFile method to open the file C:\Scripts\SongList.csv:

Set objFile = objFSO.OpenTextFile("C:\Scripts\SongList.csv", ForReading)

As soon as the file is open we use the ReadAll method to read in the entire contents of the file, storing that information in a variable named strContents:

strContents = objFile.ReadAll

And then, because we have no further use for SongList.csv, we go ahead and close the file.

By default, the data we read in from the text file is stored as one big, gigantic string value. For a script like this, a single string can be difficult to work with; therefore, we use the Split function to convert the string value to an array named arrContents:

arrContents = Split(strContents, vbCrLf)

That gives us an array in which each element represents the data for a single song. Thus we have array items like these:

Alice Cooper,I'm Eighteen,2:58
Alice Cooper,Be My Lover,3:22
Alice Cooper,School's Out,3:30
The Animals,House of the Rising Sun,4:31
Badfinger,No Matter What,2:59

We’re going to select songs for our CD by randomly choosing items from this array. With that in mind, we initialize two more variables, intLowNumber and intHighNumber:

intLowNumber = 0
intHighNumber = UBound(arrContents) - 1

intLowNumber (with a value of 0) represents the index number of the first item in the array; intHighNumber (with a value equal to the upper bound of the array, minus 1) represents the index number of the last item in the array. In just a moment, we’ll use VBScript’s random number capability to repeatedly generate a random number between those two numbers, inclusively.

In fact, we’re going to do that right now. After setting up a Do loop designed to run forever (or at least for as long as True is equal to True) we generate a random number that corresponds to the index number of one of our array items; all that takes place using these three lines of code:

Do While True
    Randomize
    intNumber = Int((intHighNumber - intLowNumber + 1) * Rnd + intLowNumber)

Let’s assume that intNumber is equal to 17. That’s nice. Now what do we do with that number?

Well, the first thing we’re going to do is use the Exists method to determine whether or not this number already exists in our Dictionary. If the number does exist, that means we’ve already selected this song; therefore, we simply zip back to the top of the loop, generate a new random number, and try again.

Now, what if the Exists method returns False; that is, what if the number isn’t already in the Dictionary? In that case, the song is a candidate for inclusion on our CD. Therefore, the first thing we do is add the song’s index number to the Dictionary:

objDictionary.Add intNumber, intNumber

Next we have to do something with the song. Each item in the array includes the artist, song title, and running time, all in a single, comma-separated string:

The Beatles,Helter Skelter,4:29

In order to get at each of these three items (artist, title, time), we use these two lines of code to retrieve the song information from the array, then split that information into a mini-array named arrSongInfo:

strSong = arrContents(intNumber)
arrSongInfo = Split(strSong, ",")

In the case of the song Helter-Skelter, that means arrSongInfo will contain the following elements:

The Beatles
Helter Skelter
4:29

Next we assign the first item in the array to a variable named strArtist, then assign the second item in the array to a variable named strTitle:

strArtist = arrSongInfo(0)
strTitle = arrSongInfo(1)

That leaves us with one unassigned item: the running time of the song. Needless to say, that’s a bit of a problem; adding 4:29 + 3:47 + 2:59 + 6:11 is tough, even for a script. That’s why our next step is to execute these two lines of code:

arrTime = Split(arrSongInfo(2), ":")
intTime = (60 * arrTime(0)) + arrTime(1)

In the first line we’re using the Split function to create yet another array, this one named arrTime. By splitting on the colon (:) we end up with an array containing these two items:

4
29

Is that useful? As a matter of fact, it is. The 4 represents the number of minutes in the song, and the 29 represents the number of seconds. That means we can multiply 4 by 60, then add 29 to get the total number of seconds in the song:

arrTime = Split(arrSongInfo(2), ":")
intTime = (60 * arrTime(0)) + arrTime(1)

Take it from the Scripting Guys: doing all the arithmetic using seconds rather than a combination of minutes and seconds makes this script much easier to write.

OK, so is this song ready to be added to the CD? Well, maybe. Remember, we have several limits placed on us:

What does that mean? That means that this song has to pass a few tests before we can add it to the CD.

Test No. 1 makes sure that adding the song to the CD will not cause us to exceed the maximum of 4800 seconds. To determine that, we add the running time of the song (intTime) to the variable intTotalTime and then check to see if the sum is greater than 4800:

If (intTotalTime + intTime > 4800) Then

If the total time is greater than 4800 we can’t use this song; therefore, we loop around and try again.

Let’s assume that the total time is less than 4800. Our next task is to check the Dictionary to see if we have an entry for the artist in question:

If Not objDictionary.Exists(strArtist) Then

If the answer is no (False), we use this line of code to add the artist to the Dictionary, using the artist name as the Dictionary key and a 1 (indicating we’ve added 1 song from this artist) as the value:

objDictionary.Add strArtist, "1"

If the artist does exist in the Dictionary we check the value for that Dictionary item to see if it’s greater than 2:

If objDictionary.Item(strArtist) > 2 Then

If it is, we do nothing; that’s because we can have only 2 songs per artist. But what if the value is less than 2? (Which it will be the first time we add an artist; remember, when we add an artist we set the value to 1.) That means we can go ahead and add this song to our CD list. That requires us to execute these three lines of code:

objDictionary.Item(strArtist) = objDictionary.Item(strArtist) + 1
DataList.Add strArtist & vbTab & strTitle & vbTab & arrSongInfo(2) 
intTotalTime = intTotalTime + intTime

In the first line, we’re incrementing the Dictionary entry for this artist by 1; that’s going to make the value equal to 2, and going to ensure that we can add only 1 more song by this artist. (After we add another song, the value will be incremented to 3. Consequently, any attempts to add another song by this artist will fail the test If objDictionary.Item(strArtist) > 2.)

In line 2, we use the Add method to add the song information to the ArrayList; as you can see, we add the artist, title, and total running time in that order, separating each item with a tab (the VBScript constant vbTab):

DataList.Add strArtist & vbTab & strTitle & vbTab & arrSongInfo(2)

Finally, we add the running time of the song (in seconds) to the total running time (the variable intTotalTime). We then check to see if the running time is more than 75 minutes (4500 seconds):

If intTotalTime > 4500 Then

If this is true that means we’ve finished compiling our CD burn list; consequently, we call the Exit Do statement and exit our loop. If not, then we simply go back to the top of the loop, generate a new random number, and repeat the entire process.

When we do exit the loop the first thing we do is call the Sort method to sort our ArrayList:

DataList.Sort()

We then use a simple For Each loop to echo back all the values stored in the ArrayList:

For Each strItem in DataList
    Wscript.Echo strItem
Next

All that’s left now is to echo the total running time for the CD. To that end, the first thing we do is use the Mod operator to divide the total running time by 60, and return only the remainder:

intTotalSeconds = intTotalTime Mod 60

Why the remainder? Well, suppose our total running time is 4517 seconds. 4517 divided by 60 yields a remainder of 17. That means our total running time will feature x number of minutes and – you guessed it – 17 seconds.

Of course, time values are always displayed using two digits to represent the seconds. Because of that, we use this block of code to add a leading zero if our seconds are less than 10:

If Len(intTotalSeconds) = 1 Then
    intTotalSeconds = "0" & intTotalSeconds
End If

Next we use the integer division operator (\) to again divide the total running time by 60; this time, however, we’re going to discard the remainder. When we divide 4517 by 60 we get 75 with a remainder of 17. This line of code tosses out the 17 and assigns the 75 to a variable named intTotalMinutes:

intTotalMinutes = intTotalTime \ 60

Last, but surely not least, we finish the event by echoing back the total music time, like so:

Wscript.Echo "Total music time: " & intTotalMinutes & ":" & intTotalSeconds

The net result should be similar to this:

Alice Cooper    School's Out    3:30
Badfinger       Come and Get It 2:22
Cracker Eurotrash Girl  8:03
Credence Clearwater Revival     Have You Ever Seen the Rain?    2:39
Donovan Catch the Wind  5:02
Donovan Lalena  2:55
George Harrison What is Life?   4:27
Nick Cave and the Bad Seeds     Deanna  3:46
Nirvana All Apologies   3:50
Nirvana The Man Who Sold the World      4:20
Paul Simon      Mother and Child Reunion        2:48
Robert Palmer   Bad Case of Loving You  3:10
Robert Palmer   Simply Irresistible     4:12
The Bangles     Manic Monday    3:06
The Beatles     I Saw Her Standing There        2:55
The Beatles     Obla-di Obla-da 3:11
The Kinks       Lola    4:05
The Kinks       Sunny Afternoon 3:36
The Ramones     I Wanna Be Sedated      2:29
The Rolling Stones      It's Only Rock and Roll 4:10
The Rolling Stones      Let It Bleed    4:15

Total music time: 75:25

Top of page