Splitting a csv by comparing values against arrays

PowerShell Help
1

Hi,

we have a csv that looks like this:

Lfd.Nr.;SAP-System-ID;Mandant;Benutzer;Abrechnungsnr.;Benutzergruppe;ID Benutzertyp;Kostenstelle;aktuelles Datum
1;X23;000;USER15;XXX-IT;CN;AX;5300;01.01.2014
2;X23;000;USERK;XXX-NU;PS;AX;7230;01.01.2014
3;X23;000;USERZ;XXX-NU;BC USERADM;;8140;01.01.2014
4;X23;000;USERY;XXX-NU;PS;AX;8781;01.01.2014
5;X23;000;USER3;XXX-NU;PP;AX;7340;01.01.2014
6;X23;000;USER2;XXX-NU;NEW;AX;5301;01.01.2014
7;X23;000;USER1;XXX-NU;NEW;AX;8330;01.01.2014

Now I need to split the csv into different files depending on the “Kostenstelle”. Only the Username must be put into the new files.
When I only had several “Kostenstelle” to compare against, I solved it this way:

$importpath = "D:\test\original_1_01.csv"
$filepath_1 = "D:\test\1.txt"
$filepath_2 = "D:\test\2.txt"
$filepath_rest = "d:\test\rest.txt"

$csv = Import-Csv $importpath -Delimiter ";" 
$csv | foreach-object { $cc = $_.Kostenstelle 
                        $user = $_.Benutzer
                
                        switch -wildcard ($cc)
                        { 
                            "5*" { switch ($cc)
                                    {
                                     
                                     5300 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest } 
                                     5301 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest } 
                                     5302 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5304 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5305 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5355 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5802 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5803 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     5811 { $user  >> $filepath_1 
                                            $user  >> $filepath_rest }  
                                     default { $user  >> $filepath_2
                                               $user  >> $filepath_rest } 
                                     }
                                  }
                            default  { $user  >> $filepath_rest} 
                        }
                    }

Now I have the challenge to enable 130 more possible “cases” / “Kostenstelle” for the split into the next file aka $filepath_3

My idea was to match the “Kostenstelle” of the original csv to one array of defined “Kostenstelle” for every file.

So for example instead of matching every single Kostenstelle like above, I would like to have an array $Kostenstelle_filepath1 and have a match like

                                    $Kostenstelle_filepath_1 = 5300,5301,5302,5304,5305,5355,5802,5803,5811
                                    switch ($cc)
                                    {
                                     
                                     $Kostenstelle_filepath_1 { $user  >> $filepath_1 
                                                               $user  >> $filepath_rest }

for every needed output file.

Do you have any Idea how to get something like this to work?

Thanks in advance

<edit:> added detailed information

Christian

2

In this case, you’re probably better off using “if” statements rather than switch, something along these lines:

#
$Kostenstelle_filepath_1 = 5300,5301,5302,5304,5305,5355,5802,5803,5811

if ($Kostenstelle_filepath_1 -contains $cc)
{                                    
    $user  >> $filepath_1 
    $user  >> $filepath_rest
}
elseif ($Kostenstelle_filepath_2 -contains $cc)
{
    # .. etc
} 
#

Personally, I would prefer to set up a hashtable instead of an array, though. The performance of lookups is much better, and you’ll be able to cut way down on the number of conditionals (though the table itself will be quite large; you could move this into a data file that the script loads, if you prefer.)

For example:

#
$KostenstelleTable = @{
    '5300' = $filepath_1
    '5301' = $filepath_1
    '5302' = $filepath_1

    # .. etc
}

$filepath = $KostenstelleTable[$cc]

if ($filepath)
{
    $user >> $filepath
    $user >> $filepath_rest
}
else
{
    # Code here to handle the case where the table does not have a match for $cc
}
#
3

Hi Dave,

thanks, looks good. I´ll try it later. It would be nice to have separate files for the declaration of the hashtable so I could make this more flexible for the other users.
How would I import a file to a hashtable?

Thanks in advance

Christian

4

I’d probably put the values into CSV file. Assuming $filePath1 is actually ‘C:\Logs\File1.txt’, the CSV would look something like:

"Kostenstelle","FilePath"
"5300","C:\Logs\File1.txt"
"5301","C:\Logs\File1.txt"
"5302","C:\Logs\File1.txt"

… and so on. To import that CSV data into a hashtable, I’d do something like this:

$kostenstelleTable = @{}

Import-Csv -Path .\myDataFile.csv |
ForEach-Object {
    # Sanity check to make sure we're looking at valid data; hashtables can't have Null keys
    if ($_.Kostenstelle)
    {
        $kostenstelleTable[$_.Kostenstelle] = $_.FilePath
    }
}