CODEWITHSUNDEEP
powershell
MFTP
MFTP

Reputation: 13

Comparing cells in two CSV files using powershell

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.

Upvotes: 0

Views: 1146

Answers (2)

Guenther Schmitz
Guenther Schmitz

Reputation: 1999

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

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

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

Upvotes: 1

Related Questions