4

I have the following script that parses an HR output file looking for employees and outputs information such as Hire Dare, First Name, Last Name, Supervisor etc.

The problem I have is that in the current format I think the Hire Date column is being treated as a string so in effect it orders the output by month (i.e. 1/1/01 comes before 2/2/98). Is there a way to map that column to a date/time so it sorts properly?

Import-CSV -delimiter "`t" Output.tab | Where-Object {$_.'First Nae' -like '*And*'} | Sort-Object 'Hire Date' | ft 'Hire Date', 'First Name'

3 Answers 3

4

You can add a new (DateTime typed) field to your CSV in-line using the value from Hire Date to seed it, and then use that for your sorting. You can still print the standard Hire Date though.

This should work for you:

Import-CSV -delimiter "`t" Output.tab | Where-Object {$_.'First Name' -like '*And*'} | add-member scriptproperty -name 'HireDateTyped' -value { [DateTime]::Parse($this.'Hire Date') } -passthru | Sort-Object 'HireDateTyped' | ft 'Hire Date', 'First Name'

Also, you have a typo in the Where-Object clause, First Name is misspelled.

2

sort by expression (and type cast the hire date column}:

... | Sort-Object { [datetime]$_.'Hire Date' } | ...

1
  • I prefer this answer over the accepted answer as it doesn't add a new column to the source object, so that when you Export-Csv, it doesn't add a duplicate column.
    – HipCzeck
    Sep 15, 2011 at 20:05
1

You need to type cast each object in the CSV file for each property to the type of object you want. In this case, you want the DateTime object set for this column.

It would look something like this:

$data = import-csv C:\report.csv
$data | foreach-object {
New-Object PSObject -prop @{
"Hire Date" = [DateTime]::Parse("$_.Hire Date")
}
}

Good article on how to do this...

http://www.heikniemi.net/hardcoded/2010/01/powershell-basics-1-reading-and-parsing-csv/

Hope this helps!

1
  • 1
    Caveat with this method - you need to "remap" each of your properties within the foreach loop, otherwise you will lose them. In addition to the Hire Date stuff, you need to add a line for the other fields as well: 'First Name' = $_.'First Name' - this is done in the linked article but not really highlighted as a pitfall.
    – MattB
    May 14, 2010 at 15:57

You must log in to answer this question.