Hi everyone,
recently I had to solve the following problem:
I had to split up a list of costcenters and names in a csv-file based on the costcenter. In the first iteration the requirement was pretty easy: “If the costcenter is beginning with a 5 then the usernames shal be put into file “List1.txt”. There were several exceptions to it:
If the costcenter is 5300, 5301, 5302, 5304, the usernames must be put into “List2.txt”
All the rest should be put into file “all.txt”
So let´s start:
The csv looks like this:
costcenter; user; foo1; foo2
4711; testuser; bla1; bla2
This is how I solved the requirement in the first iteration:
#define your paths here
$importpath = "D:testTest_1_01.csv"
$filepath_File1 = "D:testList1.txt"
$filepath_File2 = "D:testList2.txt"
$filepath_File_all = "d:testall.txt"
#import csv file
$csv = Import-Csv $importpath -Delimiter ";"
# for each row of the csv compare the costcenter against the defined values
$csv | foreach-object { $cc = $_.costcenter # get the costcenter-value out of this row
$user = $_.user # get user-value of this row
# now the first switch is used to decide what happens when the costcenter begins with a 5.
switch -wildcard ($cc)
{
"5*" { switch ($cc) # in case the costcenter begins with 5, we have our next switch command to check if it one of our exceptions:
{
5300 { $user >> $filepath_File1 # in case the costcenter is 5300, the username must be put into file1.txt and file3.txt
$user >> $filepath_File_all }
5301 { $user >> $filepath_File1
$user >> $filepath_File_all }
5302 { $user >> $filepath_File1
$user >> $filepath_File_all }
5304 { $user >> $filepath_File1
$user >> $filepath_File3_all}
default { $user >> $filepath_File2 # the default switch-case defines what to to when none of the possible cases matches.
# in our case, the username must be put into file2.txt and fil3.txt
$user >> $filepath_File_all }
}
}
default { $user >> $filepath_File_all} # all other usernames must be put into file3.txt
}
}
As with all good working solutions it did not take long until the requirements went bigger and looked like this:
1. If the costcenter is one of a list of 30 values, the username must be put into “File1.txt”
2. If the costcenter is one of a list of 15 values, the username must be put into “File2.txt”
3. If the costcenter is one of a list of 136 values (including 5000, compare this with the first iteration!), the username must be put into “File3.txt”
4. all usernames must be put into “File4.txt”
So it was clear to me from the very first moment, that the switch command would not be my weapon of choice and I had to rethink. After some thinking and searching, asking at powershell.org, I came to this solution:
To make it more flexible (as we know, requirement can change quite fast) I decided to get the values the costcenter should be compared to into separate files. Each output file needs an input file with the values in it. So for file1.txt, there is a file1_costcenters.csv with one costcenter in each line. With this setup, we can load the values easily with “get-content”
The new script looks like this:
#define your paths here
$importpath = "D:testTest_1_01.csv"
$filepath_File1 = "D:testList1.txt"
$filepath_File2 = "D:testList2.txt"
$filepath_File3 = "d:testList3.txt"
$filepath_File_all = "d:testall.txt"
$filepath_file1_CC = "d:testfile1_costcenters.csv"
$filepath_file2_CC = "d:testfile2_costcenters.csv"
$filepath_file3_CC = "d:testfile3_costcenters.csv"
#import csv file
$csv = Import-Csv $importpath -Delimiter ";"
#import the values of the costcenter files
$file1 = get-Content "$filepath_file1_CC"
$file2 = Get-Content "$filepath_file2_CC"
$file3 = Get-Content "$filepath_file3_CC"
# for each line of the csv, check if one value in one of the compare-to files matches the costcenter and write the username to the corresponding file and the all-file
$csv | foreach-object { $cc = $_.costcenter #get costcenter
$user = $_.user #get username
foreach ($file1_cc in $file1) #compare each value of file1 to the costcenter of the row current row of the imported csv.
{ if ($cc -eq $file1_cc)
{$user >> $filepath_file1 # write username to file1
}
}
foreach ($file2_cc in $file2)
{ if ($cc -eq $file2_cc)
{$user >> $filepath_file2
}
}
foreach ($file3_cc in $file3)
{ if ($cc -eq $file3_cc)
{$user >> $filepath_file4
}
}
$user >> $filepath_file_all # put the username to the All-file
}
So now we have the possibility to let the end users define and change the costcenters per file and when a new file is needed, we only have to add another foreach block.
As I´m just a Powershell beginner this is probably not the best solution, so please post your suggestions to the comments.