Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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'
share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer

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!

share|improve this answer
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 '10 at 15:57

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

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

share|improve this answer
    
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 '11 at 20:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.