Use Powershell to update date fields in CSV

Question

I have a csv file that contains a couple of fields that are in date format. This is the header line from the csv along with the first row:

"id","start_time","end_time","station_id","location"
"12617954","31/07/2014 4:38:49 AM","31/07/2014 4:39:19 AM","2","tv","home"

The csv file has many entries and I want to convert each date to a custom format using powershell. The custom format is yyyyMMdd-hh:mm:ss

Have tried this so far but haven't had any success writing the results to the csv file. I'm clearly no expert but what am I doing wrong?

$dateformats = [string[]]("dd/MM/yyyy hh:mm:ss tt","dd/MM/yyyy h:mm:ss tt")
$csvfiles = Import-Csv -path "D:\new_creative.csv"
ForEach ($line in $csvfiles) {
    $starttime2 = [datetime]::ParseExact($line.start_time, $dateformats, [System.Globalization.CultureInfo]::InvariantCulture,[System.Globalization.DateTimeStyles]::None)
    $starttime2.year.ToString() + $starttime2.month.toString().PadLeft(2,"0") + $starttime2.day.toString().PadLeft(2,"0") + "-" + $starttime2.Hour.toString().PadLeft(2,"0") + ":" + $starttime2.Minute.toString().PadLeft(2,"0") + ":" + $starttime2.Second.toString().PadLeft(2,"0")
    $starttime = $line.start_time.Replace($starttime,$startime2)
}
$csvfiles | Export-Csv "D:\file2.csv" -Force -Notype

arco444 · Accepted Answer · 2014-11-05 12:00:16Z

1

You can do this as follows:

$csv = Import-Csv -path "D:\new_creative.csv"
$dateFormat = "yyyyMMdd-hh:mm:ss"

ForEach($r in $csv) {
  $r.start_time = [DateTime]::parse($r.start_time).ToString($dateFormat) 
  $r.end_time = [DateTime]::parse($r.end_time).ToString($dateFormat) 
}

$csv | Export-Csv "D:\file2.csv" -Force -NoType

Use the ToString method of DateTime to do the conversion to the format you want.

Each time you hit a start_time or end_time field, this takes that value, converts it and then overwrites it, so the end result will have your converted dates in those fields.

edited Nov 5, 2014 at 12:00

answered Nov 5, 2014 at 11:07

arco444

23k12 gold badges66 silver badges67 bronze badges

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

1 Comment

mikec Over a year ago

This is perfect! Thanks very much arco444.

Collectives™ on Stack Overflow

Use Powershell to update date fields in CSV

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related