Background
When I’m not at work, I spend most of my days at the baseball field with my five kids. Like many families, my children participate in a local baseball organization. Most seasons I try to contribute to the local baseball organization by coaching a team. This season we had record highs in registration. The record number of families that registered was great for the league but, it uncovered some cracks within our organization. One of the cracks that surfaced was, the process of ordering jerseys. Up to this season, the ordering of jerseys fell on the shoulders of one or two brave volunteers that had to build a jersey ordering spreadsheet based on a registration CSV file. The volunteers would spend hours counting jersey and pants sizes, tallying up the total sizes for each teams, and totaling the number of additional hats and shirts ordered by adults.
CSV and Excel Spreadsheets
To fix the jersey ordering problem, I utilized PowerShell to read the information out of the registration CSV file. The data from the CSV file was then added to an already existing jersey ordering Excel spreadsheet.
In many cases, including this one, only selective columns are needed from a CSV file. Once you know the required columns, it is also important e to understand the values within each row. Some values are dynamic, like name and age. Other columns have values that are static like jersey size or pants size.
Before diving into the solution let’s first look at the registration file. The registration file is populated with a bunch of information, most of which is not needed for jersey registration.
Note: There are more columns of data then what is shown in the image below.
The required columns in the registration CSV file are, First Name, Last Name, Jersey Size, PantsSize, Adult Hat, Adult t-shirt1, Adult t-shirt2, Team Name, and Team Name. The rest of the columns can be ignored.
Now that we understand that data we are grabbing from the registration file, let’s look at the ordering spreadsheet. The most important column in the ordering spreadsheet is the coach column. This coach column is the anchor point used to map against the team name in the registration. To the coach column in the ordering spreadsheet must match the team name in the registration file.
Note: There are more columns of data then what is shown in the image below.
Solution
Now that we have an understanding of the spreadsheets let’s look at the code. Before we get started, I would recommend coping the code from the script section and pasting it to PowerShell ISE. It will make it easier to follow.
Line 7 – 28 are used to match rows and columns within the ordering spreadsheet. For example, $colcoach is column 2 and $rowcoach starts at row 4. $colXTS is column 11 and $rowYXS starts at column 4.
Lines 32 – 66 are functions are file explorer pop-up boxes requesting the user to select the registration file and ordering spreadsheet. Line 51 sets the variable $path to the ordering form location.
Line 69 maps the data variable to the registration file.
Line 75 opens the Excel application. Line 78 opens the ordering form excel spreadsheet, by calling the variable $path.
Line 80 sets the variable $page to the main sheet name in the ordering spreadsheet. Line 81 sets $sheet to the $page sheet in the ordering spreadsheet.
Lines 92 – 96 selects one coach at a time. Once the coach is selected, a new worksheet is created in the ordering spreadsheet with the coach’s name.
Lines 98 – 105 set headers at row 1 for columns 1 – 8.
Lines 112 – 122 removes unwanted characters in the Team Name field from the registration CSV file.
Line 125 matches the Team Name field from the registration spreadsheet with the coach’s name in the ordering form. If the coach’s names do not match, the script will fail. Line 128 sets the current page to the coach’s name and line 129 increments the row by 1.
Now we’ve come to the static part of the code. Lines 134 – 197 matches shirt size in the registration file and counts the number of each shirt size. Since we are looping through coaches and then we find the kids on that coaches team, we are able to count shirt sizes for each coach.
Lines 202 – 245 is counting paints size.
Lines 250 – 257 counts the number of adult hats that were ordered.
Lines 261 – 268 adds the fields from the registration CSV file to the ordering form. The $row were incremented above and the column aligns to the headers created within each worksheet.
Lines 277 – 285 adds the total of each shirt size to the main sheet in the ordering form. The $CurrentRow is the current active row in the worksheet. The columns are the variables defined in the beginning of the script.
Line 286 – 295 clear the shirt variables so there is no value when the loop process the next coach.
Lines 299 – 314 perform the same actions as lines 277 – 295.
Lines 318 – 319 perform the same actions as lines 277 – 295.
Results
Now that we’ve gone through the script lets take a look at the ordering form spreadsheet after the script has been executed.
The first thing you’ll notice, opening the ordering form after running the script is, each coach with have a separate worksheet.
When you open one of the worksheets, each player will be listed along with certain attributes.
Within the main worksheet (fall baseball order), total counts will be calculated for shirt sizes, adult hats, and paints size.
Sample Spreadsheets
Spreadsheets that I used to run the code Sample.
Script
<#
The rows don’t mean anything (example – $rowcoach).
The only thing that we care about is the location of the column.
The col refers to the numerical value of the column letter in the order form.
The first section is for shirts, second section is for adult hats, and the last section is for pants.
#>
$rowcoach,$colcoach = 3,2
$rowYXS,$colXTS = 4,11
$rowYS,$colYS = 4,12
$rowYS,$colYM = 4,13
$rowYS,$colYL = 4,14
$rowYS,$colYXL = 4,15
$rowYS,$colAS = 4,16
$rowYS,$colAM = 4,17
$rowYS,$colAL = 4,18
$rowYS,$colAXL = 4,19
$rowYS,$colA2XL = 4,20
$rowHats,$colHats = 4,27
$rowYXS,$colXSP = 4,29
$rowYS,$colYSP = 4,30
$rowYS,$colYMP = 4,31
$rowYS,$colYLP = 4,32
$rowYS,$colYXLP = 4,33
$rowYS,$colASP = 4,34
$rowYS,$colAMP = 4,35
$rowYS,$colALP = 4,36
$Hats = $NULL
#This Function is used to call the location dialog box for the order form and registration form.
Function Get-FileName($initialDirectory)
{
[System.Reflection.Assembly]::LoadWithPartialName(“System.windows.forms”) | Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
#$OpenFileDialog.filter = “CSV (*.csv)| *.csv”
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#This message box asks for user to select the order form file
$msgBoxInputOrderForm = [System.Windows.MessageBox]::Show(‘Please Select the Order Form File’,’Order Form’,’Ok’)
switch ($msgBoxInputOrderForm) {
‘ok’ {
#The location dialog box will default to the users desktop
$path = Get-FileName “c:\users\$env:UserName\desktop”
}
}
#This message box asks for user to select the registration form file
$msgBoxInputRegistrationForm = [System.Windows.MessageBox]::Show(‘Please Select the Registration Form File’,’Registration CSV File’,’Ok’)
switch ($msgBoxInputRegistrationForm) {
‘ok’ {
#The location dialog box will default to the users desktop
$FolderPath = Get-FileName “c:\users\$env:UserName\desktop”
}
}
#Importing the registration file
$data = import-csv $FolderPath
#Code testing reasons, clearing the PowerShell session
$name = $null
#Calling the Excel application
$Excel = New-Object -Com Excel.Application
#Opening the order form
$Workbook = $Excel.Workbooks.Open($Path)
#This is the main sheet name in the ordering form
$page = ‘Fall Baseball Order’
$Sheet = $Workbook.worksheets.item($page)
$rowMax = ($sheet.UsedRange.Rows).count
$rowMax = $rowMax – 2
#Start reading through the coaches in the ordering form
for ($i=1; $i -le $rowMax-1; $i++)
{
$name = $sheet.Cells.Item($rowcoach+$i,$colcoach).text
$currentrow = $rowcoach+$i
#Select one coach at a time
foreach ($CoachesName in $Name)
{
#Create a new worksheet for each coach and add feilds to each worksheet
$worksheet = $Workbook.worksheets.add()
$Worksheet.Name = $CoachesName
$Worksheet.Cells.Item(1,1) = “First Name”
$Worksheet.Cells.Item(1,2) = “Last Name”
$Worksheet.Cells.Item(1,3) = “Jersey Size”
$Worksheet.Cells.Item(1,4) = “PantsSize”
$Worksheet.Cells.Item(1,5) = “Adult Hat”
$Worksheet.Cells.Item(1,6) = “Adult t-shirt1”
$Worksheet.Cells.Item(1,7) = “Adult t-shirt2”
$Worksheet.Cells.Item(1,8) = “Team Name”
foreach($child in $data)
{
#The coach field is filled with all kinds of usless data.
#To remove the data that is no longer needed, remove the division fields
$coach = $child.’Team Name’
$coach = $coach -replace (“>T-Ball>”)
$coach = $coach -replace (“>Rookie>”)
$coach = $coach -replace (“>Rookie1>”)
$coach = $coach -replace (“>Rookie 1>”)
$coach = $coach -replace (“>Rookie2>”)
$coach = $coach -replace (“>Rookie 2>”)
$coach = $coach -replace (“>Minors>”)
$coach = $coach -replace (“>Majors>”)
$coach = $coach -replace (“>10U Softball>- “)
$coach = $coach -replace (“>8U Softball>- “)
If ($coach -notcontains ” “)
{
If ($coach -eq $CoachesName)
{
$CurrentPage = $CoachesName
$CurrentSheet = $Workbook.worksheets.item($CurrentPage)
$row += + 1
#Cycle through each child of each team and determine thier shirts size.
#Take a total of each size and save it to the master sheet.
If ($child.JerseySize -eq “Youth X-Small 22-24”)
{
$XST += 1
}
If ($child.JerseySize -eq “Youth X-Small 24-26”)
{
$XST += 1
}
If ($child.JerseySize -eq “Youth Small 24-26”)
{
$YS += 1
}
If ($child.JerseySize -eq “Youth Small 26-28”)
{
$YS += 1
}
If ($child.JerseySize -eq “Youth Small 26-27”)
{
$YS += 1
}
If ($child.JerseySize -eq “Youth Medium 26-28”)
{
$YM += 1
}
If ($child.JerseySize -eq “Youth Medium 28-30”)
{
$YM += + 1
}
If ($child.JerseySize -eq “Youth Large 30-32”)
{
$YL += + 1
}
If ($child.JerseySize -eq “Youth X-Large 32-34”)
{
$YXL += + 1
}
If ($child.JerseySize -eq “Adult Small 32-34”)
{
$AS += + 1
}
If ($child.JerseySize -eq “Adult Small 34-36”)
{
$AS += + 1
}
If ($child.JerseySize -eq “Adult Medium 34-36”)
{
$AM += + 1
}
If ($child.JerseySize -eq “Adult Medium 38-40”)
{
$AM += + 1
}
If ($child.JerseySize -eq “Adult Large 36-38”)
{
$AL += + 1
}
If ($child.JerseySize -eq “Adult Large 42-44”)
{
$AL += + 1
}
If ($child.JerseySize -eq “Adult X-Large 46-48”)
{
$AXL += + 1
}
#Cycle through each child of each team and determine thier pants size.
#Take a total of each size and save it to the master sheet.
If ($child.PantsSize -eq “Youth X-Small”)
{
$XSP += 1
}
If ($child.PantsSize -eq “Youth Small (Waist 20-22/Inseam 25)”)
{
$YSP += 1
}
If ($child.PantsSize -eq “Youth Small (Girls 20-22–Boys 22-24)”)
{
$YSP += 1
}
If ($child.PantsSize -eq “Youth Medium (Waist 22-24/ Inseam 26)”)
{
$YMP += 1
}
If ($child.PantsSize -eq “Youth Medium (Girls 22-24–Boys 24-26)”)
{
$YMP += 1
}
If ($child.PantsSize -eq “Youth Large (Waist 24-26/Inseam 27)”)
{
$YLP += 1
}
If ($child.PantsSize -eq “Youth Large (Girls 24-26–Boys 26-28)”)
{
$YLP += 1
}
If ($child.PantsSize -eq “Youth X-Large”)
{
$YXLP += 1
}
If ($child.PantsSize -eq “Adult Small (Waist 26-28/Inseam 28)”)
{
$ASP += 1
}
If ($child.PantsSize -eq “Adult Medium (Waist 28-30/Inseam 29)”)
{
$AMP += 1
}
If ($child.PantsSize -eq “Adult Large”)
{
$ALP += 1
}
#Add the number of adult hats. Since the Adult hat feild has all kind of data, we only capture the first value.
#The value is a string so we have to use one as a string and not an int
If ($child.AdultTeamHatQty[0] -eq ‘1’)
{
$Hats +=1
}
If ($child.AdultTeamHatQty[0] -eq ‘2’)
{
$Hats +=2
}
#======================================================================================================
#Fill out the cells for each child
$CurrentSheet.Cells.Item($row,1) = $child.’First Name’
$CurrentSheet.Cells.Item($row,2) = $child.’Last Name’
$CurrentSheet.Cells.Item($row,3) = $child.JerseySize
$CurrentSheet.Cells.Item($row,4) = $child.PantsSize
$CurrentSheet.Cells.Item($row,5) = $child.AdultTeamHatQty
$CurrentSheet.Cells.Item($row,6) = $child.AdultTShirtSize1
$CurrentSheet.Cells.Item($row,7) = $child.AdultTShirtSize2
$CurrentSheet.Cells.Item($row,8) = $CoachesName
}
}
}
#Add the shirt count for each size and clear the value
$Sheet.Cells.Item($currentrow,$colXTS) = $XST
$Sheet.Cells.Item($currentrow,$colYS) = $YS
$Sheet.Cells.Item($currentrow,$colYM) = $YM
$Sheet.Cells.Item($currentrow,$colYL) = $YL
$Sheet.Cells.Item($currentrow,$colYXL) = $YXL
$Sheet.Cells.Item($currentrow,$colAS) = $AS
$Sheet.Cells.Item($currentrow,$colAM) = $AM
$Sheet.Cells.Item($currentrow,$colAL) = $AL
$Sheet.Cells.Item($currentrow,$colAXL) = $AXL
$XST = $null
$XS = $null
$YS = $null
$YM = $null
$YL = $null
$YXL = $null
$AS = $Null
$AM = $Null
$AL = $Null
$AXL = $Null
#Add the pants count for each size and clear the value
$Sheet.Cells.Item($currentrow,$colXSP) = $XSP
$Sheet.Cells.Item($currentrow,$colYSP) = $YSP
$Sheet.Cells.Item($currentrow,$colYMP) = $YMP
$Sheet.Cells.Item($currentrow,$colYLP) = $YLP
$Sheet.Cells.Item($currentrow,$colYXLP) = $YXLP
$Sheet.Cells.Item($currentrow,$colASP) = $ASP
$Sheet.Cells.Item($currentrow,$colAMP) = $AMP
$Sheet.Cells.Item($currentrow,$colALP) = $ALP
$XSP = $null
$YSP = $null
$YMP = $null
$YLP = $null
$YXLP = $null
$ASP = $null
$AMP = $null
$ALP = $null
#Add the hat count for each coach
$Sheet.Cells.Item($currentrow,$colHats) = $Hats
$hATS = $Null
#Reset row to 1 for the next coach
$row = 1
}
}
#Close Excel
$Workbook.Save()
$Workbook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)