How to export to "non-standard" CSV with Powershell

Question

I need to convert a file with this format:

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

to this format

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

which outputs

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

After that I need to

remove the header (I tried -NoHeader which is not recognized)

replace last column with 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.

TheMadTechnician TheMadTechnician · Accepted Answer · 2015-07-30T20:18:40

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

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)

Edit: 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;$_}|

Whole script modified:

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.

How to export to "non-standard" CSV with Powershell

1 Answers