2008 Winter Scripting Games

Solution to Advanced Windows PowerShell Event 2: Skating on Thin Ice

Windows PowerShell solution to Event 2 in the 2008 Winter Scripting Games.

Solutions are also available for VBScript and Perl.

Skating on Thin Ice

This was kind of an interesting event, at least for the Scripting Guys. Why? Well, in this event you need to calculate a score for each skater; you then need to store the name and score from each skater somewhere in memory, preferably in a manner that makes it easy to pick out the three highest scores.

When we wrote the VBScript solution for this event, we decided to store the scores in a disconnected recordset, a sort of “virtual” database. (“Virtual” in the sense that the information is stored only in memory, and isn’t tied to a physical database file of any kind.) The disconnected recordset worked like a charm in our VBScript script.

At any rate, that got us to wondering: can you use a disconnected recordset in a Windows PowerShell script? That was something we just had to find out.

Oh, right: so can you use a disconnected recordset in a Windows PowerShell script? You bet you can:

$adVarChar = 200
$MaxCharacters = 255
$adFldIsNullable = 32
$adDouble = 5

$DataList = New-Object -com "ADOR.Recordset"
$DataList.Fields.Append("Competitor", $adVarChar, $MaxCharacters, $AdFldIsNullable)
$DataList.Fields.Append("Score", $adDouble, $Null, $AdFldIsNullable)
$DataList.Open()

$arrSkaters = Get-Content "C:\Scripts\Skaters.txt"

foreach ($strSkater in $arrSkaters)
    {
        $intTotal = 0
        $arrScores = $strSkater.Split(",")
        $strName = $arrScores[0]
        $intLength = $arrScores.length
        $arrScoresOnly = $arrScores[1 .. $intLength]
        $arrScoresOnly = ($arrScoresOnly | Sort-Object)

        $intTotal = [int] $arrScoresOnly[1] + $arrScoresOnly[2] + $arrScoresOnly[3] + $arrScoresOnly[4] + $arrScoresOnly[5]
        $dblAverage = ($intTotal / 5)

        $DataList.AddNew()
        $DataList.Fields.Item("Competitor") = $strName
        $DataList.Fields.Item("Score") = $dblAverage
        $DataList.Update()
    }

$DataList.Sort = "Score DESC"

$DataList.MoveFirst();

Write-Host "Gold medal: "  $DataList.Fields.Item("Competitor").Value "," $DataList.Fields.Item("Score").Value
$DataList.MoveNext()

Write-Host "Silver medal: " $DataList.Fields.Item("Competitor").Value "," $DataList.Fields.Item("Score").Value
$DataList.MoveNext()

Write-Host "Bronze medal: " $DataList.Fields.Item("Competitor").Value "," $DataList.Fields.Item("Score").Value

As you can see, we start this script out by assigning values to several different variables:

$adVarChar = 200
$MaxCharacters = 255
$adFldIsNullable = 32
$adDouble = 5

We’ll use these variables when we construct our disconnected recordset (which, for our purposes, is the same thing as a table in a database). The variable $adVarChar will be used to set the datatype for the Competitor fields to variant; $adDouble will be used to set the datatype of the Score field to double. The variable $MaxCharacters limits the number of characters in a field to 255, and the variable $adFldIsNullable enables us to have Null values in a field. (Null values aren’t a concern in this script, but we thought it was worth showing you how to allow Null values.) After assigning values to these variables we then use the New-Object cmdlet and the following line of code to create a disconnected recordset:

$DataList = New-Object -com "ADOR.Recordset"

So far so good, right? Of course, a database table (or a disconnected recordset) isn’t much fun if that table doesn’t include some fields (thus allowing you to actually store data in the thing). With that in mind, we use the following two lines of code to add two fields (Competitor and Score) to our disconnected recordset:

$DataList.Fields.Append("Competitor", $adVarChar, $MaxCharacters, $AdFldIsNullable)
$DataList.Fields.Append("Score", $adDouble, $Null, $AdFldIsNullable)

