替换 CSV 第一列中的数据 [英] Replace data in first column of CSV

问题描述

我有一个包含许多列的 CSV，第一列中的数据是日期但格式错误.我只能选择第一列并重新格式化日期，但我不知道如何在不覆盖所有其他数据的情况下将新数据保存到现有 CSV.

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 {'"' + $_ + '"'}

之前:

"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"

之后:

"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"

推荐答案

考虑文件 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

我会做这样的事情.操作 old_timestamp 列"并将更改输出回管道.

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
    $_
}

示例输出:

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

类似方法

你可以只使用一个 Select-Object 语句，它可以做同样的事情

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

这仅在列名称不同时才有效.它会将格式化的列输出为 New_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.

这篇关于替换 CSV 第一列中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！