Powershell to compare two columns with csv file

Question

I would like to compare values in two columns in file1.csv with file2.csv, then assigned the matching value to a new column in file1.csv.

ATM, this is the working so far. Simpler format would be ideal.

$report = Import-CSV -Path "C:\report.csv" -Encoding UTF8
$reference = Import-CSV -Path "C:\team.csv" -Encoding UTF8

foreach ($team1 in $report){
$matched = $false
    foreach ($team2 in $reference){
    $obj = "" | select "Type","Setup","Responsible","Team","Main"
    if($team1.'Setup' -like "*$($team2.'Main')*"){
        $matchCounter++
        $matched = $true
        $obj.'Type' = $team1.'Type'
        $obj.'Setup' = $team1.'Setup'
        $obj.'Responsible' = $team1.'Responsible'
        $obj.'Team' = $team2.'Team'
        $obj | Export-Csv -Path "C:\Output.csv" -Append -NoTypeInformation -Encoding 
UTF8
        }
    }
}

This is what the files like;

file1

type  setup          responsible
----  -------        -----------   
y     master fin       susan
y     sensei kuno      peter
y     sensei jon       peter
y     junior           jumo

file2

main     team
----     -----
master   sa1
sensei   sr2
jumo     st6

desired file3

type  setup       responsible  team
----  -------     -----------  -----
y     master fin     susan      sa1
y     sensei kuno    peter      sr2
y     sensei jon     peter      sr2
y     junior         jumo       st6

What I would like to achieve is column setup and responsible (file1) to be compared to main column (file2) and get its adjacent team. Atm, im not sure how to go about skipping cells in column1 with no reference in file2, but existed in column2. Also, to compare only the first character of setup in file1 with file2.

For your script, comparing the first character would be something like: `if(($team1.'Setup')[0] -eq ($team2.'Main')[0]){ ...`. In case you don't want to reinvent the wheel (also regarding performance). Using this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Import-Csv .\report.csv |Join (Import-Csv .\team.csv) -on { $_.Setup[0] } -eq { $_.Main[0] } |Format-Table` — iRon, Jun 29 '22 at 08:52
`jumo` can only be matched in the `responsible` column of file1, not in the `setup` column where the rest of the values are being matched. Please provide better examples — Theo, Jun 29 '22 at 11:44
@iRon your first method sorted the first character issue, but atm its deleting the rows with no reference, how should i simply skip those row instead of deleting it? — nasrul799, Jun 30 '22 at 03:51

iRon · Answer 1 · 2022-07-04T12:44:56.313

I guess you you just want to do this:

# $report = Import-CSV -Path "C:\report.csv" -Encoding UTF8
$report = ConvertFrom-Csv @'
type, setup,       responsible
y,    master fin,  susan
y,    sensei kuno, peter
y,    sensei jon,  peter
y,    junior,      jumo
'@

# $reference = Import-CSV -Path "C:\team.csv" -Encoding UTF8
$reference = ConvertFrom-Csv @'
main,   team
master, sa1
sensei, sr2
jumo,   st6
'@
 
foreach ($team1 in $report){
    foreach ($team2 in $reference){
        if($team1.Setup[0] -eq $team2.Main[0]){
            [pscustomobject]@{
                Type        = $team1.Type
                Setup       = $team1.Setup
                Responsible = $team1.Responsible
                Team        = $team2.Team
            }
        }
    }
} # |Export-Csv -Path .\Output.csv -NoTypeInformation -Encoding UTF8

Results:

Type Setup       Responsible Team
---- -----       ----------- ----
y    master fin  susan       sa1
y    sensei kuno peter       sr2
y    sensei jon  peter       sr2
y    junior      jumo        st6

Note that the for correctly using the PowerShell Pipeline (and performance reasons), the final (Export-Csv) cmdlet shouldn't reside in your process but actually at the end of the pipeline.

its not displaying any data atm, how should i pass the backtick, in the csv data itself? any way to get reference in code itself, without altering the csv reference table? — nasrul799, Jul 04 '22 at 10:10
You should just be able to paste the [mcve] above in PowerShell and get the results as shown which should give you a start for your own data. There is nothing about backticks or "*get reference in code itself*" in the original question, I suggest to [accept](https://stackoverflow.com/help/someone-answers) this answer (if the example works) and open new questions for any new question. — iRon, Jul 04 '22 at 12:54

Powershell to compare two columns with csv file

1 Answers1