Merge .csv files with PS

This topic contains 3 replies, has 2 voices, and was last updated by  Sam Boutros 3 months, 3 weeks ago.

  • Author
    Posts
  • #91997

    Nick D
    Participant

    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

  • #92000

    Sam Boutros
    Participant

    Please share the script you wrote so far

  • #92006

    Nick D
    Participant

    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;

  • #92042

    Sam Boutros
    Participant
    #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

You must be logged in to reply to this topic.