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?: Import-Csv .\Test1.csv |Join (Import-Csv .\Test2.csv) -On Users — iRon
– iRon, Commented Apr 13, 2022 at 10:52

Mathias R. Jessen · Accepted Answer · 2022-04-13 10:47:25Z

2

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

answered Apr 13, 2022 at 10:47

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marmotte73 Over a year ago

Thank you for your help Mathias R. Jessen :)

Collectives™ on Stack Overflow

Powershell - compare two csv - duplicates and adding column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related