Splitting a csv by comparing values against arrays

Tagged: ,

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 3 years, 3 months ago.

  • Author
    Posts
  • #12728

    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

    added detailed information

    Christian

  • #12730
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
    }
    #
    
  • #12734

    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

  • #12736
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    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
        }
    }
    

You must be logged in to reply to this topic.