Powershell CSV modification-CodePudding

CSV source exemple:

"Requested Quantity","Unit of Measure","Requested Date","Forecast Timing Qualifier","Rating"
"4.00 ","EA","26.09.2022","W","DOA1"
"12.00 ","EA","27.09.2022","W","DOA1"

I need to remove the last 3 Characters to Requested Quantity and I need to convert the Requested Date format to Month.Day.Year for each line.

Input : "4.00 ","EA","26.09.2022","W","DOA1" Output : "4","EA","09/26/2022","W","DOA1"

I got the Requested quantity ok but I need some help with converting the date portion.

Thanks

CodePudding user response：

For the Requested Quantity column you can use the -replace operator to remove the dot and everything after. For the date column, use [Datetime]::ParseExact() to parse the existing format, then convert back to a string with the desired format:

Import-Csv path\to\source.csv |ForEach-Object {
    # Cut the trailing decimals off the quantity
    $_.'Requested Quantity' = $_.'Requested Quantity' -replace '\..*$'

    # Parse and re-format date column
    $_.'Requested Date' = [datetime]::ParseExact($_.'Requested Date', 'dd.MM.yyyy', $null).ToString('MM/dd/yyyy')

    # output the modified object for export
    $_
} |Export-Csv path\to\output.csv -NoTypeInformation