(Note: These solutions were written for Event 9.)
Beginner Event 9: The javelin throw
For the javelin throw event, you will soar as you write a time logger.
Guest commentator: Salvador Manaois III
Salvador Manaois III is a senior systems engineer at Infineon Technologies Asia Pacific Pte Ltd. He currently holds the MCITP certification for both Enterprise Administrator and Server Administrator. He also holds the MCTS (x5), MCSE, and MCSA certifications. He actively evangelizes the use of automation (through scripts and other technologies) both at work and in the various IT user groups in which he is involved. He is also a moderator for The Official Scripting Guys Forum and maintains the Bytes & Badz blog.
VBScript solution
The Beginner Event 9 javelin throw was made easier by the fact that I am an operations guy. Attending to users, troubleshooting problems, and project-related work are part and parcel of what I do.
The first thing to do is to define the way with which the user inputs the data. I was thinking of popping up input boxes for the user but this approach is not too efficient. I ended up using an argument passed via the command line. Using the command line offers both simplicity and speed.
The next thing to do is to validate the data that is entered via the command line and to store the input into an array. Some data—for example, the Category field—are mapped to a predefined category; the date and time will default to the current date and time. Additionally, if the Category field is greater than 4 (cannot be mapped to the predefined categories), it is reassigned the value of 4 (Others). By defining default values for values, it makes it easier for the busy IT pro to make an entry.
For the BeginnerEvent9Solution.vbs script, I used two subroutines. The first subroutine, ValidateParameter, accepts the command-line argument and is used to validate the command-line values as mentioned earlier. The second subroutine, ShowUsage, is used to provide command-line help.
The complete BeginnerEvent9Solution.vbs script is seen here.
BeginnerEvent9Solution.vbs
Const ForWriting = 2
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Wscript.Arguments.Count = 0 Then
ShowUsage
Else
sParams = Wscript.Arguments(0)
ValidateParameter(sParams)
End If
strNewText = Date
strNewText = Replace(strNewText,"/", "-")
OutputFile = strNewText & "_MyWork.csv"
If objFSO.FileExists(OutputFile) then
Set oOutputFile = objFSO.OpenTextFile(OutputFile, ForAppending)
oOutputFile.Writeline sParams
oOutputFile.Close
Else
Set oOutputFile = objFSO.CreateTextFile(OutputFile, ForWriting)
oOutputFile.Writeline "Category,Time Completed,Time Used (in hours),Description,Status,Remarks"
oOutputFile.Writeline sParams
oOutputFile.Close
End if
Sub ValidateParameter(Params)
arrParams = Split(Params,",")
If Ubound(arrParams)+1 <> 6 then
Wscript.Echo "You have entered an invalid number of parameters. Please try again."
Exit Sub
End if
Select Case arrParams(0)
Case 1 arrParams(0) = "Incident"
Case 2 arrParams(0) = "Change Task"
Case 3 arrParams(0) = "Project"
Case 4 arrParams(0) = "Others"
Case Else
Wscript.Echo "You have entered an invalid category. Selecting ""Others""."
arrParams(0) = "Others"
End Select
if arrParams(1) = nul then
arrParams(1) = Now
end if
sParams = Join(arrParams, ",")
End Sub
Sub ShowUsage
Wscript.Echo "LogMyWork.vbs Category, TimeCompleted, TimeSpent, Description, Status, Remarks"
Wscript.Echo vbReturn
Wscript.Echo "Description : This script is used to log daily activities to an Office Excel file."
Wscript.Echo vbReturn
Wscript.Echo "Parameter List:"
Wscript.Echo vbTab & "Category" & vbTab & "Specifies the category of the task: 1 for Incidents,"
Wscript.Echo vbTab & vbTab & vbTab & "2 for Change Tasks/Requests, 3 for Projects, and"
Wscript.Echo vbTab & vbTab & vbTab & "4 for Other"
Wscript.Echo vbReturn
Wscript.Echo vbTab & "TimeCompleted" & vbTab & "Specifies the date and time the task was completed,"
Wscript.Echo vbTab & vbTab & vbTab & "stopped, or put to pending. Leave this field blank if current"
Wscript.Echo vbTab & vbTab & vbTab & "date and time is to be used."
Wscript.Echo vbReturn
Wscript.Echo vbTab & "TimeSpent" & vbTab & "Specifies the amount of time spent to complete"
Wscript.Echo vbTab & vbTab & vbTab & "the task (in hours)."
Wscript.Echo vbReturn
Wscript.Echo vbTab & "Description" & vbTab & "A brief description of the task done."
Wscript.Echo vbReturn
Wscript.Echo vbTab & "Status" & vbTab & vbTab & "Status of the task (Completed, Pending, Closed)."
Wscript.Echo vbReturn
Wscript.Echo vbTab & "Remarks" & vbTab & vbTab & "Remarks or comments."
Wscript.Echo vbReturn
Wscript.Echo "Example:"
Wscript.Echo vbReturn
Wscript.Echo vbTab & "LogMyWork.vbs " & """1,,1,Install Application A,Completed,None"""
End Sub
When the script is run, the information from the script is stored in a comma-separated values (.CSV) file. Each new entry is appended to previous entries in the file. The generated file name uses the current date as part of the file name (for example, 18-5-2009_MyWork.csv).
I would love to port this script to an ASP application, expand the CTI field to cover almost all IT-related stuff, and store the data in a back-end database. With this, I should be able to generate more comprehensive, management-friendly reports to better understand the strengths, weaknesses, and needs of the IT services.
When you open the CSV file in Microsoft Excel, a spreadsheet appears that is similar to this one:
Guest commentator: Daniele Muscetta
Daniele Muscetta’s journey with computers and software started when he began programming on a Commodore 64 at the age of ten. Daniele preferred to write programs on his Commodore 64 rather than playing games with it like the other kids did. Today, Daniele works at Microsoft Italy as a premier field engineer who specializes in both System Center Operations Manager and scripting technologies such as VBScript and Windows PowerShell. Daniele maintains a personal blog on his Web site.
Windows PowerShell solution
The most basic time logger would be a single command that writes the current date and time, and a description or name of the activity being logged to a text file. There are multiple ways to log a datetime, and I tend to like a method I saw mentioned on the Windows PowerShell team blog. One advantage of this method is it can be changed to some other format.
But the biggest challenge with the Beginner Event 9 scenario is the usability of such an application. I came up with two questions:
· How do I tell the script I am starting to do something and supply the name of the new task? I could use command-line parameters or the Read-Host cmdlet. Both options seemed ugly to me. The script is not one that processes data in log files—it is a user application.
· How do I tell the script when I am actually done doing something? I could end script execution with CTRL+C. I could require the user to enter another more data into a Read-Host cmdlet. But what if someone makes a mistake and presses the wrong key?
In the end I decided against all these. Thought it is true this is a script, this script challenge looked to me more like an application!
The basic task of writing time-stamped entries is the easy part of the problem. It is the usability of the tool that is the most important part of the task. At the same time I need to keep it simple.
I then thought of breaking loose from the command-line interface (CLI), and decided to use the power of the .NET Framework classes from Windows PowerShell. This is an approach I also used in other situations. It is not too difficult to build a simple Windows Form.
When BasicEvent9Solution.ps1 script is run, a text box appears:
It has a simple text box where you type the task name and then click Start. At this point, the script will write an entry in the log file saying that we started the task and initialize a System.Diagnostics.StopWatch .NET Framework object (I got the basic idea from a posting made by Thomas Lee who is a moderator for the Official Scripting Guys forum and a fellow 2009 Summer Scripting Games commentator).
At this point, according to my use case scenario, you minimize the application and keep doing your work.
When you are done with your work, bring the form back and click Stop. At that point the timer will stop, and another entry will be written in the log file saying that you have done that task and how long it took you to complete it. A sample of the output from the logfile.txt file is seen here:
Of course the BeginnerEvent9Solution.ps1 script can be improved in a number of ways. Some of the things I have thought about doing to the script include the following:
· Display the actual timer progress in the GUI while it runs. In this way, the script could double as a stop watch.
· Use a better storage format. Instead of using a simple text file I could use a CSV file, an Office Excel spreadsheet, or even a database.
· Use multiple timers and allowing for “multitasking.” In this approach, which might be useful in a consulting type of environment, you declare to the tool you are actually performing multiple tasks at once.
The BeginnerEvent9Solution.ps1 script is commented, so it should be easy to read. As with most scripts that create a graphical interface, a large part of the code generates the Form and the entire GUI, while very little of it is the actual “logging engine.” The compete BeginnerEvent9Solution.ps1 script is seen here.
BeginnerEvent9Solution.ps1
#this is our logfile
$logfile = "c:\scripts\logfile.txt"
#StopWatch Object we use to track elapsed time
$sw = new-object System.Diagnostics.StopWatch
$sw.Start()
Function CreateMainGUI
{
# Loads useful .net Assemblies, required to create the GUI....
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
[void][reflection.assembly]::LoadWithPartialName("System.Drawing")
#creates form and controls objects
$form = New-Object System.Windows.Forms.Form
$buttonStart = New-Object System.Windows.Forms.Button
$buttonStop = New-Object System.Windows.Forms.Button
$textBoxTask = new-object System.Windows.Forms.TextBox
#pauses rendering until later
$form.SuspendLayout();
# textBoxTask
$textBoxTask.Location = new-object System.Drawing.Point(10, 10);
$textBoxTask.Name = "textBoxTask";
$textBoxTask.Size = new-object System.Drawing.Size(350, 25);
$textBoxTask.TabIndex = 7;
$textBoxTask.Text = "Enter Task Name";
# buttonStart
$buttonStart.Location = new-object System.Drawing.Point(370, 10);
$buttonStart.Name = "buttonStart";
$buttonStart.Size = new-object System.Drawing.Size(50, 20);
$buttonStart.TabIndex = 4;
$buttonStart.Text = "Start";
$buttonStart.UseVisualStyleBackColor = $true;
$buttonStart.add_click({
$taskname = $textBoxTask.Text
StartLogTask $taskname
})
# buttonStop
$buttonStop.Location = new-object System.Drawing.Point(430, 10);
$buttonStop.Name = "buttonStop";
$buttonStop.Size = new-object System.Drawing.Size(50, 20);
$buttonStop.TabIndex = 4;
$buttonStop.Text = "Stop";
$buttonStop.UseVisualStyleBackColor = $true;
$buttonStop.Enabled = $false #this will start disabled - we want to START a task first!
$buttonStop.add_click({
StopLogTask $taskname
})
# Form1
$form.ClientSize = new-object System.Drawing.Size(500, 50);
$form.FormBorderStyle=[System.Windows.Forms.FormBorderStyle]::FixedSingle
$form.Controls.Add($textBoxTask);
$form.Controls.Add($buttonStart);
$form.Controls.Add($buttonStop);
$form.Name = "Form1";
$form.Text = "Time Logging Script";
$form.ResumeLayout($false);
$form.PerformLayout();
$form.Load
$form.ShowDialog()
$form.Add_Shown({$form.Activate()})
}
Function StartLogTask([string]$taskname)
{
#reset the stopwatch in case it was previously running
$sw.Reset()
#start the stopwatch again
$sw.Start()
#logs the entry
"$(get-date -f o) Started Working on $taskname">>$logfile
#switches the buttons so that START is now disabled and STOP gets enabled
$buttonStart.Enabled = $false
$buttonStop.Enabled = $true
}
Function StopLogTask([string]$taskname)
{
#gets the stopwatch's elapsed time in milliseconds
$ts = $sw.Elapsed
#converts those milliseconds to a nicely (?) formatted string
$ElapsedTime = [system.String]::Format("{0:00}:{1:00}:{2:00}.{3:00}", $ts.Hours, $ts.Minutes, $ts.Seconds, $ts.Milliseconds / 10)
#logs the entry
"$(get-date -f o) Stopped Working on $taskname - Time worked on this was: $ElapsedTime">>$logfile
#switches the buttons so that STOP is now disabled and START is enabled
$buttonStop.Enabled = $false
$buttonStart.Enabled = $true
}
# *** Entry Point to the Script ***
CreateMainGUI
Advanced Event 9: The javelin throw
In the javelin throw event, you will throw your heart into your work as you attempt to sort a tab-delimited text file based upon a particular column.
Guest commentator: Alex Angelopoulos
Alex K. Angelopoulos is an IT consultant, an MCSE, and a contributing editor for Windows IT Pro magazine. An avid scripter, Alex writes about Windows task automation using Windows PowerShell and the Windows Script Host.
VBScript solution
You can minimize the effort for the javelin throw event if you realize that the data set is already designed for consumption from Microsoft Office Excel. If you choose to use Excel to solve the problem, you avoid low-level coding in VBScript to handle the rankings.
Even using Excel, the problem isn't trivial, but you can apply most of the coding techniques to any problem where you may want to leverage Excel as a tool.
Although I didn't start by declaring constants (I added them to the script after I had the prototype working), the script starts there because it's a convenient location to show all the values that I'll hang onto (and it makes it simpler to find them if you ever need to modify the constants). So the first few lines, which won't mean a lot until you work through the script, look like this:
Option Explicit
Const xlMinimized = 2, xlYes = 1, xlDescending = 2
Const ShortestAutoQualifyThrow = 82, MinimumQualifierCount = 12
Const TabDelimiter = 1
const sourcefile = "Javelin Throw Data.txt"
const targetfile = "Javelin Throw Results.txt"
Because I use Excel as the engine for the javelin throw calculations, the next step is to set up a reference to Excel. This is seen here:
Dim xl
Set xl = CreateObject("Excel.Application")
The next step is some defensive scripting to deal with the fact that Excel is first and foremost an interactive tool, not a script component.
One of the side effects of its design is that Excel will often prompt you about particular actions, causing the script to pause until you respond. We can suppress this behavior by telling Excel that we don't want special alerts shown:
xl.DisplayAlerts = False
Furthermore, as an actual application, Excel doesn't necessarily quit when the script exits. If the script crashes or if a buggy add-in keeps a reference to Excel, Excel will continue to run. Although we can't prevent all possible problems, we can choose to make Excel visible while the script runs; then if it doesn't shut down, the user can see it in the Taskbar and shut it down manually. If you set Excel's window state to minimized, this automatically makes it visible as well as running it in the Taskbar where it won't interfere with other tasks:
xl.WindowState = xlMinimized
At this point, we're ready to open the data file. There are two new problems to handle here, however. We need to find the file and we need to tell Excel how to interpret the contents.
The script assumes that Javelin Throw Data.txt is in the same folder as the script. We can't just specify the bare name of the data file, however. Excel starts out with a different home folder, depending on the version of Excel and Windows, so we need to get the complete path to the data file. I use the Scripting.FileSystemObject object to expand the bare file name to the complete path like this:
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim sourcepath
sourcepath = fso.GetFile(sourcefile).Path
Next, we need to tell Excel that the file is tab-delimited data. Excel's Workbooks collection has an Open method that allows you to specify a large number of parameters—so many that it's difficult to work with at times. Fortunately, you can simply omit parameters that you want to assume default values. All we're concerned with is telling Excel the path to the file to open (the first parameter) and what kind of delimiter to use for columns (the fourth parameter for the Open method). Excel doesn't use a built-in constant for the delimiter type, but the Excel VBA documentation does discuss the numbers used to represent delimiter types. A value of 1 tells Excel that columns are delimited with tabs; for reference, 2 means the delimiters are commas; 3 means spaces; and 4 means semicolons. So I wouldn't have a meaningless number floating around in the Open method, I defined a constant called TabDelimiter at the top of the script and use it with Open:
Dim workbook
Set workbook = xl.Workbooks.Open(sourcepath, , , TabDelimiter)
We now have the data file open and can begin to work with the contents. Technically we'll be operating on a worksheet, not a workbook. With a tab-delimited text file, they amount to the same thing, because there's only one table of data in the file, but we need to explicitly specify the worksheet to Excel, like this:
Dim sheet
Set sheet = workbook.ActiveSheet
Our first task is to find the best throw for each competitor. This takes some thinking about how the data is structured. For simplicity, I assumed that the columns are always guaranteed to be in the order shown in the sample data. The Best Throw is then always Column H in the Excel worksheet. What reasonably might vary is the number of competitors. Fortunately, worksheets have a UsedRange object, which contains a Rows object, and Rows has a Count property. Because the top row of the worksheet contains the headers, we only want to look at rows 2 through sheet.UsedRange.Rows.Count. Excel uses the column letter and the row number for specifying ends of a range, so we can set our range this way:
Dim range
Set range = sheet.Range("H2","H" & sheet.UsedRange.Rows.Count)
After all this effort, calculating the best throw is a one-liner. Excel has a built-in Max function that will select the highest value in a range of cells, and it automatically treats an "x" entry as 0:
range.FormulaR1C1 = "=MAX(RC[-3]:RC[-1]"
Sorting the data is another single line:
sheet.usedRange.Sort range, xlDescending, , , , , , xlYes
The Sort method is of course yet another Excel function with limitless parameters. The 8th parameter with the value xlYes simply tells Excel that the first row is a set of headers and should not be included in the sorting.
The next step is to make the output data look more like input data. First, if a contestant had no qualifying throws, we change the Best Throw value from 0 to "x" so it looks like the input data:
Dim cell
for each cell in range.Cells
if cell.Value = 0 Then cell.value = "x"
next
Finally, we perform the rankings, a task simplified because competitors are now sorted in order of their best throw:
Dim i
for i = 2 to sheet.UsedRange.Rows.Count
set range = sheet.Range("I" & i)
dim bestThrow: bestThrow = sheet.Range("H" & i).Value
dim ranking: ranking = i - 1
if bestThrow = "x" Then
range.Value = "x"
elseif bestThrow => ShortestAutoQualifyThrow Then
range.Value = ranking
elseif ranking <= MinimumQualifierCount Then
range.Value = ranking
else
range.Value = "x"
end if
next
And finally, we save the target file, close the workbook, and quit Excel:
xl.ActiveWorkbook.SaveAs targetfile
xl.ActiveWorkbook.Close
xl.Quit
The completed AdvancedEvent9.vbs script is shown here.
AdvancedEvent9.vbs
Option Explicit
Const xlMinimized = 2, xlYes = 1, xlDescending = 2
Const ShortestAutoQualifyThrow = 82, MinimumQualifierCount = 12
Const TabDelimiter = 1
const sourcefile = "Javelin Throw Data.txt"
const targetfile = "Javelin Throw Results.txt"
dim xl
Set xl = CreateObject("Excel.Application")
' Office Excel has a variety of dialogs that appear to stop you from
' doing things that it thinks may be harmful: for example, saving
' a workbook as a text file, particularly when it contains formulas.
' We can suppress this using DisplayAlerts.
xl.DisplayAlerts = False
' It's possible to run Excel hidden, but if there are issues
' due to either script errors or buggy add-ins that don't let
' Excel unload cleanly, it will continue to run invisibly.
' To make this solvable without taking over the desktop, we
' set Excel's windowstate to minimized. This also automatically
' makes Excel visible.
xl.WindowState = xlMinimized
' We need to get the COMPLETE PATH to the source data file.
' Although the script knows where it is running from,
' Excel is a separate process and has its own default startup folder.
' If we pass it a bare file name, Excel will probably be looking for
' the file in the wrong folder; so expand sourcefile to a sourcepath.
' When we save the modified file, we don't have anything to worry about;
' Excel will assume a relative path is relative to the already-opened
' source file.
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim sourcepath
sourcepath = fso.GetFile(sourcefile).Path
Dim workbook
Set workbook = xl.Workbooks.Open(sourcepath, , , TabDelimiter)
Dim sheet
Set sheet = workbook.ActiveSheet
' Now select the range for the best throw column and calculate the values.
' Excel handily treats X as a non-value.
Dim range
Set range = sheet.Range("H2","H" & sheet.UsedRange.Rows.Count)
range.FormulaR1C1 = "=MAX(RC[-3]:RC[-1]"
' Now we sort the sheet based on these values and then change 0 to "x"
' This marks contestants who had no qualifying throws
sheet.usedRange.Sort range, xlDescending, , , , , , xlYes
Dim cell
for each cell in range.Cells
if cell.Value = 0 Then cell.value = "x"
next
Dim i
for i = 2 to sheet.UsedRange.Rows.Count
set range = sheet.Range("I" & i)
dim bestThrow: bestThrow = sheet.Range("H" & i).Value
dim ranking: ranking = i - 1
if bestThrow = "x" Then
range.Value = "x"
elseif bestThrow => ShortestAutoQualifyThrow Then
range.Value = ranking
elseif ranking <= MinimumQualifierCount Then
range.Value = ranking
else
range.Value = "x"
end if
next
xl.ActiveWorkbook.SaveAs targetfile
xl.ActiveWorkbook.Close
xl.Quit
Guest commentator: Bruce Payette
Bruce Payette is a principal developer on the Windows PowerShell team. He is one of the founding members of the team, co-designer of the Windows PowerShell language and the principal author of the Windows PowerShell language implementation. Bruce is the author of one of the top-selling Windows PowerShell books on Amazon.com, Windows PowerShell in Action, from Manning Publications.
Windows PowerShell solution
The solution to this problem is quite straightforward. Step one is to load the data into memory for processing. Because the data file is a simple CSV file with fields separated by tabs, we can load it in Windows PowerShell 2.0 by doing the following:
$data = Import-CSV -Delimiter "`t" '.\Javelin Throw Data.txt'
The next step is to calculate the result field for each athlete. This is somewhat complicated by two things: the data in the table is stored as strings and some of these strings aren't numbers because of the “x” fields for disqualified throws. There are two approaches we can use to calculate this. The first is to look through each throw and pick the largest one. We can deal with both of the type conversion problems by using the -as operator to convert the results to a floating point value. If the argument to -as is a string that looks like a number, it will convert the string to a number. If the argument is a string that doesn't look like a number, it will return $null. This is fine because when $null is used in numeric conversions it will be treated as 0. To loop through the throws, we could simply list each one. However we can also use string expansion and the fact that the right side of the '.' operator in PowerShell need not be a constant value allowing us to do the following:
foreach ($a in $data) # for each athlete
{
$a.Result = 1..3 | # find the longest throw...
foreach `
{$max=0} `
{
$current = $a."throw $_" -as [double]
if ($current -gt $max) {$max = $current}
} `
{$max}
}
The result of the selection is assigned to the Result field in the data row for that athlete. We then sort the data into descending order and add descending ranking number for each athlete:
$data = $data | sort -desc Result
$rank = 1
$data | foreach { $_.Rank = $rank++ }
Finally, we need to select the top twelve qualifying athletes, or the top twelve if there aren't enough that meet the minimum score. We can do this with the where cmdlet:
$count = 0
$nextRound = $data | where { $count++ -lt $minimumNumber -or
$_.result -ge $minimumAcceptableScore }
The final step is to write the new data out to a file using Export-CSV. Again we'll use tabs to separate the fields:
$nextRound | Export-Csv -Path $outputFile -Delimiter "`t" -NoTypeInformation
This complete solution, AdvancedEvent9Solution.ps1, is seen here.
AdvancedEvent9Solution.ps1
#
# Solution 1 - straightforward incremental steps
#
$inputFile = '.\Javelin Throw Data.txt'
$outputFile = "Next Round.txt"
$minimumAcceptableScore = 82.5
$minimumNumber = 12
#
# Load the csv file into a variable. The columns are separated by a tab
#
$data = Import-Csv -Delimiter "`t" $inputFile
#
# Loop through the data, calculating and updating the
# result property for each athlete. We'll treat “x”
# as zero when picking the best result
#
foreach ($a in $data) <# for each athlete #>
{
$a.Result = 1..3 | # find the longest throw...
foreach `
{$max=0} `
{
$current = $a."throw $_" -as [double]
if ($current -gt $max) {$max = $current}
} `
{$max}
}
# Now sort in descending order best to worst, using the result field
$data = $data | sort -desc Result
# Fill in the rank field
$rank = 1
$data | foreach { $_.Rank = $rank++ }
#
# Select the set that will go on to the next round. We want everyone
# who meets the minimum score or the top 12 if there aren't enough
# people meeting the minimum.
#
$count = 0
$nextRound = $data | where { $count++ -lt $minimumNumber -or $_.result -ge $minimumAcceptableScore }
#
# Write out the data for the next round
#
$nextRound | Export-Csv -Path $outputFile -Delimiter "`t" -NoTypeInformation
Earlier we mentioned that there were two approaches to calculating the best result for each athlete. The second approach doesn't use a loop. Instead we can use the Windows PowerShell operators to perform each step in the conversion:
$a.Result =
@(1 .. 3 | foreach { $a."throw $_" }) <# get the three throws #> `
-replace "x","0" <# replace the 'x's with 0 #> `
-as [double[]] | <# convert the results to numbers #>
sort -desc | <# sort in descending order #>
select -first 1 <# take the first (best) value #>
With this approach, we extract each result as in the previous solution, use the -replace operator to remove the x’s, convert the collection to an array of double, sort that array in descending order, and then select the first (largest) result. The result of the solution remains the same. The complete solution using this technique is shown in AdvancedEvent9Solution2.ps1.
AdvancedEvent9Solution2.ps1
#
# Solution 1 - straightforward incremental steps
#
$inputFile = '.\Javelin Throw Data.txt'
$outputFile = "Next Round.txt"
$minimumAcceptableScore = 82.5
$minimumNumber = 12
#
# Load the csv file into a variable. The columns are separated by a tab
#
$data = Import-Csv -Delimiter "`t" $inputFile
#
# Loop through the data, calculating and updating the
# result property for each athlete. We'll treat “x”
# as zero when picking the best result
#
foreach ($a in $data) <# for each athlete #>
{
$a.Result = 1..3 | # find the longest throw...
foreach `
{$max=0} `
{
$current = $a."throw $_" -as [double]
if ($current -gt $max) {$max = $current}
} `
{$max}
}
# Now sort in descending order best to worst, using the result field
$data = $data | sort -desc Result
# Fill in the rank field
$rank = 1
$data | foreach { $_.Rank = $rank++ }
#
# Select the set that will go on to the next round. We want everyone
# who meets the minimum score or the top 12 if there aren't enough
# people who meet the minimum.
#
$count = 0
$nextRound = $data | where { $count++ -lt $minimumNumber -or $_.result -ge $minimumAcceptableScore }
#
# Write out the data for the next round
#
$nextRound | Export-Csv -Path $outputFile -Delimiter "`t" -NoTypeInformation
The final solution, AdvancedEvent9Solution3.ps1, is the same as the second solution, removing all of the intermediate variables and composing the solution as a single, top-level pipeline. This is included for academic purposes only and is not recommended if you want other people to be able to maintain your code. The complete AdvancedEvent9Solution3.ps1 script is seen here.
AdvancedEvent9Solution3.ps1
#
# Solution 3 - solution 2, but done as a single pipeline
#
$inputFile = '.\Javelin Throw Data.txt'
$outputFile = "Next Round.txt"
$minimumAcceptableScore = 82.5
$minimumNumber = 12
#
# Load the csv file into a variable. The columns are separated by a tab
#
import-csv -delimiter "`t" $inputFile |
foreach {
$a = $_
$a.Result =
@(1 .. 3 | foreach { $a."throw $_" }) <# get the three throws #> `
-replace "x","0" <# replace the x’s with 0 #> `
-as [double[]] | <# convert the results to numbers #>
sort -desc | <# sort in descending order #>
select -first 1
$a <# emit the updated object #> } |
sort -desc Result | <# sort in descending order by result #>
foreach {$rank=0; $count=0} { $_.Rank = $rank++; $_ } | <#Calculate the rank field #>
where { $count++ -lt $minimumNumber -or $_.result -ge $minimumAcceptableScore } |
Export-Csv -Path $outputFile -Delimiter "`t" -NoTypeInformation
Our most hardy thanks go out to Salvador, Daniele, Alex, and Bruce for their excellent commentaries today. Once again we have gained some tremendous insights into the mysteries of the script-writing process. Join us tomorrow as we reveal the last of our guest commentators and the last set of expert solutions. Though we are not saying we saved the best for last, join us tomorrow to find out. If you run into any snags while working on today's events, remember that you can post questions to the 2009 Summer Scripting Games forum. To keep up with all the latest information, follow us on Twitter. Until tomorrow, peace!
Ed Wilson and Craig Liebendorfer, Scripting Guys