Q:
Sorting columns with headers in csv
I have a csv file with a date in each row
2014-06-03 14:52:00
2014-06-03 21:17:12
2014-06-04 21:21:14
2014-06-04 17:28:09
I would like to display in this order : date, timestamp (in same order as date) (as timestamp appears in this format)
My actual command to display the dates works as I want
foreach ($date_time in Import-Csv D:\path\test.csv | sort-object {get-date $_.created_time}) {
write-host "$date_time"
}
How can I display timestamp with the same order as date ?
A:
The output of your pipeline will be in the default sort order for the string representations of those DateTime objects, which is lexical, not numeric. Since the DateTime strings don't have leading zeros, the numeric representation will be larger (or equal) to the alphanumeric representation.
That means that the dates are sorted lexicographically (i.e. by character value). That means the seconds are compared before the day of month, which are compared before the month and so on. The timestamp sorting works well for me, with both leading zeros and values like 50000 for milliseconds.
To get the output you want, you can do something like
$source | ForEach-Object {
$_
[datetime]::ParseExact($_.CreatedTime,"MM/dd/yyyy HH:mm:ss",$null)
} | Sort-Object -Property @{Expression={$_.Date.ToShortDateString()}}, CreatedTime
The Sort-Object call will order the resulting dates by Date.ToShortDateString() instead. That's a simple string comparison, so you'll get a lexical sort in that case.
Depending on the input data (and other code around the pipeline) you may want to use Add-Type (or similar) to create the [datetime] instances from their string representations before putting them into the pipeline.
A:
With your current example code the timestamp appears to be first, followed by the date. This is not so with a sample data set I just got. When sorting with Sort-Object it appears that the date is first.
Import-Csv has an -Append parameter to allow you to add a line to the end of an existing csv file. With this you could add the date column without it being the first line.
If you don't want to do this and are intent on displaying the column order in your script then you will need to do as in @PetSerAl's answer, using -Property to specify the property to sort on as well as the -CustomObject parameter to get the property's values.
Here's a quick and dirty sample:
$data = Get-Content D:\path\test.csv |
? {$_ -like '*Date*' -and $_.date -ne $null} |
? {$_ -like '*Timestamp*' -and $_.date -ne $null} |
? {$_ -like '*CreationTime*' -and $_.date -ne $null} |
ForEach-Object {
[PSCustomObject]@{
Date = $_.date
Time = $_.TimeOfDay
}
} |
Sort-Object -Property @{Expression={$_.Date.ToShortDateString()}},
CreatedTime
You can test with
$data |
? {$_ -like 'CreatedTime'} |
select date, time
A:
I don't think it's possible without some hacking. Here's how I managed to do it.
I'm not sure it's the best way, but it works (I think).
At first create a new custom object and put your date and your timestamp inside of it, like this:
[CustomObject]@{
Date = "05/01/2012 00:00:00",
Time = "06/05/2012 01:18:21"
}
Then use Select-Object and ConvertFrom-Csv to put your original data in it and sort by the date and the timestamp (using ConvertFrom-Csv means that the date column is not sorted by default, that's why I added it to the select-object):
$data = Get-Content D:\path\test.csv |
? {$_ -like '*Date*' -and $_.date -ne $null} |
? {$_ -like '*Timestamp*' -and $_.date -ne $null} |
? {$_ -like '*CreationTime*' -and $_.date -ne $null} |
Select-Object *, @{name="Date"; expression={$_.date}}, @{name="Time"; expression={$_.TimeOfDay}} |
ConvertFrom-Csv -Header CreatedTime
Then use sort-object to sort it:
$data | sort-object {[DateTime]$_.Date} -Property CreatedTime
Of course you can use a different property to sort by.
You can test it with:
$data |
? {$_ -like 'CreatedTime'} |
select date, time
The only problem is that I don't think it is possible to put the value of a CustomObject property inside the key of a hashtable.
A:
Since PowerShell 3.0 you can sort by the value of the object.
Sort-Object -Property value sorts by value of the property, not by name.
Sort-Object -Property @{propertyName = value}
So I would do this
Get-Content D:\path\test.csv |
? {$_ -like '*Date*' -and $_.date -ne $null} |
? {$_ -like '*Timestamp*' -and $_.date -ne $null} |
? {$_ -like '*CreationTime*' -and $_.date -ne $null} |
Select-Object *, @{Name = "Date"; Expression = {$_.Date}}, @{Name = "Time"; Expression = {$_.TimeOfDay}} |
Sort-Object @{Name = "Date"; Expression = {$_.Date}} -Property @{Name = "Time";
