Replace data in first CSV column
I have a CSV with many columns and the data in the first column is dates, but in the wrong format. I can only select the first column and reformat the dates, but I cannot figure out how to store the new data in the existing CSV without overwriting all 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"
source to share
Consider the following csv data in a 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. Manage the old_timestamp column and commit changes to the pipeline.
Import-CSV C:\temp\test.csv | ForEach-Object{
$_.old_timestamp = get-date $_.old_timestamp -Format s
$_
}
Output example:
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 whatever you want with a return to the 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 can just use an operator Select-Object
that could do the same
Import-CSV C:\temp\test.csv |
Select-Object @{Name="New_TimeStamp";Expression = {get-date $_.old_timestamp -Format s}},* -ExcludeProperty old_timestamp
This only works 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've seen from your other questions, it may not be compatible with them, but this is the solution.
source to share
Use a ParseExact()
class method System.DateTime
to parse a string input to a date and convert the date to a formatted string using the method ToString()
.
$csv = 'C:\path\to\your.csv' $culture = [Globalization.CultureInfo]::InvariantCulture $srcfmt = 'dd-MMM-yyyy hh:mm:ss tt' $dstfmt = 'yyyy-MM-ddTHH:mm:ss' (Import-Csv $csv) | % { $date = [DateTime]::ParseExact($_.timestamp, $srcfmt, $culture) $_.timestamp = $date.ToString($dstfmt) $_ # required to inject the current object back into the pipeline } | Export-Csv $csv -NoType
Instead of using a loop to update a property, timestamp
you can also replace it with a calculated property :
$csv = 'C:\path\to\your.csv' $culture = [Globalization.CultureInfo]::InvariantCulture $srcfmt = 'dd-MMM-yyyy hh:mm:ss tt' $dstfmt = 'yyyy-MM-ddTHH:mm:ss' (Import-Csv $csv) | select @{n='timestamp';e={ [DateTime]::ParseExact($_.timestamp, $srcfmt, $culture).ToString($dstfmt) }}, other, properties, here, ... | Export-Csv $csv -NoType
Note that you must run Import-Csv
in a subexpression anyway (or write your output to a variable first), because otherwise the file will still be open for reading when you Export-Csv
start writing to it.
source to share