Powershell - Formatting column as date-CodePudding

I am importing a CSV which has 5 "columns". One of them is a date and time. The 3rd party software that is reading this column, then does not sort the date well.

IE: (4/8/2022 1:24:08 PM) will sort above (4/13/2022 8:51:52 AM)

Even though 4/13 is after 4/8 it will not sort it properly. I would like to add a leading zero in front of the month and date with powershell. I did do some searching but nothing seems to make sense to me, I am not a HUGE programmer.

Thanks for any help!

This is what I am currently doing. I am using unique to remove duplicate rows (this is needed for what I am doing).

$FinalSessions = Import-Csv -Path "C:\Windows\AdminArsenal\UserSessions.csv"  | Sort-Object * -Unique
$FinalSessions | Export-Csv -Path "C:\Windows\AdminArsenal\UserSessions.csv"  -NoTypeInformation
$FinalSessions

CodePudding user response：

You can use Get-Date to actually get a datetime object and then reformat it. It would look something like this:

$FinalSessions = Import-Csv -Path "C:\Windows\AdminArsenal\UserSessions.csv"| Sort-Object * -Unique
$FinalSessions | % { $_.DateColumn = Get-Date $_.DateColumn -Format "MM/dd/yyyy hh:mm:ss tt" }
$FinalSessions | Export-Csv -Path 
"C:\Windows\AdminArsenal\UserSessions.csv"  -NoTypeInformation
$FinalSessions

Just replace "DateColumn" with the name of your column

CodePudding user response：

Assuming that the column that contains the date-time string is named Date (adjust as needed):

Import-Csv -Path C:\Windows\AdminArsenal\UserSessions.csv | 
  ForEach-Object { $_.Date = ([datetime] $_.Date).ToString('s') } | 
    Sort-Object * -Unique -OutVariable finalSessions |
      Export-Csv -Path C:\Windows\AdminArsenal\UserSessions.csv -NoTypeInformation

$finalSessions

Note that the s format specifier (in ISO 8601 format) is used to reformat the date-time strings, as that results in a string whose lexical sorting reliably indicates chronological order, across year boundaries; e.g. 2022-05-05T17:52:47