# Sorting CSV table in Powershell

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"

• Please post a sample CSV file with just a few records. Use fake data. Sep 17, 2018 at 8:32
• Sorry for the delay - I've appended a sample chunk of raw CSV similar to what will be processed
– LeeM
Sep 20, 2018 at 6:24
• I will take a look a bit later. But first I have to ask, is it in fact slow? How long does it take to run, and how big are the CSV files? Sep 20, 2018 at 13:32
• It's not slow at all - it takes ~20 sec to run over a typical data set. But I just wanted to check from a stylistic point of view whether there's a more elegant/better way of doing it. If there's no better way to do it, great! Thanks for looking at it. :-)
– LeeM
Sep 21, 2018 at 1:04
• Oops, I wrote a reply a few days ago, but I had it in "deleted" mode while I was doing some edits to it, and I forgot to undelete it. It is there now. Sep 24, 2018 at 13:19

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

• Thanks. I did mean "efficiency" in the usual sense. If it takes ~20s to run the code, but it'd take ~10s if I didn't use so many arrays of custom objects or expensive regexes, that's the kind of feedback I was looking for. I'm pretty comfortable with regexes in general and in Powershell, so the approach of using a regexe to sanitise and reconstruct the FullName first, and then sorting makes perfect sense. I wouldn't have thought of running it thru the pipeline using an expression to call a function, so thanks for that!
– LeeM
Sep 26, 2018 at 6:22