CSV add fields based on values of fields present

Question

Guys and girls,

Can you look at this, am i thinking in the right way:

I have this input CSV:

Usr;Function;Department;Division;
Sebastian;BookKeeper;Sales;Finance;
Peter;Team Lead;Sales;Finance;
Mirjam;Consultant;Marketing;Finance;
Sandra;Team Lead;Marketing;Finance;
Cinthia;Head of Division; ;Finance
Cindy;Consultant;Education;HR;
Margareth;Team Lead;Education;HR;
John;Head of Division; ;HR;

I need to add 2 fields Aut1 (Team lead) and Aut2 (Head of Division) - Autorisator

So the ouput must be:

Usr;Function;Department;Division;Aut1;Aut2
Sebastian;BookKeeper;Sales;Finance;Peter;Cynthia;
Peter;Team Lead;Sales;Finance;Peter;Cynthia;
Mirjam;Consultant;Marketing;Finance;Sandra;Cynthia;
Sandra;Team Lead;Marketing;Finance;Sandra;Cynthia;
Cynthia;Head of Division; ;Finance;Cynthia;Cynthia;
Cindy;Consultant;Education;HR;Margareth;John;
Margareth;Team Lead;Education;HR;Margareth;John;
John;Head of Division; ;HR;John;John;

code would be like this:

Import-Csv C:\Temp\import.csv -Encoding UTF8 -Delimiter ';' |
     ForEach-Object {
         $usr = $_.Usr
         $func = $_.Function
         $dept = $_.Department
         $div = $_.Division


         if (($div -eq 'Finance') -and ($dept -eq 'Sales')  -and ($func -eq 'Team Lead'))
       {
             [PSCustomObject]@{
                 Name = $_.Name
                 Auth1 = $usr
             }
         }
         elseif (($div -eq 'Finance') -and ($dept -eq 'Sales')  -and ($func -eq 'Head of Division'))
         {
             [PSCustomObject]@{
                 Name = $_.Name
                  Auth2 = $usr
             }
         }
     } | Export-Csv c:\temp\aut.csv -NoTypeInformation

Feedback would be great

Answer 1

How about this?

Import-Csv C:\Junk\import.csv -Encoding UTF8 -Delimiter ';' |
    ForEach-Object {
        $auth1 = $null
        $auth2 = $null
        if (($_.division -eq 'Finance') -and ($_.department -eq 'Sales')  -and ($_.function -eq 'Team Lead'))
        {
            $auth1 = $_.Usr
        }
        elseif (($_.divivision -eq 'Finance') -and ($_.department -eq 'Sales')  -and ($_function -eq 'Head of Division'))
        {
            $auth2 = $_.Usr
        }
        $_ | Add-Member NoteProperty Auth1 $auth1
        $_ | Add-Member NoteProperty Auth2 $auth2 -PassThru

    } | Export-Csv c:\Junk\aut.csv -NoTypeInformation

Your code never added the new properties to the imported row. Also, you have to add both new properties to ALL the rows. If you add on, or the other, or none, the resulting CSV won't work the way you expect it to because the number of columns in each row would be unequal.

Answer 2

That looks very good, I'm going to test it tomorrow. Thanks @Rich Matheisen

Answer 3

Hi,

There should be two loops. The first one is for getting the objects with function "Team Lead" and "Head of Division" and the second one is for adding new properties to all the objects.

$users = Import-Csv C:\Temp\import.csv -Encoding UTF8 -Delimiter ';'   
$Auth1s=@()  
$Auth2s=@()  
$users | ForEach-Object{  
    if($_.function -eq "Team Lead"){  
        $Auth1s += $_  
    }   
    if($_.function -eq "Head of Division"){  
        $Auth2s += $_  
    }   
}  
$users | ForEach-Object{  
    foreach($Auth1 in $Auth1s){  
        if($_.Department -eq $Auth1.Department){  
            $_ | Add-Member -MemberType NoteProperty -Name "Auth1" -Value $Auth1.Usr  
        }  
    }  
    foreach($Auth2 in $Auth2s){  
        if($_.Division -eq $Auth2.Division){  
            $_ | Add-Member -MemberType NoteProperty -Name "Auth2" -Value $Auth2.Usr   
        }  
    }  
    if($_.function -eq "Head of Division"){  
        $_ | Add-Member -MemberType NoteProperty -Name "Auth1" -Value $_.Usr   
    }  
}  
$users | Export-Csv c:\temp\aut.csv -NoTypeInformation  

Best Regards，
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

It seems like the solution from @Ian Xue (Shanghai Wicresoft Co., Ltd.) is working, but it depend on the sort order of the file which column it adds. Very strange.
When it's not ordered it gives me the Auth1 in the export-csv.
When it's ordered it gives me the Auth 2

Without export-csv it gives me both.

Answer 5

This should take care of the "many departments/divisions" and correctly populate all rows with a team lead (except for division heads) and division head. There's no need to sort the CSV.

$AllRows = Import-Csv C:\Junk\import.csv -Encoding UTF8 -Delimiter ';' 
# 1st pass --get "Head of Division" and "Team Lead"
$DivisionHead = @{}
$TeamLead = @{}
$AllRows |
    ForEach-Object{
        if ($_.Function -eq "Head of Division"){    # N.B. NO CHECK for duplicates!
            $DivisionHead.($_.Division) = $_.Usr
        }
        if ($_.Function -eq "Team Lead"){             # N.B. NO CHECK for duplicates!
            $Team = "{0};{1}" -f $_.Division,$_.Department  # create unique key for division/department 
                                                            # (there may be the same department in multiple divisions)
            $TeamLead.$Team = $_.Usr
        }
    }
# 2nd pass -- fill Auth1 and Auth2 columns
$AllRows |
    ForEach-Object {
        $auth1 = $null     
        $auth2 = $null     

        $Team = "{0};{1}" -f $_.Division,$_.Department
        $auth1 = $TeamLead.$Team                # Team Lead
        $auth2 = $DivisionHead.($_.Division)    # Head of Division

        $_ | Add-Member NoteProperty Auth1 $auth1
        $_ | Add-Member NoteProperty Auth2 $auth2 -PassThru
    } | Export-Csv c:\Junk\aut.csv -NoTypeInformation