This blog has moved to http://ThePowerShellGuy.com
Greetings /\/\o\/\/
The Final day of the scripting games,
Anwers to
Event 7 and
Event 8 are posted on the
Scripting Games Home. (where you can also find the needed files and original events)
And I will post the MSH versions I made as aswers here, lets start with event 7.
Event 7In my Vbscript answer I used this way to translate the time,
Total = (TimeValue("00:" & time1) + TimeValue("00:" & time2))for the rest my script was basicly the same.
with the same construction in MSH I came to this one-liner for the 10 points answer :
# 10 Points one-liner unsorted
gc C:\Scripts\Event_7.txt | foreach {$l = $_.split(",");"$($l[0];(([datetime]`"00:$($l[1])`").Add([timespan]`"00:$($l[2])`")).tostring('m:ss'))"}
but to get the full 15 points you need to sort the list, how to do that ?
now does MSH have a sort (sort-object) but its not very usefull on this output.
the trick here is to use select-object , with select I came to the following onliner :
# using select to be able to sort to get the 15 points
gc C:\Scripts\Event_7.txt | select {$_.split(",")[0]},@{E={([datetime]"00:$($_.split(',')[1])").add([timespan]"00:$($_.split(',')[2])").tostring('m:ss')};N="t"} | sort -desc t | ft -h
(as no columns where asked, I just renamed the last to make the sort command not to long ;-))but to get a nice list and because this is a bit cryptic, I also made this version,
# Write it out a litle bit more and rename the first column
# so we can show the headers
get-content C:\Scripts\Event_7.txt | select @{Expression={$_.split(",")[0]};Name="Competitor"},
@{Expression={
$Time1 = ([datetime]"00:$($_.split(',')[1])")
$Time2 = ([timespan]"00:$($_.split(',')[2])")
$Time1.add($Time2).tostring('m:ss')
};Name="Time"} | sort -property Time -descending :$($_.split(',')[1])").add([timespan]"00:$($_.split(',')[2])").tostring('m:ss')};N="t"} | sort -desc t | ft -h
*Note* technicaly this is still a one-liner I just entered some linebreaks and spaces to format it a bit.this will add meaningfull descriptions to the table generated.
the naming and formating af data in select is not yet in the Help but tor more information see also the good explanation in
::MSH:: from DontBotherMeWith Spam about the first MP3 entry on my blog where I use this also.
for another even more powerfull way to do this kind of things (and more) in MSH (with add-member) see the second post about MP's
Some fun With Monads Add-Member, MP3 files and COMBut time to go on to the last event
Event 8For this I also made 2 solutions,
The first one is basicly the same as the Vbscript version.
- It connects to access,
- It does transform the data in the SQL query
- It outputs the data.
# Open connection to the Access DataBase
$mdb = "c:\scripts\event_8.mdb"
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$mdb"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
# do processing in SQL Query
$SQL = "SELECT MedalWinners.Country,"
$SQL += " Sum(MedalWinners.Gold) AS Gold,"
$SQL += " Sum(MedalWinners.Silver) AS Silver,"
$SQL += " Sum(MedalWinners.Bronze) AS Bronze,"
$SQL += " Sum([Gold]+[silver]+[bronze]) AS Total"
$SQL += " FROM MedalWinners GROUP BY MedalWinners.Country"
$SQL += " ORDER BY Sum([Gold]+[silver]+[bronze]) DESC;"
$cmd = new-object System.Data.OleDb.OleDbCommand($SQL,$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt | ft -auto
You can see the formatting part is a bit easier (I used the spaces function in my VbScript answer), for the rest is basicly the same.
also I did the datebase connection already for Excel, see
Using ADO to get data from Excel to Dataset in Monad. and SQL see
Getting and Working With SQL Server data in MonadAnd the SQL query I did just create in Access, only I changed the Query by removing the Sum that Access does append automatic so I do not have to make the headers myself as the scripting guys did ;-)
So to make it a bit more an event I made a second version that just gets the original table and does the processing of the data on the MSH side.
this second version looks like this :
# Alternative way, do the processing on MSH side
$mdb = "c:\scripts\event_8.mdb"
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$mdb"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand("select * from medalwinners",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
$da.fill($dt)
$Total = $dt.Columns.add("Total",[int])
$Total.expression = "(Gold+silver+bronze)"
$dt | select -unique country | select country,
@{e={$dt.compute("Sum(Bronze)","Country = '$($_.country)'")};n='Bronze'},
@{e={$dt.compute("Sum(Silver)","Country = '$($_.country)'")};n='Silver'},
@{e={$dt.compute("Sum(Gold)","Country = '$($_.country)'")};n='Gold'},
@{e={$dt.compute("Sum(Total)","Country = '$($_.country)'")};n='Total'} |
sort Total -desc | ft -auto
You can see that it just append a column for the type Int, and adds a expression to it.
Then I do a select -unique to get only the countries
while I loop through them I use the Compute Method of the datatable, that takes a Expression and a filter as arguments, to sum the medals for every country , like in the first sample I did this all within a Select statement inside calculated properties.
and then as promised, as a bonus, the alternative ending for
The Scripting Games Event 6 in MSH as you can guess by now it uses SELECT ;-)
# Format and output results
$words | select @{e={$_};n='Word'},@{e={$found -contains $_};n='Found'} | sort found -desc | ft -auto
that was it for this final day, hoped you liked this series as I did doing the original Games and the MSH side-o-lypics series and thanks from here to the scripting guys for aranging the Scripting games, and hope there will be some MSH events in it next year.
Enjoy,
Greetings /\/\o\/\/
PS you can find the needed files and orignal events from the main pa
Tags : Monad msh