Comparing values with Compare-Object

Question

Have a bunch of values from csv file, with column looking like this:

shouston
cgonzalez
bbrown
hlader
kpesavento
jbloom
polson
bcharlow
bcharlow
bkalt

Need to find duplicates and modify them.

# Grab CSV file
$inputFile = Import-Csv -Path $filePath
$text = (Get-Culture).TextInfo
$HashSet = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)

foreach ($line in $inputFile) {
    $name = $line.name
    $line.name = $text.ToTitleCase($name)
    $firstName = $line.name.split(" ")[0]
    $lastName = $line.name.split(" ")[1]
    $newEmail = ($firstName[0] + $lastName).toLower()

    if (!$HashSet.Add($newEmail)) {
        $line.email = ($firstName[0] + $lastName + $line.location_id + "@abc.com").toLower()
    }
    else {
        $line.email = ($firstName[0] + $lastName + "@abc.com").toLower()
    }
} 

$inputFile | Export-Csv $fullPath

But its not working as I expected - find a duplicate values and modify them. In my case, the location must also be added to the first duplicate email, example bcharlow1 bcharlow2, not it's bcharlow, bcharlow2. Can anyone help me - how I need to modify script?

Answer 1

You can use Group-Object to see the duplicates, so doing something like :

$val=@(
'**name**',
'shouston',
'cgonzalez',
'bbrown',
'hlader',
'kpesavento',
'jbloom',
'polson',
'bcharlow',
'bcharlow',
'bkalt'
)

$val | Group-Object

Will give you a count of how many times the given value is been seen. If you want to see lets say the top 3 you can pipe it to sort and select

$val | Group-Object -NoElement | Sort-Object Count -Descending | Select-Object -First 3

Count Name                     
----- ----                     
    2 bcharlow                 
    1 **name**                 
    1 shouston 

Answer 2

Probably the easiest (and fastest) way to do this, is using a HashSet:

$InputFile = ConvertFrom-Csv @'
id, location_id, name,                 title,                              email, department
 1,           1, "Susan houston",      DS,                                      ,
 2,           1, "Christina Gonzalez", D,                                       ,
 3,           2, "Brenda brown",       "Director, Second Career Services",      ,
 4,           3, "Howard Lader",       "Manager, Senior Counseling",            ,
 5,           4, "Kimberly Pesavento", "Commercial director",                   ,
 6,           5, "Joe Bloom",          Finance,                                 ,          7
 8,           6, "Bart charlow",       Director,                                ,
 9,           7, "Bart Charlow",       Director,                                ,
'@

$Emails = [System.Collections.Generic.HashSet[string]]::new([System.StringComparer]::OrdinalIgnoreCase)

foreach ($line in $inputFile) {
    $name = $line.name
    $line.name = (Get-Culture).TextInfo.ToTitleCase($name)
    $firstName = $line.name.split(" ")[0]
    $lastName = $line.name.split(" ")[1]
    $newEmail = ($firstName[0] + $lastName).toLower()

    if (!$Emails.Add($newEmail)) {
        $line.email = ($firstName[0] + $lastName + $line.location_id + "@abc.com").toLower()
    }
    else {
        $line.email = ($firstName[0] + $lastName + "@abc.com").toLower()
    }
}

$InputFile |ConvertTo-Csv

"id","location_id","name","title","email","department"
"1","1","Susan Houston","DS","[email protected]",""
"2","1","Christina Gonzalez","D","[email protected]",""
"3","2","Brenda Brown","Director, Second Career Services","[email protected]",""
"4","3","Howard Lader","Manager, Senior Counseling","[email protected]",""
"5","4","Kimberly Pesavento","Commercial director","[email protected]",""
"6","5","Joe Bloom","Finance","[email protected]","7"
"8","6","Bart Charlow","Director","[email protected]",""
"9","7","Bart Charlow","Director","[email protected]",