Converting CSV date string to another format

Question

I got a CSV file with a date column with the format dd/MM/YYY HH:mm:ss, e.g. 14/11/2016 00:00:00.

I'm trying to batch convert the formating on that column to an ISO-8601 date format: YYYY-mm-dd HH:mm:ss

I have tried: ( I have edited I had an error on input string format...)

Import-Csv someCSV.csv | % {
  $_.'[Date]' = ([datetime]::ParseExact(($_.'[Date]'),"dd/MM/YYYY HH:mm:ss").ToString('yyyy-MM-dd HH:mm:ss'))
} | Export-Csv 'C:\testBis.csv' -NoTypeInformation

I'm getting an error:

Cannot find an overload for "ParseExact" and the argument count: "2".
At line:1 char:69
+ ... Date]' ; ([datetime]::ParseExact(($_.'[Date]'),"dd/MM/YYY HH ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest

I've had a look and tryed with Try parse and sever few ideas but nothing seam to work.

I dont know what I'm going wrong. any suggestions?

.. why do people actually fail to read error messages, how is this a thing? — specializt
– specializt, Commented Jan 17, 2017 at 12:44

Ansgar Wiechers · Accepted Answer · 2017-01-17 14:38:54Z

1

The pattern for matching the year must be yyyy (with lowercase y), there is no comma in your sample date, and you need to provide culture information for ParseExact(). I would also recommend to escape the forward slashes, otherwise they'll match whatever date separator is configured in the computer's regional settings. And you need to echo the modified record back to the pipeline, otherwise there'd be nothing to export.

$culture = [Globalization.CultureInfo]::InvariantCulture
Import-Csv someCSV.csv | ForEach-Object {
    $_.'[Date]' = [DateTime]::ParseExact($_.'[Date]', 'dd\/MM\/yyyy HH:mm:ss', $culture).ToString('yyyy-MM-dd HH:mm:ss')
    $_    # <-- feed modified record back into pipeline
} | Export-Csv 'C:\testBis.csv' -NoType

edited Jan 17, 2017 at 14:38

answered Jan 17, 2017 at 12:50

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

2 Comments

maco1717 Over a year ago

wouldn't culture need to go as a parameter in to ParseExact function?

Ansgar Wiechers Over a year ago

@maco1717 Indeed it would. Fixed.

Panagiotis Kanavos · Accepted Answer · 2017-01-17 12:44:42Z

0

DateTime.ParseExact requires at least three parameters. The third parameter is the culture to use. You can pass the invariant culture [System.Globalization.CultureInfo]::InvariantCulture, eg:

([datetime]::ParseExact(($_.'[Date]'),"dd/MM/YYYY HH:mm:ss",[System.Globalization.CultureInfo]::InvariantCulture)

or a cleaner version:

$inv = [System.Globalization.CultureInfo]::InvariantCulture
$fmtFrom = "dd/MM/YYY HH:mm:ss"
$fmtTo   = "yyyy-MM-dd HH:mm:ss"

Import-Csv someCSV.csv | % {
    $_.'[Date]' = ([datetime]::ParseExact(($_.'[Date]'),$fmtFrom, $inv).ToString($fmtTo))
  } | Export-Csv 'C:\testBis.csv' -NoTypeInformation

I removed the , from the format string because you mentioned that the actual format is dd/MM/YYY HH:mm:ss, not dd/MM/YYY, HH:mm:ss

answered Jan 17, 2017 at 12:44

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

1 Comment

Ansgar Wiechers Over a year ago

This won't work. Your input format string is still invalid, and no output will be created, because you don't feed the modified records back to the pipeline.

Collectives™ on Stack Overflow

Converting CSV date string to another format

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related