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')
}

Answer 1

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

Answer 2

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'