Merging two CSV files with Powershell

Question

I have two CSV files.Columns names are like below example

CSV1 
SAMAccountName mail
JohnS          johns@example.com

CSV2
GivenName Surname DisplayName Department Title Mail MobilePhone Manager

What I've wanted to do is to compare the CSV1 with CSV2 and match the mail column in CSV2 file with SAMAccountName Column in CSV1 and export a new CSV which gives the below output

CSV3(merged.csv) as in script
GivenName Surname DisplayName Department Title Mail MobilePhone Manager SAMAccountName

I tried the below powershell script which i found in stack overflow site posted sometime back and i never got the values from the SAMAccountName column of CSV1.All other column names are coming up without any issue but SAMAccountName column is blank.I'm not a powershell script expert need some help here.Below is the example script i used.

$csv1 = Import-Csv -Path C:\TEMP\CSV1.csv
$csv2 = Import-Csv -Path C:\temp\CSV2.csv

ForEach($Record in $csv2){
$MatchedValue = (Compare-Object $csv1 $Record -Property "SAMAccountName" -IncludeEqual -ExcludeDifferent -PassThru).value
$Record = Add-Member -InputObject $Record -Type NoteProperty -Name "SAMAccountName" -Value $MatchedValue
}
$csv2|Export-Csv 'C:\temp\merged.csv' -NoTypeInformation

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 .\test2.csv |Update-Object -On Mail (Import-csv .\test1.csv) |Export-csv .\test3.csv` — iRon, Sep 13 '21 at 09:47

score 0 · Accepted Answer · answered Sep 13 '21 at 10:01

This is very common, You can do this quite easily using:

$csv1 = Import-Csv -Path 'C:\temp\CSV1.csv'
$csv2 = Import-Csv -Path 'C:\temp\CSV2.csv'

$csv3 = foreach ($record in $csv2) {
    $matchingRecord = $csv1 | Where-Object { $_.mail -eq $record.mail }
    # if your goal is to ONLY export records with a matching email address,
    # uncomment the if ($matchingRecord) condition

    #if ($matchingRecord) {
        $record | Select-Object *, @{Name = 'SamAccountName'; Expression = {$matchingRecord.SamAccountName}}
    #}
}

$csv3 | Export-Csv -Path 'C:\temp\merged.csv' -NoTypeInformation

Merging two CSV files with Powershell

1 Answers1