Replace data in first column of CSV

Question

I have a CSV with many columns, and the data in the first column are dates but in the wrong format. I am able to select just the first column and reformat the dates, but I cannot figure out how to save the new data to the existing CSV without overwriting all the other data.

$File = "File.csv"
$Content = Get-Content $File
$timestamp = @()
$timestamp += '"' + "timestamp" + '"'
$timestamp += $Content | Foreach { $_.Split(",") | select -first 1 } | select -skip 1 -last 10000 | where {$_ -notmatch "timestamp"} | foreach {($_).Substring(1,$_.Length-2)} | foreach {get-date ($_).ToString() -Format s} | foreach {'"' + $_ + '"'}

Before:

"timestamp"
"17-Dec-2014 07:00:00 AM"
"17-Dec-2014 07:15:00 AM"
"17-Dec-2014 07:30:00 AM"
"17-Dec-2014 07:45:00 AM"
"17-Dec-2014 08:00:00 AM"

After:

"timestamp"
"2014-12-17T07:00:00"
"2014-12-17T07:15:00"
"2014-12-17T07:30:00"
"2014-12-17T07:45:00"
"2014-12-17T08:00:00"

Answer 1

Consider the following data in csv format in the file c:\temp\test.csv

old_timestamp   timestamp
12/17/2014 7:00 12/17/2014 7:00
12/17/2014 7:15 12/17/2014 7:15
12/17/2014 7:30 12/17/2014 7:30
12/17/2014 7:45 12/17/2014 7:45
12/17/2014 8:00 12/17/2014 8:00

I would do something like this. Manipulate the old_timestamp "column" and output the changes back to the pipeline.

Import-CSV C:\temp\test.csv | ForEach-Object{
    $_.old_timestamp = get-date $_.old_timestamp -Format s
    $_
}

Sample output:

old_timestamp       timestamp      
-------------       ---------      
2014-12-17T07:00:00 12/17/2014 7:00
2014-12-17T07:15:00 12/17/2014 7:15
2014-12-17T07:30:00 12/17/2014 7:30
2014-12-17T07:45:00 12/17/2014 7:45
2014-12-17T08:00:00 12/17/2014 8:00

Now you can do what ever you want with it like output back to a file !

Import-CSV C:\temp\test.csv | ForEach-Object{
    $_.old_timestamp = get-date $_.old_timestamp -Format s
    $_
} | Export-Csv C:\temp\updated_test.csv -NoTypeInformation

Simlar Approach

You could just use a Select-Object statement which could do the same thing

Import-CSV C:\temp\test.csv | 
    Select-Object @{Name="New_TimeStamp";Expression = {get-date $_.old_timestamp -Format s}},* -ExcludeProperty old_timestamp

This only works as is if the column name is different. It will output the formatted column as New_TimeStamp as well as the rest of the data by specifying *. From what I have seen of your other questions this might not meld well with them but it is a solution.