Importing from CSV and sorting by Date

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'

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.

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

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

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!