Export-Csv adding unwanted header double quotes

Question

I have got a source CSV file (without a header, all columns delimited by a comma) which I am trying split out into separate CSV files based upon the value in the first column and using that column value as the output file name.

Input file:

S00000009,2016,M04 01/07/2016,0.00,0.00,0.00,0.00,0.00,0.00,750.00,0.00,0.00
S00000009,2016,M05 01/08/2016,0.00,0.00,0.00,0.00,0.00,0.00,600.00,0.00,0.00
S00000009,2016,M06 01/09/2016,0.00,0.00,0.00,0.00,0.00,0.00,600.00,0.00,0.00
S00000010,2015,W28 05/10/2015,2275.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
S00000010,2015,W41 04/01/2016,0.00,0.00,0.00,0.00,0.00,0.00,568.75,0.00,0.00
S00000010,2015,W42 11/01/2016,0.00,0.00,0.00,0.00,0.00,0.00,568.75,0.00,0.00
S00000012,2015,W10 01/06/2015,0.00,0.00,0.00,0.00,0.00,0.00,650.00,0.00,0.00
S00000012,2015,W11 08/06/2015,0.00,0.00,0.00,0.00,0.00,0.00,650.00,0.00,0.00
S00000012,2015,W12 15/06/2015,0.00,0.00,0.00,0.00,0.00,0.00,650.00,0.00,0.00

My PowerShell script looks like this:

Import-Csv INPUT_FILE.csv -Header service_id,year,period,cash_exp,cash_inc,cash_def,act_exp,act_inc,act_def,comm_exp,comm_inc,comm_def |
    Group-Object -Property "service_id" | 
    Foreach-Object {
        $path = $_.Name + ".csv";
        $_.group | Export-Csv -Path $path -NoTypeInformation
    }

Output files:

S00000009.csv:

"service_id","year","period","cash_exp","cash_inc","cash_def","act_exp","act_inc","act_def","comm_exp","comm_inc","comm_def"
"S00000009","2016","M04 01/07/2016","0.00","0.00","0.00","0.00","0.00","0.00","750.00","0.00","0.00"
"S00000009","2016","M05 01/08/2016","0.00","0.00","0.00","0.00","0.00","0.00","600.00","0.00","0.00"
"S00000009","2016","M06 01/09/2016","0.00","0.00","0.00","0.00","0.00","0.00","600.00","0.00","0.00"

S00000010.csv:

"service_id","year","period","cash_exp","cash_inc","cash_def","act_exp","act_inc","act_def","comm_exp","comm_inc","comm_def"
"S00000010","2015","W28 05/10/2015","2275.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00","0.00"
"S00000010","2015","W41 04/01/2016","0.00","0.00","0.00","0.00","0.00","0.00","568.75","0.00","0.00"
"S00000010","2015","W42 11/01/2016","0.00","0.00","0.00","0.00","0.00","0.00","568.75","0.00","0.00"

It is generating the new files using the header value in column 1 (service_id). There are 2 problems.

The output CSV file contains a header row which I don't need.
The columns are enclosed with double quotes which I don't need.

For Q1 have a look here [PowerShell Export-Csv No Headers](https://stackoverflow.com/questions/45964069/powershell-export-csv-no-headers): , and for Q2 have a look here: [ConvertTo-Csv Output without quotes](https://stackoverflow.com/questions/24074205/convertto-csv-output-without-quotes) — boxdog, Aug 24 '18 at 07:41

score 1 · Accepted Answer · answered Aug 24 '18 at 08:07

1

First of all the .csv file needs headers and the quote marks as a csv file structure. But if you don't want them then you can go on with a text file or...

$temp = Import-Csv INPUT_FILE.csv -Header service_id,year,period,cash_exp,cash_inc,cash_def,act_exp,act_inc,act_def,comm_exp,comm_inc,comm_def | Group-Object -Property "service_id" | 
        Foreach-Object {
        $path=$_.name+".csv"
        $temp0 = $_.group | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1
        $temp1 = $temp0.replace("""","")
        $temp1 > $path

        }

But this output is not a "real" csv file. Hope that helps.

answered Aug 24 '18 at 08:07

StefTheo

429
5
9

Thanks Stef, that works really well, output file is nicely formatted. Is it possible to force the newly created .csv files to be outputted to a different folder - it defaults to the current folder. I tried to specify a subfolder on the line $path=$_.name+".csv" I tried something like this $out_file=\output\$_.name+".csv" – zoomzoomvince Aug 24 '18 at 10:04
I have go that last issue fixed now (i have go outpath defined as a subfolder) $path=$out_path+'\'+$_.name+".csv" – zoomzoomvince Aug 24 '18 at 10:20

score 1 · Answer 2 · answered Aug 24 '18 at 08:33

For your particular scenario you could probably use a simpler approach. Read the input file as a plain text file, group the lines by splitting off the first field, then write the groups to output files named after the groups:

Get-Content 'INPUT_FILE.csv' |
    Group-Object { $_.Split(',')[0] } |
    ForEach-Object { $_.Group | Set-Content ($_.Name + '.csv') }

score 0 · Answer 3 · answered Aug 24 '18 at 16:42

Another solution,

using no named headers but simply numbers (as they aren't wanted in output anyway)
avoiding unneccessary temporary files.
removing only field delimiting double quotes.

Import-Csv INPUT_FILE.csv -Header (1..12) | 
  Group-Object -Property "1" | Foreach-Object {
    ($_.Group | ConvertTo-Csv -NoType | Select-Object -Skip 1).Trim('"') -replace '","',',' | 
       Set-Content -Path ("{0}.csv" -f $_.Name) 
}

Export-Csv adding unwanted header double quotes

3 Answers3