compare a header value between two .csv file if match is found then add a column with corresponding value using powershell

Question

I seen this kind of posts but couldn't able to get exact working module. I hope someone can help me on this.

I have two .CSV files

file1.csv

"ID","status","Type"

"mzx1","Active","A"

"mzx2","Active","B"

"mzx3","Active","C"

file2.csv

"ID","User Company","Date"

"mzx1","MS","2020-01-01"

"mzx3","Sam","020-01-01"

"mzx5","new","020-01-03"

I need to compare the ID value in file2.csv file if it matches then add a column with value "User Company"

file3.csv

"ID","status","Type","Company"

"mzx1","Active","A","MS"

"mzx2","Active","B","Not found"

"mzx3","Active","C","Sam"

Now if you see the example of file3.csv file which has all the rows of file1.csv file and new column "Company". As well for no ID match found it should draft "Not found".

Using this [`Join-Object`](https://www.powershellgallery.com/packages/Join) cmdlet (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `$File1 | LeftJoin $File2 -on ID -Property ID,status,Type,@{Company = {$Right.'User Company'}}` — iRon, Aug 06 '20 at 10:37

score 0 · Answer 1 · answered Aug 05 '20 at 18:34

This example show how to add property to a csv $file1 variable:

$file1 = @'
"ID","status","Type"
"mzx1","Active","A"
"mzx2","Active","B"
"mzx3","Active","C"
'@ | ConvertFrom-Csv

$file2 = @'
"ID","User Company","Date"
"mzx1","MS","2020-01-01"
"mzx3","Sam","020-01-01"
"mzx5","new","020-01-03"
'@| ConvertFrom-Csv


foreach($l in $file1){
    $l2 = $file2 | Where { $_.ID -eq $l.ID }
    if ($l2 )
    {
        $l | Add-Member -MemberType NoteProperty -Name 'User Company' -Value $l2.'User Company'
    }
    else
    {
        $l | Add-Member -MemberType NoteProperty -Name 'User Company' -Value 'Not found'
    }
}
$file1

score 0 · Answer 2 · answered Aug 05 '20 at 18:51

0

You can use a calculated property with Select-Object in this PowerShell v4+ solution:

$file1 = Import-Csv file1.csv
$file2 = Import-Csv file2.csv
$file3 = foreach ($row in $file1) {
    $lookup = $file2.Where({$_.ID -eq $row.ID},'First')
    $row | Select *,@{n='Company';e={if ($lookup) { $lookup.'User Company' } else { 'Not Found'}}}
}
$file3 | Export-Csv file3.csv -NoType

answered Aug 05 '20 at 18:51

AdminOfThings

23,946
4
17
27

@BalaKrishna, please consider marking the answer (green checkbox) if you feel this answered your question. This helps future readers. – AdminOfThings Aug 06 '20 at 22:13

score 0 · Answer 3 · answered Aug 06 '20 at 06:06

You can also use the -AsHashTable property of Group-Object to create a lookup table and combine with calculated properties.

$file1 = @'
"ID","status","Type"
"mzx1","Active","A"
"mzx2","Active","B"
"mzx3","Active","C"
'@ | ConvertFrom-Csv

$file2 = @'
"ID","User Company","Date"
"mzx1","MS","2020-01-01"
"mzx3","Sam","020-01-01"
"mzx5","new","020-01-03"
'@ | ConvertFrom-Csv | group -Property id -AsHashTable

$file1 | select *,@{n='Company';e={$(if($file2[$_.id]){$file2[$_.id].'User Company'}else{'Not Found'})}}

Output as expected

ID   status Type Company  
--   ------ ---- -------  
mzx1 Active A    MS       
mzx2 Active B    Not Found
mzx3 Active C    Sam

Could look a lot neater

$file1 = @'
"ID","status","Type"
"mzx1","Active","A"
"mzx2","Active","B"
"mzx3","Active","C"
'@ | ConvertFrom-Csv

$file2 = @'
"ID","User Company","Date"
"mzx1","MS","2020-01-01"
"mzx3","Sam","020-01-01"
"mzx5","new","020-01-03"
'@| ConvertFrom-Csv | group -Property id -AsHashTable

$company = {
    Param($ID)

    $out = $file2[$ID].'User Company'
    if($out){$out} else {'Not Found'}
}

$file1 | select *,@{n='Company';e={& $company $_.id}} | Export-Csv $file3 -NoTypeInformation

compare a header value between two .csv file if match is found then add a column with corresponding value using powershell

file1.csv

file2.csv

file3.csv

3 Answers3