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

Question

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.

Consider creating a hashtable from your data with the key as "$($_.First),$($_.Last),$($_.DOB)" and the value is the entire row. Then when you iterate you will have both keys and can parse / rejoin both values. Powershell is not the ideal vehicle to do this. — No Refunds No Returns, Sep 11 '18 at 18:41

score 1 · Accepted Answer · answered Sep 11 '18 at 19:08

1

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

answered Sep 11 '18 at 19:08

iRon

20,463
10
53
79

i have tried and i cant get the join script to work. i get no results, i dont know what I am missing. Do you drop this all in a ps1 file and then run your script on it? Im used to using module and cmdlets without issue. – moore1emu Sep 11 '18 at 19:10
You can use [`dot sourcing`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_scripts?view=powershell-6#script-scope-and-dot-sourcing) to include the script. E.g.: `. .\Join.ps1` – iRon Sep 11 '18 at 20:18
I installed the latest Version of the script and I just tried the dot sourcing and im getting this error: Join-Object : Cannot process argument transformation on parameter 'Property'. Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Collections.Hashtable". – moore1emu Sep 11 '18 at 20:26
I don't think you have the latest version. The latest version is [`2.2.4`](https://www.powershellgallery.com/packages/Join/2.2.4/DisplayScript). This version specifically supports a list of properties. – iRon Sep 11 '18 at 20:33
after removing the -property part i finally got it working and putting in | Select-Object i was able to return everything i ended. – moore1emu Sep 11 '18 at 20:33
you are correct, i updated to the latest version and now its working – moore1emu Sep 11 '18 at 20:36
how do i use csv files instead of the array you have posted? – moore1emu Sep 11 '18 at 20:42
See: [Import-Csv](https://learn.microsoft.com/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6), e.g.:`(Import-Csv .\Left.csv) | LeftJoin (Import-Csv .\Right.csv)...)` – iRon Sep 11 '18 at 20:47
Thanks, I got it working, had to add an alias for one of my headers in the real file. thank you for the help. glad I got it working – moore1emu Sep 11 '18 at 20:50

score 0 · Answer 2 · answered Sep 12 '18 at 14:20

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

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

2 Answers2