Sorting CSV table in Powershell

Question

I'm wondering if some PS gurus can advise on whether my method for sorting and recombining some data fields by using an array of PSCustomObjects is as efficient as it might be.

I'm importing a CSV file that contains a number of person details, including Full Names in a single field that have a variety of separators between first and last names (e.g. comma, space, comma + space, semicolon, etc). Last name comes first. Sanitising those variants is also an objective.

The dataset needs to be sorted by last name and reassembled with a single FullName field. Doing a simple sort by the FullName field doesn't work reliably given the variety of delimiters between the name parts.

I've attempted it creating an array of new PSCustomObjects that comprises of all the original fields from the CSV plus the Full Name field split into two new name fields. It's then sorted by the last name, and the results are stored in a new array ready for output, joining the previously-split name fields back together with a comma and space.

Any suggestions for better efficiency welcome.

$data = import-csv .\Cust.csv
# CSV fields: FullName, StreetAddress, City, Postcode
# Create a temporary array for sorting
$splitNames = @()
# Split "FullName" into separate name parts and add all to temp array
$data | foreach {
    $spltName = $_.FullName -split '[\s|,|;]+'
    $splitNames += [PSCustomObject]@{
        Last = $spltName[0]
        First = $spltName[1]
        StreetAddress = $_.StreetAddress
        City = $_.City
        Postcode = $_.Postcode
    }
}
# Create an output array to hold the final result
$sortedNames = @()
# Sort the temporary array by lastname, rejoin name field and add to output array
$splitNames | Sort Last | foreach {
    $FullName = $_.Last, $_.First -join ', '
    $sortednames += [PSCustomObject]@{
        FullName = $Fullname
        StreetAddress = $_.StreetAddress
        City = $_.City
        Postcode = $_.Postcode
    }
}
$sortednames

Sample data - first row is the header:

"FullName","StreetAddress","City","Postcode"
"Bloggs,Joe","1 Some Street","City","1001"
"Bloggs Jane","1 Some Street","City","1001"
"Bloggs;Jill","1 Some Street","City","1001"
"Bloggs, Jo","1 Some Street","City","1001"

Answer 1

I'm glad we established in the comments that your code doesn't in fact run slow. When you use the word "efficiency", most people would take that to mean run-time speed (or space, depending on the context).

You should never optimize for efficiency unless there is a demonstrated lack of efficiency that has a negative impact on humans beings. Optimize for humans, not for computers. Computers don't care.

In this case, if I had gone down the path of trying to make it run faster, we would probably have ended up with less readable, less maintainable code.

We can certainly optimize for readability or style though, as you pointed out in the comments.

I should say first that I don't have a problem with your code. I think it's fine.

(This is one bug, however. Instead of Sort Last, you should do Sort Last,First. "Bloggs, Jane" should come before "Bloggs, Joe" in the output.)

I've rewritten the code below how I would personally write it. There are a couple of techniques that you may not be familiar with that can be very useful.

One is "capturing groups" in regular expressions. E.g. (?<first>.+), that matches a group named "first", and we can get the captured text with this: $matches['first']

($matches is a "magic variable" defined by PowerShell, if you are wondering.)

Another technique is "calculated properties" with Select-Object. You can make properties by supplying a property name and a script block that calculates the value. That is often more straightforward than creating objects with [PSCustomObject].

# E.g. "Bloggs;Joe" ==> "Bloggs, Joe"
function NormalizeName($fullName)
{   
    $separators = '\s|,|;'
    if ($fullName -match "^(?<last>[^$separators]+)[$separators]+(?<first>.+)")
    {
        $last  = $matches['last']
        $first = $matches['first']
        "$last, $first"
    }
    else 
    {
        # Doesn't match, so return unchanged.
        $fullName    
    }
}

Import-Csv .\Cust.csv |
    Select-Object @{name="FullName"; exp={NormalizeName $_.FullName} }, StreetAddress, City, Postcode |
    Sort-Object FullName