Doing math on values in CSV Powershell

Question

I have a CSV that contains a Scheduled Time (of day) but the value is in milliseconds from the start of day and I need to convert the values to time of day (0:HH:mm:ss,fff). This is how the CSV looks:

"Log Date","Scheduled Time","Category","Cart #","Scheduled Title","Actual Title","Start Date","End Date","Scheduled Length","Actual Length"
"7/18/2018 12:00:00 AM","22439181","DCM","3172","BONCHE URBANO DIGITAL 201","BONCHE URBANO COMERCIAL JUN 23","12/31/1969 7:00:00 PM","12/31/9999 11:59:59 PM","30","33"
"7/18/2018 12:00:00 AM","45750000","DCM","3172","BONCHE URBANO DIGITAL 201","BONCHE URBANO COMERCIAL JUN 23","12/31/1969 7:00:00 PM","12/31/9999 11:59:59 PM","30","33"

I have this and when I input the time into this, it works:

("{0:HH:mm:ss,fff}" -f ([datetime]([timespan]::fromseconds($time / 1000)).Ticks))

How can I use that to replace all the values in a CSV? I have this so far, but it doesn't work and I'm not great with Powershell to begin with.

Import-Csv .\Values.csv | 
ForEach-Object {
$time = $_.'Scheduled Time'
$_.'Scheduled Time' = ("{0:HH:mm:ss,fff}" -f ([datetime]([timespan]::fromseconds($time / 1000)).Ticks));$_
} | 
Export-Csv '.\Values.csv' -NoTypeInformation

Thanks in advance for the help!

Use [`Select-Object`](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/select-object?view=powershell-6) and an property expression like: `@{Name="Scheduled Time"; Expression = {("{0:HH:mm:ss,fff}" -f ([datetime]([timespan]::fromseconds($time / 1000)).Ticks))}}` — iRon, Jul 18 '18 at 13:13

mklement0 · Accepted Answer · 2018-07-19T12:55:47.050

iRon has provided the crucial pointer in a comment on the question: pipe the Import-Csv output to a Select-Object call, which enables selective transformation of the input objects' properties via calculated properties.

Since you want to preserve the other properties as-is, while presumably performing the transformation without changing the property order, you must explicitly enumerate all output properties, which is perhaps easier to do by constructing an array up front.

# Define the property names to extract from the imported rows,
# interspersed with the calculcated property to transfrom the 'Scheduled Time'
# column values:
$properties = 
  'Log Date',
  @{ 
     n='Scheduled Time'
     e={ '{0:HH:mm:ss,fff}' -f [datetime] ([timespan]::FromTicks([long] $_.'Scheduled Time' * 10000)).Ticks } 
  },
  'Category',
  'Cart #',
  'Scheduled Title',
  'Actual Title',
  'Start Date',
  'End Date',
  'Scheduled Length',
  'Actual Length'

# Import the CSV - by reading it into memory _in full_ first -, select the
# properties and perform the transformation, then write back to the file.
(Import-Csv ./Values.csv) | # !! The (...) are required
  Select-Object $properties |
    Export-Csv -NoTypeInformation ./Values.csv

Note the need to enclose the Import-Csv call in (...) in order to enforce reading its entire content up front, in full into memory, which is a prerequisite for being able to write back to the same file as part of the same pipeline.
Without that, you would essentially erase the file.

Aside from concerns about available memory with large input files, also note that this method bears a slight risk of data loss, if the pipeline is interrupted before writing all (transformed) objects back to the input file has completed.

A more robust solution would be to write to a temporary file first, and replace the original file with the temporary file on successful completion.

Doing math on values in CSV Powershell

1 Answers1