North East Bytes - a Microsoft technology usergroup in North East England.

Powered by Squarespace

« First taste of FIM | Main | Locating AD Computer Objects with PowerShell »
Tuesday
Oct252011

Casting dates on the fly to sort with PowerShell

Yesterday I was approaced to help someone sorting some data using PowerShell. The data was in a CSV file with one of the fields, called "date", containing a date & time that the data was going to be sorted on. Some of the dates were empty, but all of the ones that were populated were valid. The problem was that doing this didn't work as expected:

Import-Csv file.csv | Sort-Object date

That's because the objects being created and passed along the pipeline by Import-Csv contained the date as a string.

Now if you were going to do a lot of things with this data, you might want to be explicit about creating an object and importing the data into it, but in this case we were just sorting it and exporting to another CSV file, so there's no need to go to that trouble.

The Sort-Object cmdlet doesn't just accept properties as the parameter to sort by, you can give it a PowerShell script block. That means that as each object comes into Sort-Object, you can do things like casting types before they're sorted. In this case it was as simple as:

Import-Csv file.csv | Sort-Object {$_.date -as [datetime]}

As I said before, some of those dates were empty, so they came out of the sort at the top, above the earliest proper date. That may be what you want, or it may not, but this makes it easy to do something about that. Say you wanted to set all the empty ones to the current date/time, your script block could look like this:

{Switch ($_.date){"" {Get-Date}}; $_.date -as [datetime]}

Just think of the possibilities!

PrintView Printer Friendly Version

EmailEmail Article to Friend

References (6)

References allow you to track sources for this article, as well as articles that were written in response to this article.
  • Response
  • Response
    Response: haaruitval
    Casting dates on the fly to sort with PowerShell - Blog - jonoble.com
  • Response
    Response: kijk de video
    Casting dates on the fly to sort with PowerShell - Blog - jonoble.com
  • Response
    Casting dates on the fly to sort with PowerShell - Blog - jonoble.com
  • Response
    Response: afslankpillen
    Casting dates on the fly to sort with PowerShell - Blog - jonoble.com
  • Response
    Response: earn money online
    Casting dates on the fly to sort with PowerShell - Blog - jonoble.com

Reader Comments (2)

thanks for the tip !
just for another example , as I was parsing a xml file
I needed to sort It on a version number. And as you may know you can't cast a node value except from a string type.
So it would give :

$updates.SelectNodes("/Packages/Package") | Sort-Object {$_.version -as [version]}

And BOOM sorted by major, minor, build, and Revision, order.
This is awesome.

September 16, 2013 | Unregistered Commenterdoctori

Thanks a lot, I could not find this solution anywhere else

December 12, 2013 | Unregistered CommenterRavi

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>