Fix dates in CSV files using powershell, convert to UK format and add second if missing-CodePudding

I have the below csv data (sample), as you can see the date is in US format (MM/dd/yyyy) and sometimes the seconds (:ss) is missing. I would like to fix it so the date is in UK format (dd/MM/yyyy) and if the seconds is missing in the time, then add :00

Time,Incident
03/25/2021 12:59:49,INC18072411
04/03/2021 13:00,INC17989469
05/14/2021 13:01:57,INC17987450
05/28/2021 13:02:56,INC18000995
06/01/2021 13:06,INC17990104
06/06/2021 13:06:23,INC17983804
07/19/2021 13:07,INC17973858
08/08/2021 13:08:04,INC17987287
09/20/2021 13:08:19,INC17986031
10/10/2021 13:13,INC17926602

I have tried the below

Import-Csv $CsvPath | ForEach-Object { ([datetime]::ParseExact($_.Time,"MM/dd/yyyy HH:mm:ss",$null)).ToString('dd/MM/yyyy H:mm:ss') } | Export-Csv $CsvUpdatedPath -NoTypeInformation

but get error on records where the seconds is missing:

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."

CodePudding user response：

Using regex you could add the missing seconds before parsing the input string to a [DateTime] type.

$InputData = @'
Time,Incident
03/25/2021 12:59:49,INC18072411
04/03/2021 13:00,INC17989469
05/14/2021 13:01:57,INC17987450
05/28/2021 13:02:56,INC18000995
06/01/2021 13:06,INC17990104
06/06/2021 13:06:23,INC17983804
07/19/2021 13:07,INC17973858
08/08/2021 13:08:04,INC17987287
09/20/2021 13:08:19,INC17986031
10/10/2021 13:13,INC17926602
'@ |
ConvertFrom-Csv

$InputData |
ForEach-Object {
    $TimeString = $_.Time -replace '(\d{2}\/\d{2}\/\d{4}\s \d{2}:\d{2})$', '$1:00'
    [PSCustomObject]@{
        Name     = ([datetime]::ParseExact($TimeString, "MM/dd/yyyy HH:mm:ss", $null)).ToString('dd/MM/yyyy H:mm:ss')
        Incident = $_.Incident
    }
}

CodePudding user response：

You can use ParseExact on multiple formats provided you also specify more parameters:

$formats = 'MM/dd/yyyy HH:mm:ss', 'MM/dd/yyyy HH:mm'
Import-Csv -Path 'D:\Test\theTimes.csv' | ForEach-Object {
    $date = [datetime]::ParseExact($_.Time, $formats, [cultureInfo]::InvariantCulture, 'None').ToString('dd\/MM\/yyyy H:mm:ss')
    [PsCustomObject]@{
        Time = $date
        Incident = $_.Incident
    }
}

Given your example data this would yield

Time                Incident   
----                --------   
25/03/2021 12:59:49 INC18072411
03/04/2021 13:00:00 INC17989469
14/05/2021 13:01:57 INC17987450
28/05/2021 13:02:56 INC18000995
01/06/2021 13:06:00 INC17990104
06/06/2021 13:06:23 INC17983804
19/07/2021 13:07:00 INC17973858
08/08/2021 13:08:04 INC17987287
20/09/2021 13:08:19 INC17986031
10/10/2021 13:13:00 INC17926602