Comparing cells in two CSV files using powershell

Question

Pretty new to this. I need to compare a column with usernames from one CSV file, against a column with usernames in another CSV file and output if duplicates exists or remove them.

How they currently look:

$File1:

Firstname,Lastname,Username
Peter,Larsen,Larsen.123
Pia,Jensen,Jensen.123
Poul,Hansen,Hansen.123

$File2:

Username
Larsen.123
Andersen.123
Jensen.123

What I'm looking for:

Firstname,Lastname,Username,Duplicate
Peter,Larsen,Larsen.123,True
Pia,Jensen,Jensen.123,True
Poul,Hansen,Hansen.123,False

It doesn't necessarily have to be a true/false output, removing all duplicate entries from File1 or similar, would be perfectly fine as well. It's essentially just to compare users in our AD against users in our user database, to see if anyone still have access who shouldn't have.

Edit:

Tried with this so far, found in a similar question in here:

foreach ($user in $File1) {
    $MatchAccount = (Compare-Object $File2 $user -Property 'Username' -PassThru).Value
    $user = Add-Member -InputObject $user -Type NoteProperty -Name 'Value' -Value $MatchAccount

And just importing and exporting the CSV files before and after, in that order.

What coding have you tried so far? Welcome to Stackoverflow, please check the Help section for details about how to ask a solid question. Your question is great except just needs details of your attempted code so far — Grantly
– Grantly, Commented Dec 7, 2017 at 14:46

Ansgar Wiechers · Accepted Answer · 2017-12-07 16:28:17Z

1

Read the second file into a hashtable:

$users = @{}
Import-Csv 'file2.csv' | ForEach-Object {
    $users[$_.Username] = $true
}

Then check if each row of the first CSV contains a username from that hashtable and add a field with the result of that check:

(Import-Csv 'file1.csv') |
    Select-Object *,@{n='Duplicate';e={$users.ContainsKey($_.Username)}} |
    Export-Csv 'file1.csv' -NoType

answered Dec 7, 2017 at 16:28

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

Comments

Guenther Schmitz · Accepted Answer · 2017-12-08 17:34:21Z

0

here is my approach:

# import csv file1
$file1 = Import-Csv -Path C:\tmp\file1.csv

# import csv file2 and select column "Username"
$file2 = Import-Csv -Path C:\tmp\file2.csv | % {$_.Username}

# create output/result hash table
$file3 = @()

# go through entries in file1
foreach($entry in $file1) {
    # check if entry is in not in file2
    if($file2 -notcontains $entry.Username) {
        # add entry to output hash table
        $file3 += $entry
    }
}

# export result to csv
$file3 | Export-Csv -Path C:\tmp\file3.csv -NoTypeInformation

file3.csv will then have the following contents

Firstname,Lastname,Username
Poul,Hansen,Hansen.123

Best regards Guenther

answered Dec 8, 2017 at 17:34

Guenther Schmitz

2,0191 gold badge13 silver badges23 bronze badges

Collectives™ on Stack Overflow

Comparing cells in two CSV files using powershell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related