如何导出为“非标"带有 Powershell 的 CSV [英] How to export to "non-standard" CSV with Powershell

问题描述

我需要使用这种格式转换文件:

I need to convert a file with this format:

2015.03.27,09:00,1.08764,1.08827,1.08535,1.08747,89412015.03.27,10:00,1.08745,1.08893,1.08604,1.08762,7558

2015.03.27,09:00,1.08764,1.08827,1.08535,1.08747,8941 2015.03.27,10:00,1.08745,1.08893,1.08604,1.08762,7558

到这种格式

2015.03.27,1.08764,1.08827,1.08535,1.08747,1
2015.03.27,1.08745,1.08893,1.08604,1.08762,1

2015.03.27,1.08764,1.08827,1.08535,1.08747,1
2015.03.27,1.08745,1.08893,1.08604,1.08762,1

我从这段代码开始，但看不到如何实现完整的转换:

I started with this code but can't see how to achieve the full transformation:

    Import-Csv in.csv -Header Date,Time,O,H,L,C,V | Select-Object Date,O,H,L,C,V | Export-Csv -path out.csv -NoTypeInformation
    (Get-Content out.csv) | % {$_ -replace '"', ""} | out-file -FilePath out.csv -Force -Encoding ascii

哪个输出

日期,O,H,L,C,V
2015.03.27,1.08745,1.08893,1.08604,1.08762,8941
2015.03.27,1.08763,1.08911,1.08542,1.08901,7558

Date,O,H,L,C,V
2015.03.27,1.08745,1.08893,1.08604,1.08762,8941
2015.03.27,1.08763,1.08911,1.08542,1.08901,7558

在那之后我需要

• 删除标题(我试过 -NoHeader 无法识别)
• 用 1 替换最后一列.

如何尽可能简单地做到这一点(如果可能，不要遍历每一行)

How to do that as simply as possible (if possible without looping through each row)

更新:最后我简化了要求.我只需要用常量替换最后一列.

Update : finally I have simplified requirement. I just need to replace last column with constant.

推荐答案

好吧，这可能是一个巨大的单线......我要在管道上换行以保持理智原因.

Ok, this could be one massive one-liner... I'm going to do line breaks at the pipes for sanity reasons though.

Import-Csv in.csv -header Date,Time,O,H,L,C,V|select * -ExcludeProperty time|
    %{$_.date = [datetime]::ParseExact($_.date,"yyyy.MM.dd",$null).tostring("yyMMdd");$_}|
    ConvertTo-Csv -NoTypeInformation|
    select -skip 1|
    %{$_ -replace '"'}|
    Set-Content out.csv -encoding ascii

基本上我导入 CSV，排除时间列，将日期列转换为实际的 [datetime] 对象，然后将其转换回所需的格式.然后我将修改后的对象(带有新格式化的日期)通过管道传递到 ConvertTo-CSV，并跳过第一行(你不想要的标题)，然后从它，最后用 Set-Content 输出到文件(比 Out-File 快)

Basically I import the CSV, exclude the time column, convert the date column to an actual [datetime] object and then convert it back in the desired format. Then I pass the modified object (with the newly formatted date) down the pipe to ConvertTo-CSV, and skip the first line (your headers that you don't want), and then remove the quotes from it, and lastly output to file with Set-Content (faster than Out-File)

刚刚看到您的更新...为此，我们只需将最后一列更改为 1，同时通过添加 $_.v 修改日期列=1;...

Just saw your update... to do that we'll just change the last column to 1 at the same time we modify the date column by adding $_.v=1;...

        %{$_.date = [datetime]::ParseExact($_.date,"yyyy.MM.dd",$null).tostring("yyMMdd");$_.v=1;$_}|

整个脚本修改:

Import-Csv in.csv -header Date,Time,O,H,L,C,V|select * -ExcludeProperty time|
    %{$_.date = [datetime]::ParseExact($_.date,"yyyy.MM.dd",$null).tostring("yyMMdd");$_.v=1;$_}|
    ConvertTo-Csv -NoTypeInformation|
    select -skip 1|
    %{$_ -replace '"'}|
    Set-Content out.csv -encoding ascii

哦，这有一个额外的好处，即不必读入文件，将文件写入驱动器，读入该文件，然后再次将文件写入驱动器.

Oh, and this has the added benefit of not having to read the file in, write the file to the drive, read that file in, and then write the file to the drive again.

这篇关于如何导出为“非标"带有 Powershell 的 CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！