Format a column of dates in CSV

Question

I'm attempting to format some dates in the first column of a CSV. I would prefer to user something like powershell as I plan to automate this task. Does anyone have any advice on the best way to change the format of the date from something like MM/DD/YYY to YYYY-MM-DD? I've tried something like this:

$date = date -f ('yyyyMMdd')

$HMDA = Import-Csv "C:\HMDA\$date.YieldTableFixed.csv"
   ForEach-Object {
      $HMDA.Date = [datetime]::ParseExact($HMDA.Date).ToString('YYYY-MM-DD')
   } |
   Export-Csv -NoTypeInformation C:\HMDA\test.csv

Unfortunately, that didn't seem to do anything but give me a parse error and I can't seem to figure out why that is. Is there a way I can say something like:

ForEach-Object{
   $HMDA.A2:$HMDA.A63 = HMDA.$AC.Date.Format('YYYY-MM-DD')
}

TheMadTechnician · Accepted Answer · 2014-11-17 22:33:12Z

Ok, there's some basic errors here, but that's just a matter of not knowing better I think. Now this is hard to answer accurately because you did not give us an example of the incoming date field, so if it has some strange formatting this may throw errors as PowerShell fails to recognize that a string is in fact a date.

First off, if you pipe to a ForEach loop you reference the current object with $_. Such as:

Import-Csv "C:\HMDA\$date.YieldTableFixed.csv" | ForEach-Object {
    $_.Date = get-date $_.Date -f 'yyyy-MM-dd'
} | Export-Csv -NoTypeInformation C:\HMDA\test.csv

What would probably be simpler, as I recently learned from somebody else here on SO, would be to use Select, create the updated property on the fly, and then exclude the original property, effectively replacing it with the new one. Something like this:

Import-Csv "C:\HMDA\$date.YieldTableFixed.csv" | 
    Select *,@{label = 'Date';expression={get-date $_.Date -f 'yyyy-MM-dd'}} -ExcludeProperty Date | 
    Export-Csv -NoTypeInformation C:\HMDA\test.csv

Ansgar Wiechers · Accepted Answer · 2014-11-18 14:59:28Z

0

ParseExact() expects 3 parameters: the date string, a format string, and a format provider (which may be $null). Also, your output format string is incorrect (the year and day format specifiers need to be lowercase), and ForEach-Object reads from a pipeline.

Change this:

$HMDA = Import-Csv "C:\HMDA\$date.YieldTableFixed.csv"
  ForEach-Object {
    $HMDA.Date = [datetime]::ParseExact($HMDA.Date).ToString('YYYY-MM-DD')
  } |
  Export-Csv -NoTypeInformation C:\HMDA\test.csv

into this:

Import-Csv 'C:\HMDA\$date.YieldTableFixed.csv' | ForEach-Object {
  $_.Date = [DateTime]::ParseExact($_.Date, '*M\/dd\/yyyy', $null).ToString('yyyy-MM-dd')
  $_
} | Export-Csv -NoTypeInformation 'C:\HMDA\test.csv'

edited Nov 18, 2014 at 14:59

answered Nov 17, 2014 at 22:40

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

2 Comments

flamingslaptrap Over a year ago

Unfortunately I'm greeted with this error. If it makes any difference, when I open the file in excel the date format is displayed as "*3/14/2001"

Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime." At line:2 char:1 + $_.Date = [DateTime]::ParseExact($_.Date, 'MM/dd/yyyy', $null).ToString('yyyy-MM ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException     + FullyQualifiedErrorId : FormatException

Ansgar Wiechers Over a year ago

@flamingslaptrap The leading asterisk in your date values must be represented in the input format string as well (*M\/dd\/yyyy). If the asterisk is not present for double digit months remove it from the date before parsing it (($_.Date -replace '^\*')) and use the format string M\/dd\/yyyy (without the leading asterisk). Further adjustment may be required, depending on whether days 1 through 9 are represented with or without leading zeroes.

Collectives™ on Stack Overflow

Format a column of dates in CSV

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related