Compare 2 csv users list in powershell and create on csv file with all the differences

Question

I'm stuck with my code, could anyone help me ? I got to csv file like :

Yesterday.csv :

email,UPN
Joe.Jones@test.ch,JoeJ
Mark.Miller@test.ch,MarkM
Bob.Brown@test.ch,BobB
Frank.Funk@test.ch,FrankF
Roger.Fed@test.ch,RogerF

Today.csv :

email,UPN
Joe.Jones@test.ch,JoeJ
Mark.Miller@test.ch,MarkM
Frank.Funk@test.ch,FrankF
Roger.Federer@test.ch,RogerF

Code:

$yesterday = Import-Csv C:\Tools\Scripts\yesterday.csv
$today = Import-Csv C:\Tools\Scripts\today.csv

$users = @()

foreach ($u in $today) {
    $u2 = $yesterday | Where-Object { $_.email -ne $u.email -and $_.UPN -eq $u.UPN } | Select email, UPN -First 1 
    if ($u2)
    { $users += $u2 } 
}

$users | Export-Csv -Path C:\Tools\Scripts\csv3.csv -NoTypeInformation -Force

I want a third csv file that contain email and UPN where that the email address from today has changed from yesterday (and keep yesterday) and also where the user don't appears in today (could be erased)

The result I have is :

"email","UPN"
"Roger.Federer@test.ch","RogerF"

What I want :

"email","UPN"
"Bob.Brown@test.ch","BobB"
"Roger.Federer@test.ch","RogerF"

I tried to add : `foreach($y in $yesterday) { $u3 = $today |Where-Object {$_.UPN -notcontains $y.UPN}|Select email,UPN -First 1 if($u3) { $users += $u3 } }` — Guillaume vM M, Jun 22 '21 at 13:27
If you don't want to reinvent the wheel and performance matters, you might want to use this [`Join-Object script`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `Install-Module -Name JoinModule`; `$Yesterday |FullJoin $Today -On UPN -Name yesterday, today |Where { $_.yesterdayemail -ne $_.todayemail }` — iRon, Jun 22 '21 at 16:49

Santiago Squarzon · Answer 1 · 2021-06-22T18:50:25.280

You can use something like this. Note, I'm hardcoding the CSVs, you should use Import-Csv pointing to your CSVs instead. I'm also using ConvertTo-Csv to display the results here, you should use Export-Csv instead.

Lastly, the $map hashtable can throw an error in case there are 2 or more users with the exact same UPN in your Csv.

$yesterday = @'
email,UPN
Joe.Jones@test.ch,JoeJ
Mark.Miller@test.ch,MarkM
Bob.Brown@test.ch,BobB
Frank.Funk@test.ch,FrankF
Roger.Fed@test.ch,RogerF
'@ | ConvertFrom-Csv

$today = @'
email,UPN
Joe.Jones@test.ch,JoeJ
Mark.Miller@test.ch,MarkM
Frank.Funk@test.ch,FrankF
Roger.Federer@test.ch,RogerF
'@ | ConvertFrom-Csv

$map = @{}
$yesterday.ForEach({
    $map.Add($_.UPN,$_.email)
})

$(

    # Yesterday CSV where the UPN does not exist in Today CSV
    $yesterday | Where-Object { $_.UPN -notin $today.UPN }
    
    # Today CSV where UPN exists in Yesterday CSV and
    # email is different from yesterday email
    $today | Where-Object {
        $_.UPN -in $yesterday.UPN -and $_.email -ne $map[$_.UPN]
    } 

) | ConvertTo-Csv -NoTypeInformation

Output

"email","UPN"
"Bob.Brown@test.ch","BobB"
"Roger.Federer@test.ch","RogerF"

Compare 2 csv users list in powershell and create on csv file with all the differences

1 Answers1

Output