locked
Compare 2 Csvs, returning all matches into separate rows, compare on mutiple Values, Export Values from both CSVs RRS feed

  • Question

  • i Have 2 CSV's

    left.csv

        Ref_ID,First_Name,Last_Name,DOB
        321364060,User1,Micah,11/01/1969
        946497594,User2,Acker,05/28/1960
        887327716,User3,Aco,06/26/1950
        588496260,User4,John,05/23/1960
        565465465,User5,Jack,07/08/2020



    right.csv

        First_Name,Last_Name,DOB,City,Document_Type,Filename
        User1,Micah,11/01/1969,Parker,Transcript,T4IJZSYO.pdf
        User1,Micah,11/01/1969,Parker,Letter,BADBADC.pdf
        User1,Micah,11/01/1969,Parker,Resume,AJMLMOC.pdf
        User2,Acker,05/28/1960,,Transcript,R4IKTRYN.pdf
        User3,Aco,06/26/1950,,Transcript,R4IKTHMK.pdf
        User4,John,05/23/1960,,Letter,R4IKTHSL.pdf



    I need to match them on First_Name,Last_Name,DOB then return Ref_ID from the left.csv and First, Last, DOB, Document_Type,Filename from the right.csv for each row.

    So the end Result would look like this:

    Combined.csv

        Ref_ID,First_Name,Last_Name,DOB,Document_Type,Filename
        321364060,User1,Micah,11/01/1969,Transcript,T4IJZSYO.pdf
        321364060,User1,Micah,11/01/1969,Letter,BADBADC.pdf
        321364060,User1,Micah,11/01/1969,Resume,AJMLMOC.pdf
        946497594,User2,Acker,05/28/1960,Transcript,R4IKTRYN.pdf
        887327716,User3,Aco,06/26/1950,Transcript,R4IKTHMK.pdf
        588496260,User4,John,05/23/1960,Letter,R4IKTHSL.pdf
    


    This code is close:

    $left = Import-Csv C:\left.csv
    $right = Import-Csv C:\right.csv
    
    Compare-Object -ReferenceObject $left -DifferenceObject $right -Property First_Name,Last_Name,DOB -IncludeEqual -ExcludeDifferent | 
        ForEach-Object {
            $iItem = $_
            $ileft = $left.Where({$_.First_Name -eq $iItem.First_Name -and $_.Last_Name -eq $iItem.Last_Name -and$_.DOB -eq $iItem.DOB})
            $iright = $right.Where({$_.First_Name -eq $iItem.First_Name -and $_.Last_Name -eq $iItem.Last_Name -and$_.DOB -eq $iItem.DOB})
            [pscustomobject]@{
                Ref_ID=$ileft.Ref_ID
                first_name=$ileft.first_name
                last_name=$ileft.last_name
                DOB=$ileft.DOB
                Document_Type=$iright.Document_Type
                Filename=$iright.Filename
            }
        } | Format-Table
    But the multiple matches are grouped together in the same row, like below:
        Ref_ID,First_Name,Last_Name,DOB,Document_Type,Filename
        321364060,{User1,User1,User1},{Micah,Micah,Micah},{11/01/1969,11/01/1969,11/01/1969},{Transcript,Letter,Resume},{T4IJZSYO.pdf,BADBADC.pdf,AJMLMOC.pdf}

    The query below works to get me each individual results, but i don't know how to add the REF_ID i need from the left.csv

        $right.Where( {$_.DOB -in $left.DOB -and $_.First_Name -in $left.First_Name -and $_.Last_Name -in $left.Last_Name}) | 
        export-csv C:\Combined.csv -notypeinformation



    Results: It allows me to do -in, It excluded User5, since it doesnt match, but i need the REF_Id returned

        First_Name,Last_Name,DOB,City,Document_Type,Filename
        User1,Micah,11/01/1969,Parker,Transcript,T4IJZSYO.pdf
        User1,Micah,11/01/1969,Parker,Letter,BADBADC.pdf
        User1,Micah,11/01/1969,Parker,Resume,AJMLMOC.pdf
        User2,Acker,05/28/1960,,Transcript,R4IKTRYN.pdf
        User3,Aco,06/26/1950,,Transcript,R4IKTHMK.pdf
        User4,John,05/23/1960,,Letter,R4IKTHSL.pdf



    I feel like i am just missing an operator that would put the results the way i need/want but I tried adding -join or -split into pscustomobject but with no luck, or a count+1 so it loops through those, or a nested foreach-object, really at a loss.

     
    Tuesday, September 11, 2018 6:34 PM

