Powershell - compare two csv - duplicates and adding column-CodePudding

I have a csv

TEST1.CSV

Users DHMS          
----- ----          
A     22/12/21 05:02:00
B     22/12/21 05:10:00
C     22/12/21 06:30:00
D     23/12/21 12:30:00
A     23/12/21 12:35:00
B     23/12/21 12:45:00
C     26/12/21 10:32:00
D     28/12/21 11:15:00
A     29/12/21 14:17:00
B     29/12/21 14:25:00

And a second one

TEST2.CSV

Users Code
----- ----          
A     1324
E     5643
G     2678
C     2325

I wanted to keep only Users duplicates with code columln added

RESULT.CSV

Users DHMS      Code             
----- ----      ----         
A     22/12/21 05:02:00 1324
C     22/12/21 06:30:00 2325
A     23/12/21 12:35:00 1324
C     26/12/21 10:32:00 2325
A     29/12/21 14:17:00 1324

I tried

$Path = "C:\Users\X\Downloads\Comparaison\" 
$TEST1 = Import-Csv -Path "$($path)\TEST1.csv" -Delimiter ";" 
$TEST2 = Import-Csv -Path> "$($path)\TEST2.csv" -Delimiter ";" 
Compare-Object -ReferenceObject $TEST1 -DifferenceObject $TEST2 -IncludeEqual -ExcludeDifferent -Property Users -PassThru | select Users,DHMS | Export-Csv -NoTypeInformation -Path "$($path)\RESULT.csv"

But I have only fist occurence for A and C and i don't know how to add the column "Code".

Thank you for yout help

CodePudding user response：

Start by using the second CSV file to build a table that maps the user key to the code:

$userCodeMap = @{}
Import-Csv -Path $path\TEST2.csv -Delimiter ";" |ForEach-Object {$userCodeMap[$_.Users] = $_.Code}

Now we can use Select-Object to add a new "column" based on the value of the Users column:

Import-Csv -Path $path\TEST1.csv -Delimiter ";" |Where-Object { $userCodeMap.Contains($_.Users) } |Select-Object Users,DHMS,@{Name='Code';Expression={$userCodeMap[$_.Users]}} |Export-Csv -NoTypeInformation -Path $path\RESULT.csv