PowerShell altering date format in a CSV-CodePudding

Please excuse me if I don't format this post properly, I'm new to StackOverflow - and a bit new to scripting in Powershell..

I'm trying to build an interface between a system that generates access codes, and another that picks it up. The generated CSV looks like this:

The generated file has the date formatted as ddMMyy, but the system that picks it up requires it in the format YYYY-MM-DD HH:MM:SS Fortunately the time should always be the same: 23:59:00

I've attempted to break this down into changing the date first, and then I need to append the time into the field on the end.

Using this answer I managed to write the following line, which when pasted into a powershell window outputs the date just as I need it:

PS C:\temp> Import-Csv "C:\temp\codes3.csv" | ForEach{[datetime]::ParseExact($_."When to Cancel User","ddMMyy",$null).ToString("yyyy-MM-dd")} 2022-10-25 2022-10-25 2022-10-25 2022-10-25 2022-10-25 2022-10-25 PS C:\temp>

which is perfect - but I can't work out how to write that back to my CSV file. When I add

 | Export-csv 'C:\temp\codes4.csv' -notype

to the end of the string, it doesn't actually update the dates. The saved file is exactly the same as the original. Any idea what I'm doing wrong?

I also need to append " 23:59:00" to the end of the date, but I'm not sure how. Any advice you might have would be much appreciated!

CodePudding user response：

#Load csv
$csv = import-csv [path]

#Update date/time format
$csv | %{
    $_."When to Cancel User" = ([datetime]::ParseExact($_."When to Cancel User","ddMMyy",$null).ToString("yyyy-MM-dd"))   " 23:59:00"
}

#Write new data to disk
$csv | export-csv [path]

CodePudding user response：

You have to make sure to store the value in the ForEach loop:

$csv = Import-Csv "E:\users\temp\test.csv"
$csv | ForEach-Object {$_."When to Cancel User" = (([datetime]::ParseExact($_."When to Cancel User","ddMMyy",$null).ToString("yyyy-MM-dd")   " 23:59:00"))}
$csv | Export-Csv "E:\users\temp\test_output.csv" -NoTypeInformation