CODEWITHSUNDEEP
powershellcsv
moore1emu
moore1emu

Reputation: 496

Compare 2 Csvs on mutiple columns, export all matching rows including duplicates values, export columns from both csvs with powershell

Powershell-CompareCsv-Export-Values-From-Each I posted that question and its pretty much identical, except that my right CSV has multiple Rows with the same First, Last, and DOB, but different file names and the my Left File only has the First, Last, DOB one time.

I think the answer will be different enough that I decided to post a second 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

Using the answer from the other post, only returns the first match. I tried changing -eq to -in, which gives me all the matches, but that returns it like this:

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.

Upvotes: 0

Views: 177

Answers (2)

moore1emu
moore1emu

Reputation: 496

Giving the Answer to @IRon, but i found a solution that does not require a separate script and thought i would post it for future reference.

$left = Import-Csv .\left.csv
$right = Import-Csv .\right.csv

$right | foreach { 
    $r = $_; 
    $left | where{ $_.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}}
} | format-table

Upvotes: 0

iRon
iRon

Reputation: 23788

As in my answer for Powershell-CompareCsv-Export-Values-From-Each, using Join-Object:

$Left = ConvertFrom-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
'@

$Right = ConvertFrom-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
'@

Note that I have swapped $Left and Right. Although it is also possible to do a RightJoin,
I recommend to use a LeftJoin over a RightJoin from a PowerShell streaming perspective

$Left | LeftJoin $Right `
    -On First_Name,Last_Name,DOB `
    -Property Ref_ID,First_Name,Last_Name,DOB,Document_Type,Filename `
    | Format-Table

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

Upvotes: 1

Related Questions