Merge .csv files with PS

Welcome Forums General PowerShell Q&A Merge .csv files with PS

This topic contains 3 replies, has 2 voices, and was last updated by

 
Participant
9 months, 3 weeks ago.

  • Author
    Posts
  • #91997

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 59
    Rank: Member

    Please share the script you wrote so far

  • #92006

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 59
    Rank: Member
    #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

The topic ‘Merge .csv files with PS’ is closed to new replies.