使用PowerShell修复CSV文件中的日期,转换为UK格式,如果缺少,则添加第二个日期

gywdnpxw  于 2022-11-10  发布在  Shell
关注(0)|答案(2)|浏览(130)

我有下面的CSV数据(示例),因为您可以看到日期是美国格式(MM/dd/yyyy),有时会缺少秒(:ss)。我想修复它,这样日期就是英国格式(dd/MM/yyyy),如果时间中缺少秒,则添加: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

我已经试过以下几种了

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

但在缺少秒数的记录上出现错误:
使用“3”个参数调用“ParseExact”时出现异常:“未将字符串识别为有效的日期时间。”

fivyi3re

fivyi3re1#

如果您还指定了更多参数,则可以在多种格式上使用ParseExact


# .Net expects a strongly typed string array

# (must implement [System.Collections.IEnumerable]), so cast to [string[]]

[string[]]$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
    }
}

对于您的示例数据,这将产生

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
ru9i0ody

ru9i0ody2#

使用regex,您可以在将输入字符串解析为[DateTime]类型之前添加缺少的秒数。

$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
    }
}

相关问题