Powershell - compare two csv - duplicates and adding column

Question

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

Does this answer your question? [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026): `Import-Csv .\Test1.csv |Join (Import-Csv .\Test2.csv) -On Users` — iRon, Apr 13 '22 at 10:52

score 2 · Accepted Answer · answered Apr 13 '22 at 10:47

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

Thank you for your help Mathias R. Jessen :) – Marmotte73 Apr 13 '22 at 11:43 — Marmotte73, Apr 13 '22 at 11:43

Powershell - compare two csv - duplicates and adding column

1 Answers1