Powershell CSV modification

Question

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

Please add your code to the question

Santiago Squarzon
– Santiago Squarzon

2022-03-08 18:19:32 +00:00
Commented Mar 8, 2022 at 18:19 — Santiago Squarzon
– Santiago Squarzon, Commented Mar 8, 2022 at 18:19

Mathias R. Jessen · Accepted Answer · 2022-03-08 18:20:41Z

2

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

answered Mar 8, 2022 at 18:20

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martin B Over a year ago

The output I get is not correct for the date. "4","EA","09-26-2022","W","DOA1" It looks like it take my system date separators not /

Martin B Over a year ago

Thanks for putting me on the right track Mathias. I ended up changing the Request Date Parse to # Parse and re-format date column $_.'Requested Date' = [datetime]::ParseExact($_.'Requested Date', 'dd.MM.yyyy', $null).ToString('MM\/dd\/yyyy') to get it to work correctly.

Collectives™ on Stack Overflow

Powershell CSV modification

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related