ConvertTo-Csv Output without quotes

问题:

I am using ConvertTo-Csv to get comma separated output

 get-process | convertto-csv -NoTypeInformation -Delimiter ","

It outputs like:

"__NounName","Name","Handles","VM","WS",".....

However I would like to get output without quotes, like

__NounName,Name,Handles,VM,WS....

回答1:

Here is a way to remove the quotes

get-process | convertto-csv -NoTypeInformation -Delimiter "," | % {$_ -replace '"',''} 

But it has a serious drawback if one of the item contains a " it will be removed !

回答2:

I was working on a table today and thought about this very question as I was previewing the CSV file in notepad and decided to see what others had come up with. It seems many have over-complicated the solution.
Here's a real simple way to remove the quote marks from a CSV file generated by the Export-Csv cmdlet in PowerShell.

Create a TEST.csv file with the following data.

"ID","Name","State"
"5","Stephanie","Arizona"
"4","Melanie","Oregon"
"2","Katie","Texas"
"8","Steve","Idaho"
"9","Dolly","Tennessee"

Save As: TEST.csv

Store file contents in a $Test variable
$Test = Get-Content .\TEST.csv

Load $Test variable to see results of the get-content cmdlet
$Test

Load $Test variable again and replace all ( "," ) with a comma, then trim start and end by removing each quote mark
$Test.Replace('","',",").TrimStart('"').TrimEnd('"')

Save/Replace TEST.csv file
$Test.Replace('","',",").TrimStart('"').TrimEnd('"') | Out-File .\TEST.csv -Force -Confirm:$false

Test new file Output with Import-Csv and Get-Content:
Import-Csv .\TEST.csv
Get-Content .\TEST.csv

To Sum it all up, the work can be done with 2 lines of code
$Test = Get-Content .\TEST.csv
$Test.Replace('","',",").TrimStart('"').TrimEnd('"') | Out-File .\TEST.csv -Force -Confirm:$false

回答3:

This is pretty similar to the accepted answer but it helps to prevent unwanted removal of "real" quotes.

$delimiter = ','
Get-Process | ConvertTo-Csv -Delimiter $delimiter -NoTypeInformation | foreach { $_ -replace '^"','' -replace "`"$delimiter`"",$delimiter -replace '"$','' }

This will do the following:

• Remove quotes that begin a line
• Remove quotes that end a line
• Replace quotes that wrap a delimiter with the delimiter alone.

Therefore, the only way this would go wrong is if one of the values actually contained not only quotes, but specifically a quote-delimiter-quote sequence, which hopefully should be pretty uncommon.

回答4:

I ran into this issue, found this question, but was not satisfied with the answers because they all seem to suffer if the data you are using contains a delimiter, which should remain quoted. Getting rid of the unneeded double quotes is a good thing.

The solution below appears to solve this issue for a general case, and for all variants that would cause issues.

I found this answer elsewhere, Removing quotes from CSV created by PowerShell, and have used it to code up an example answer for the SO community.

Attribution: Credit for the regex, goes 100% to Russ Loski.

Code in a Function, Remove-DoubleQuotesFromCsv

function Remove-DoubleQuotesFromCsv
{
    param (
        [Parameter(Mandatory=$true)]
        [string]
        $InputFile,

        [string]
        $OutputFile
    )

    if (-not $OutputFile)
    {
        $OutputFile = $InputFile
    }

    $inputCsv = Import-Csv $InputFile

    $quotedData = $inputCsv | ConvertTo-Csv -NoTypeInformation

    $outputCsv = $quotedData | % {$_ -replace  `
        '\G(?<start>^|,)(("(?<output>[^,"]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$)))' `
        ,'${start}${output}'}

    $outputCsv | Out-File $OutputFile -Encoding utf8 -Force
}

Test Code

$csvData = @"
id,string,notes,number
1,hello world.,classic,123
2,"a comma, is in here","test data 1",345
3,",a comma, is in here","test data 2",346
4,"a comma, is in here,","test data 3",347
"@

$data = $csvData | ConvertFrom-Csv
"`r`n---- data ---"
$data

$quotedData = $data | ConvertTo-Csv -NoTypeInformation
"`r`n---- quotedData ---"
$quotedData

# this regular expression comes from:
# http://www.sqlmovers.com/removing-quotes-from-csv-created-by-powershell/
$fixedData = $quotedData | % {$_ -replace  `
  '\G(?<start>^|,)(("(?<output>[^,"]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$)))' `
  ,'${start}${output}'}
"`r`n---- fixedData ---"
$fixedData

$fixedData | Out-File e:\test.csv -Encoding ascii -Force
"`r`n---- e:\test.csv ---"
Get-Content e:\test.csv

Test Output

---- data ---
id string               notes       number
-- ------               -----       ------
1  hello world.         classic     123   
2  a comma, is in here  test data 1 345   
3  ,a comma, is in here test data 2 346   
4  a comma, is in here, test data 3 347   

---- quotedData ---
"id","string","notes","number"
"1","hello world.","classic","123"
"2","a comma, is in here","test data 1","345"
"3",",a comma, is in here","test data 2","346"
"4","a comma, is in here,","test data 3","347"

---- fixedData ---
id,string,notes,number
1,hello world.,classic,123
2,"a comma, is in here",test data 1,345
3,",a comma, is in here",test data 2,346
4,"a comma, is in here,",test data 3,347

---- e:\test.csv ---
id,string,notes,number
1,hello world.,classic,123
2,"a comma, is in here",test data 1,345
3,",a comma, is in here",test data 2,346
4,"a comma, is in here,",test data 3,347

回答5:

I haven't spent much time looking for removing the quotes. But, here is a workaround.

get-process | Export-Csv -NoTypeInformation -Verbose -Path $env:temp\test.csv
$csv = Import-Csv -Path $env:temp\test.csv

This is a quick workaround and there may be a better way to do this.

回答6:

I found that Kory's answer didn't work for the case where the original string included more than one blank field in a row. I.e. "ABC",,"0" was fine but "ABC",,,"0" wasn't handled properly. It stopped replacing quotes after the ",,,". I fixed it by adding "|(?<output>)" near the end of the first parameter, like this:

% {$_ -replace  `
    '\G(?<start>^|,)(("(?<output>[^,"]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$))|(?<output>))', `
    '${start}${output}'}

回答7:

Depending on how pathological (or "full-featured") your CSV data is, one of the posted solutions will already work.

The solution posted by Kory Gill is almost perfect - the only issue remaining is that quotes are removed also for cells containing the line separator \r\n, which is causing issues in many tools.

The solution is adding a newline to the character class expression:

$fixedData = $quotedData | % {$_ -replace  `
'\G(?<start>^|,)(("(?<output>[^,"\n]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$)))' `
,'${start}${output}'}