And once we’ve done that, we can use the Open method to open the recordset:

$DataList.Open()

As you might have guessed, as soon as the recordset is open we’re ready to start adding records to it.

Of course, you can’t add data to a database unless you actually have some data. Consequently, our next task is to use the Get-Content cmdlet to read in the file C:\Scripts\Skaters.txt, storing the contents of that file in an array named $arrSkaters:

$arrSkaters = Get-Content "C:\Scripts\Skaters.txt"

That’s going to give us an array with items like these:

Janko Cajhen,84,80,61,81,71,62,76
Ryan Calafato,59,93,93,80,67,73,95
Iulian Calinov,56,70,76,93,79,83,58
Geert Camelbeke,97,61,55,73,70,92,63

Now it’s time to calculate the scores for each skater.

To do that, we first set up a foreach loop to walk us through each item in the array (that is, through the scores for each skater in the competition). Inside that loop we set the value of a counter variable named $intTotal to 0; we then use this line of code to split the scores for each skater into a mini-array named $arrScores:

$arrScores = $strSkater.Split(",")

For our first competitor, Janko Cajhen, that’s going to result in an array that looks like this:

Janko Cajhen
84
80
61
81
71
62
76

Why did we turn Janko’s scores into an array? Well, as part of the event rules, we need to discard Janko’s high score and his low score. So what are his high and low scores? We have no idea. But by placing these values in an array and then sorting that array, we’ll be able to figure it out, and with very little trouble.

Before we do all that, however, we first grab Janko’s name (item 0 in the array) and store it in a variable named $strName:

$strName = $arrScores[0]

Next, we use the Length property to determine the number of items in $arrScores:

$intLength = $arrScores.length

We then create yet another array, this one consisting of all the items in $arrScores except for item 0, Janko’s name:

$arrScoresOnly = $arrScores[1 .. $intLength]

In other words, $arrScoresOnly consists of the following:

84
80
61
81
71
62
76

Now we’re in business. We first pass $arrScoresOnly to the Sort-Object cmdlet; that’s going to cause our set of scores to be sorted, like so:

61
62
71
76
80
81
84

At this point it’s easy to discard the high and low scores; we simply ignore the first item in the array (item 0) and the last item in the array (item 6). This line of code takes the middle five scores and adds them together:

$intTotal = [int] $arrScoresOnly[1] + $arrScoresOnly[2] + $arrScoresOnly[3] + `
            $arrScoresOnly[4] + $arrScoresOnly[5]

And then, per the event instructions, we determine Janko’s score by dividing this total by 5:

$dblAverage = ($intTotal / 5)

And that’s it; that’s all we have to do for our first competitor. Therefore, we use this block of code to add Janko Cajhen and his score to our disconnected recordset:

$DataList.AddNew()
$DataList.Fields.Item("Competitor") = $strName
$DataList.Fields.Item("Score") = $dblAverage
$DataList.Update()

And then we loop around and repeat the process for the remaining skaters.

When we’re all done we use the following line of code to sort our disconnected recordset in descending order (that is, the highest score comes first; the second-highest score comes next; etc.). After we’ve done that we call the MoveFirst method to move to the first record in the recordset, then use this line of code to echo back our gold medal winner:

Write-Host "Gold medal: "  $DataList.Fields.Item("Competitor").Value "," $DataList.Fields.Item("Score").Value

After that we call the MoveNext method to move to the second recordset and echo back the winner of the silver medal. We then call MoveNext one last time and echo back the winner of the bronze medal. And once we’ve done that we are finished.

Well, with Event 2 anyway.

Incidentally, here are the official results:

Gold medal:  Guido Chuffart , 88.2
Silver medal:  Jack Creasey , 85.8
Bronze medal:  Cecilia Cornejo , 85.4

As for all of you who said Guido Chuffart would never win an international skating competition, well, what do you say now?

Top of page