Answers

  • Quick solution:

    $left = Import-Csv .\left.csv
    $right = Import-Csv .\right.csv
    
    $right | % { 
        $r = $_; 
        $left | ? { $_.First_Name -eq $r.First_Name -and $_.Last_Name -eq $r.Last_Name -and $_.DOB -eq $r.DOB } | 
            select Ref_Id, 
                First_Name, 
                Last_Name, 
                DOB, 
                @{Name="City";Expression={$r.City}}, 
                @{Name="Document_Type";Expression={$r.Document_Type}}, 
                @{Name="FileName";Expression={$r.FileName}}
    } | ft

    Tuesday, September 11, 2018 7:14 PM

All replies

  • Quick solution:

    $left = Import-Csv .\left.csv
    $right = Import-Csv .\right.csv
    
    $right | % { 
        $r = $_; 
        $left | ? { $_.First_Name -eq $r.First_Name -and $_.Last_Name -eq $r.Last_Name -and $_.DOB -eq $r.DOB } | 
            select Ref_Id, 
                First_Name, 
                Last_Name, 
                DOB, 
                @{Name="City";Expression={$r.City}}, 
                @{Name="Document_Type";Expression={$r.Document_Type}}, 
                @{Name="FileName";Expression={$r.FileName}}
    } | ft

    Tuesday, September 11, 2018 7:14 PM
  • Hashtables can make this very easy,

    $left = Import-Csv C:\left.csv | ForEach-Object -Process {@{$_.First_Name=$_.Ref_ID}}
    $right = Import-Csv C:\right.csv
    
    $right | Select-Object -Property *,@{E={$left.($_.'First_Name')};L='Ref_Id'}
    


    Regards kvprasoon

    Tuesday, September 11, 2018 7:43 PM
  • Why is this so hard?  Just use Compare-Object.

    $left = @'
    Ref_ID,First_Name,Last_Name,DOB
    321364060,User1,Micah,11/01/1969
    946497594,User2,Acker,05/28/1960
    887327716,User3,Aco,06/26/1950
    588496260,User4,John,05/23/1960
    565465465,User5,Jack,07/08/2020
    '@ | ConvertFrom-Csv
    
    $right = @'
    First_Name,Last_Name,DOB,City,Document_Type,Filename
    User1,Micah,11/01/1969,Parker,Transcript,T4IJZSYO.pdf
    User1,Micah,11/01/1969,Parker,Letter,BADBADC.pdf
    User1,Micah,11/01/1969,Parker,Resume,AJMLMOC.pdf
    User2,Acker,05/28/1960,,Transcript,R4IKTRYN.pdf
    User3,Aco,06/26/1950,,Transcript,R4IKTHMK.pdf
    User4,John,05/23/1960,,Letter,R4IKTHSL.pdf
    '@ | ConvertFrom-Csv
    
    Compare-Object $right $left -Property First_Name,Last_Name,DOB -IncludeEqual -PassThru
    


    \_(ツ)_/



    • Edited by jrv Tuesday, September 11, 2018 8:06 PM
    Tuesday, September 11, 2018 7:59 PM
  • Now add - Group-Object

    Compare-Object $left $right -Property Last_Name,First_Name,DOB -IncludeEqual -PassThru |
        Group-Object Last_Name,First_Name,DOB
    

    Next add ForEach to combine elements of each group:

    Compare-Object $left $right -Property Last_Name,First_Name,DOB -IncludeEqual -PassThru |
        Group-Object Last_Name,First_Name,DOB |
        ForEach-Object{
            #  combine all elements as needed
            $obj = [PSCustomObject}@{
                # properties to keep
            }
            foreach($item in $_.Group){
                add items to obejct
            }
            $obj      
        }
    
    


    \_(ツ)_/

    Tuesday, September 11, 2018 8:12 PM
  • This is giving me mutiple ref id per a row, im confused on why i would want First_Name=Ref_ID? The one CSV has Ref_Id, First, Last, Dob, the other csv does not have Ref_ID, but I need it added to the final output.
    Wednesday, September 12, 2018 1:33 PM
  • I have not used the Group-object piece before. I added the pscustomobjects like so:

                Ref_ID=$left.Ref_ID
                first_name=$left.first_name
                last_name=$left.last_name
                DOB=$left.DOB
                Document_Type=$right.Document_Type
                Filename=$right.Filename

    But I am not sure what to add to the foreach($item in $_,Group)

    Everything i have tried just brings back all the results in each column or it only brings back the items from the $left column.

    $item.     only gives me Ref_Id, First_Name, Last_Name, DOB


    Wednesday, September 12, 2018 1:48 PM
  • This answer is perfect. any downside or problems with using this solution?

    Im trying to figure out what your answer does different than the one i was trying. what does $r = $_; do?

    if i replace $r with $right it works the same, so i guess what does = $_;

    part do?
    • Edited by moore1unc Wednesday, September 12, 2018 2:26 PM
    Wednesday, September 12, 2018 2:16 PM