Issue sorting CSV input data by date using PowerShell

Question

I am trying to sort a collection of CSV files within a directory using PowerShell and then want to export the results to a new single CSV file.

My code executed without error but the the results are not sorted correctly.

My Source data looks like this (this is a sample for testing):

"TimeCreated","TargetUserSID","UserName","Domain","Message","Remark"
"04.12.2017 13:56:56","S-1-5-21-66666666-777777777-888888888-99999","User123","testlab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 13:56:48",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Workstation unlocked"
"04.12.2017 13:56:48",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Workstation unlocked"
"04.12.2017 13:56:34",,"User123","testlab.internal","Fehler beim Anmelden eines Kontos","Failed Login !"
"04.12.2017 10:29:49",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:29:49",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:27:27","S-1-5-21-66666666-777777777-888888888-99999","User123","testlab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"08.11.2017 09:24:58",,"","","Fehler beim Anmelden eines Kontos","Failed Login !"
"08.11.2017 09:23:14",,"","","Fehler beim Anmelden eines Kontos","Failed Login !"
"04.12.2017 10:25:35",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:25:35",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:23:20","S-1-5-21-66666666-777777777-888888888-99999","User123","testlab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 10:23:14","S-1-5-21-2025377670-2384379295-2560266631-1005","Admin","TestClientWin7","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 10:22:44",,"Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:44",,"Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:38",,"Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:38",,"Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:20:28",,"Admin","TestClientWin7","Fehler beim Anmelden eines Kontos","Failed Login !"
"01.12.2017 11:39:17",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"01.12.2017 11:39:17",,"User123","testlab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"

My result looks like this:

"TimeCreated","TargetUserSID","UserName","Domain","Message","Remark"
"01.12.2017 11:39:17","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"01.12.2017 11:39:17","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:20:28","","Admin","TestClientWin7","Fehler beim Anmelden eines Kontos","Failed Login !"
"04.12.2017 10:22:38","","Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:38","","Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:44","","Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:22:44","","Admin","TestClientWin7","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:23:14","S-1-5-21-66666666-777777777-888888888-99999","Admin","TestClientWin7","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 10:23:20","S-1-5-21-964984727-670836246-1844936127-10903","User123","tetslab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 10:25:35","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:25:35","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:27:27","S-1-5-21-964984727-670836246-1844936127-10903","User123","tetslab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"04.12.2017 10:29:49","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 10:29:49","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Interactive (logon at keyboard and screen of system)"
"04.12.2017 13:56:34","","User123","tetslab.internal","Fehler beim Anmelden eines Kontos","Failed Login !"
"04.12.2017 13:56:48","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Workstation unlocked"
"04.12.2017 13:56:48","","User123","tetslab.internal","Ein Konto wurde erfolgreich angemeldet","Workstation unlocked"
"04.12.2017 13:56:56","S-1-5-21-964984727-670836246-1844936127-10903","User123","tetslab.internal","Benutzerinitiierte Abmeldung","User initiated Logoff."
"08.11.2017 09:23:14","","","","Fehler beim Anmelden eines Kontos","Failed Login !"
"08.11.2017 09:24:58","","","","Fehler beim Anmelden eines Kontos","Failed Login !"

This is my code:

$SortingCSV = @()
Get-ChildItem "c:\temp\*.csv" | ForEach-Object{
    $SortingCSV += Import-Csv $_
}
$SortingCSV | Sort-Object TimeCreated | Export-Csv -NoTypeInformation -Force -Encoding UTF8 "c:\temp\sorted.csv"

It looks like its sorting alphabetically, you need to convert timecreated to a datetime object. — Mark Wragg
– Mark Wragg, Commented Feb 6, 2018 at 16:01

Ansgar Wiechers · Accepted Answer · 2018-02-06 16:06:22Z

5

The fields imported from the CSV are strings, so they're sorted in string order, not date order, so the sorting is absolutely correct, just not what you were expecting. You need to calculate a proper DateTime value for Sort-Object to work with to get the rows sorted in date order. I would also recommend not appending to an array in a loop.

$pattern = 'dd\.MM\.yyyy HH:mm:ss'
$culture = [Globalization.CultureInfo]::InvariantCulture

Get-ChildItem 'C:\temp\*.csv' |
    ForEach-Object { Import-Csv $_.FullName } |
    Sort-Object { [DateTime]::ParseExact($_.TimeCreated, $pattern, $culture) } |
    Export-Csv 'C:\temp\sorted.csv' -Encoding UTF8 -NoType -Force

answered Feb 6, 2018 at 16:06

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Peter Core Over a year ago

thank you for your contributions. excellent solution and description.

Mark Wragg · Accepted Answer · 2018-02-06 16:05:18Z

1

The issue occurs because the TimeCreated column is being sorted alphabetically due to be being interpreted as a string value. One way to handle this would be to convert it to a DateTime object. You can do this within the Select-Object cmdlet by using a calculated property:

$SortingCSV | Select -ExcludeProperty TimeCreated @{N='TimeCreated';E={Get-Date $_.TimeCreated}},* | Sort-Object TimeCreated | Export-CSV ..

The above excludes the TimeCreated property byt then recreates it with the same name by using Get-Date to return a date object for the string value it contains.

answered Feb 6, 2018 at 16:05

Mark Wragg

23.6k7 gold badges48 silver badges77 bronze badges

2 Comments

Ansgar Wiechers Over a year ago

This would work too, but has the disadvantage that the format of the TimeCreated column might get changed inadvertently (if the regional settings specify a different date/time format than what is present in the input data).

Mark Wragg Over a year ago

Yes be wary of regional variations with dates that will occur depending on where the script is run. Ansgar's answer is better in that regard as it makes the expected date pattern explicit.

Collectives™ on Stack Overflow

Issue sorting CSV input data by date using PowerShell

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related