Merge .csv files with PS

PowerShell Help
1

I have two .csv files.
File1.csv
“ADGroup”,“Name”,“SamAccountName”
“mygroup1”,“Doe, John”’ “jdoe1”
“mygroup1”,“Spade, Sam”’ “sspade”
“mygroup5”,“Jones, Bill”’ “bjones”

File2.csv
“SamAccountName”,“EmployeeID”
“jdoe1”, “123456”
“sspade”, “654321”
“bjones”, “789012”

I’m looking to merge the columns based on the common SamAccountName field for this result:

file3.csv
“ADGroup”,“Name”,“SamAccountName”, “EmployeeID”
“mygroup1”,“Doe, John”’ “jdoe1”, “123456”
“mygroup1”,“Spade, Sam”’ “sspade”, “654321”
“mygroup5”,“Jones, Bill”’ “bjones”, “789012”

Easily done in MSAccess but I’m looking to automate this with a PS script.
Thanks

2

Please share the script you wrote so far

3

I abandoned the PS code for a minute and here’s my SQL code for it…

SELECT Table1.Citrix_Group, Table1.Name, Table1.SamAccountName AS Table1_SamAccountName, Table2.SamAccountName AS Table2_SamAccountName, Table2.EmployeeID

FROM Table1 LEFT JOIN Table2 ON Table1.[SamAccountName] = Table2.[SamAccountName]

UNION SELECT Table1.Citrix_Group, Table1.Name, Table1.SamAccountName AS Table1_SamAccountName, Table2.SamAccountName AS Table2_SamAccountName, Table2.EmployeeID

FROM Table1 RIGHT JOIN Table2 ON Table1.[SamAccountName] = Table2.[SamAccountName]

WHERE Table1.[SamAccountName] IS NULL;

4 
#region Sample data
$File1Name = '.\File1.csv'
@'
"ADGroup","Name","SamAccountName"
"mygroup1","Doe, John","jdoe1"
"mygroup1","Spade, Sam","sspade"
"mygroup5","Jones, Bill","bjones"
'@ | Out-File $File1Name

$File2Name = '.\File2.csv'
@'
"SamAccountName","EmployeeID"
"jdoe1", "123456"
"sspade", "654321"
"bjones", "789012"
#endregion
'@ | Out-File $File2Name
#endregion

#region read input CSV
$File1Objects = Import-Csv $File1Name
'File 1 objects:'
$File1Objects
$File2Objects = Import-Csv $File2Name
'File 2 objects:'
$File2Objects | Out-String
#endregion

#region Merge the 2 objects/tables based on common property/column SamAccountName
$Merged = foreach ($Item in $File1Objects) {
    if ($FoundMatch = $File2Objects | where { $PSItem.SamAccountName -eq $Item.SamAccountName }) {
        [PSCustomObject][Ordered]@{
            SamAccountName = $Item.SamAccountName
            Name           = $Item.Name
            ADGroup        = $Item.ADGroup
            EmployeeID     = $FoundMatch.EmployeeID 
        }
    }
}
'Merged:'
$Merged | Format-Table -AutoSize
#endregion

This works well if SamAccountName is unique in both tables.
If table2 has more than 1 record for a given SamAccountName, you’ll get multiple values for EmployeeID in the merged table for that record
If table2 has records with no corresponding SamAccountName in table1, they will not appear in the merged table. If you anticipate this scenario a second loop through table2 is required