This continues my series of solution posts for the 2008 Scripting Games with my solution for the Advanced Event 2 which was about scoring a skating contest. The idea was to take a CSV file with skater names and scores (7 per skater), and calculate the average (leaving off the highest and lowest) score for each and award medals to the top three scorers. The Scripting Guys solution for this problem was kind-of interesting, because they used a DataSet to do it, but it’s truly an abomination which uses COM interop and a bunch of mysterious magic numbers like $adFldIsNullable = 32 and $adDouble = 5 … whatever those are.
Their script won’t even run on my computer. they didn’t specify a version on their COM object, and the ADOR.Recordset that I get on my Vista computer throws an error on line 7 of their script: Cannot find an overload for "Append" and the argument count: "4" and once I add a 5th argument to that, it throws dozens more like until I changed (among other things) their line $DataList.Fields.Item("Competitor") = $strName to $DataList.Fields.Item("Competitor").Value = $strName to get it to run. Anyway. For the record: don’t use COM stuff when you could use .Net stuff, because it adds measurably to the run time, and makes you do crazy things like defining a bunch of numeric constants. .
Anyway, before I show you my solution, let me show you how to load this data into a .Net DataTable. Even though it’s obviously not necessary for this script, but it’s an interesting PowerShell script, so here’s their solution rewritten with the System.Data.DataTable:
$DataList = New-Object Data.DataTable "Skaters"
$null = $DataList.Columns.Add("Name",[string])
$null = $DataList.Columns.Add("Score",[double])
foreach ($strSkater in (Get-Content "C:\Scripts\Skaters.txt"))
{
$arrScores = $strSkater.Split(",")
$dblAverage = (( $arrScores[1..7] | Sort )[1..5] | measure-object -average).Average
$null = $DataList.LoadDataRow( @($arrScores[0],$dblAverage), $true )
}
$winners = $DataList.Select("","Score Desc")[0..2]
"Gold Medal: {0}, {1}" -f $winners[0].Name, $winners[0].Score
"Silver Medal: {0}, {1}" -f $winners[1].Name, $winners[1].Score
"Bronze Medal: {0}, {1}" -f $winners[2].Name, $winners[2].Score
That’s much simpler and clearer than the original script (and even, I dare say, than the alternate solution from Ben Pearce) partly because it doesn’t need all the predeclared constants and the cursor stuff like Open() and MoveNext() ... but it’s nowhere near an ideal PowerShell solution. That would look something like this:
$skaters = gc C:\Scripts\skaters.txt |
Select @{n="Score";e={(( $_.split(",")[1..7] | Sort )[1..5] | measure-object -average).Average}},
@{n="Name";e={$_.split(",")[0]}} |
Sort Score -desc | Select -first 3
"Gold Medal: {0}, {1}" -f $skaters[0].Name, $skaters[0].Score
"Silver Medal: {0}, {1}" -f $skaters[1].Name, $skaters[1].Score
"Bronze Medal: {0}, {1}" -f $skaters[2].Name, $skaters[2].Score
That’s basically a 2 line script which I reformatted to make readable. Let’s go through it slowly in case you need some help understanding it. Obviously the very first line is assigning the output of the command to the $skaters variable, and using Get-Content to read in the CSV file with the scores and pipe it to the next line. Remember, Get-Content reads in a single line at a time and passes that down the pipeline, so the next command in the pipeline will be dealing with a single line at a time.
The lines in the skaters.txt file consist of a skater’s name followed by 7 numbers (the scores that skater received). So on the next line we will create a custom PowerShell object for each skater … and use Select-Object to get the Name and Score.
$skaters = gc C:\Scripts\skaters.txt |
Select @{n="Name";e={$_.split(",")[0]}},
@{n="Score";e={(( $_.split(",")[1..7] | Sort )[1..5] | measure-object -average).Average}} |
We’re taking advantage of the Select-Object’s ability to use hash tables to define custom properties using script blocks. The full syntax for that is {Name="Property Name"; Expression={ ... }} where the Expression property can use the built in $_ variable which represents the pipeline object (in this case, the full line). As you can see, you can use just n and e instead of the full names for the hashtable. This part of the script might be better in a loop as above, so you could do the string split just once and assign it to a variable, but keeping it in the pipeline is ultimately smoother.
When we split the line on the commas, the first value (at index 0) is the skaters Name: $_.split(",")[0] and everything after that are the scores. Of course, we want to remove the highest and lowest values, so we use PowerShell’s array indexing to trim them off: $_.split(",")[1..7] will return all the scores, and we pipe that through sort, and then index just [1..5] of the sorted numbers. Then we pipe that through the Measure-Object cmdlet to calculate the Average. Then, we wrap the whole thing in parentheses so we can actually get the Average property of the GenericMeasureInfo object that Measure-Object outputs … which of course is a double number which is assigned to the Score property.
Sort Score -desc | Select -first 3
Then, we just pipe these custom objects into a sort by the score property, and select the highest three and output them (into that variable I mentioned earlier). It’s important to realize that even though I’ve wrapped the lines in the script on the pipe characters | so they would fit in the web page, PowerShell thinks of them as a single command line — which is why the output of the Select at the end ends up in the $skaters variable.
But of course, the contest called for us to specify the medals as part of the output. That’s pretty simple, and we can use the string formatting built into PowerShell … here’s what it looked like originally (as a single line command, but wrapped for the sake of the web):
"Gold Medal: {0}, {1}`nSilver Medal: {2}, {3}`nBronze Medal: {4}, {5}" -f `
$skaters[0].Name, $skaters[0].Score, $skaters[1].Name, `
$skaters[1].Score, $skaters[2].Name, $skaters[2].Score
So. That’s pretty much it.
[...] solution for Event 9 in the beginners competition, as Jaykul already noted here : 2008 Scripting Games – Solution for Advanced Event 2 , when working with ADO in PowerShell it’s much better to use .NET to connect to a database as the [...]