ConvertTo-Csv Output without quotes

Question

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

score 25 · Accepted Answer · answered Jun 06 '14 at 04:53

25

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 !

answered Jun 06 '14 at 04:53

JPBlanc

70,406
17
130
175

2

This is likely not applicable to the output of `get-process`, but this solution will also unescape fields that have special characters in them. (e.g. "aaa","bbb","cc,cc","ddd"). – Steve Guidi Mar 26 '19 at 17:56
2

@SteveGuidi Your are right, it's written in the answer. – JPBlanc Mar 26 '19 at 18:21

js2010 · Answer 2 · 2022-05-25T15:35:07.867

22

Hmm, I have Powershell 7 preview 1 on my mac, and Export-Csv has a -UseQuotes option that you can set to AsNeeded. :)

edited May 25 '22 at 15:35

answered Jul 18 '19 at 02:26

js2010

23,033
6
64
66

1

That's not helpful for older versions of PowerShell. It appears [Kory Gill's answer](https://stackoverflow.com/a/39647567/639580) works best in that case. – Tony Dec 10 '20 at 17:22

score 11 · Answer 3 · edited Jul 25 '19 at 01:04

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

score 9 · Answer 4 · answered Apr 22 '16 at 18:18

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.

This does not work if the data you are working with was quoted because it contained a delimiter. — Kory Gill, Sep 22 '16 at 18:54

Kory Gill · Answer 5 · 2019-01-18T22:04:00.183

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
5,"a comma, is in here,","test data 4`r`nwith a newline",347
6,hello world2.,classic,123
"@

$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>[^,"\n]*?)"(?=,|$))|(?<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   
5  a comma, is in here, test data 4...              347   
6  hello world2.        classic                     123   

---- 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"
"5","a comma, is in here,","test data 4
with a newline","347"
"6","hello world2.","classic","123"

---- 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
5,"a comma, is in here,","test data 4
with a newline","347"
6,hello world2.,classic,123

---- 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,"a comma, is in here,","test data 4
with a newline","347"
6,hello world2.,classic,123

[An answer by LCC](https://stackoverflow.com/a/50415379/1026) notes that this removes quotes also for cells containing the line separator \r\n — Nickolay, Jan 17 '19 at 16:48

score 7 · Answer 6 · answered Nov 20 '19 at 13:05

7

Once the file is generated, you can run

set-content FILENAME.csv ((get-content FILENAME.csv) -replace '"')

answered Nov 20 '19 at 13:05

Tiago Martins Peres

14,289
18
86
145

1

This deserves to be the answer. Thank you sop much – Nerd in Training Dec 01 '20 at 19:35
2

If I have a CSV line with three parameters like `"123", "Sanchez, Rick", "Scientist"`, this turns it into four parameters. Subsequent input processes will fail to import correctly. – Tony Dec 10 '20 at 17:25
@Tony isn't that 4 parameters? Which error do you get? – Tiago Martins Peres Dec 10 '20 at 17:28
1

@Tiago It's three: ID, Name, and Job title. Name just happens to include a comma separator it its text. Let's say Job Title was "Vice President of Science, Technology, and Logistics". Stripping away the quotes turned that single column into three. – Tony Dec 11 '20 at 17:18
@Tony you can replace the comma between double quotes with any other character like an hyphen (-) before removing the double quotes. [There's a solution here](https://social.technet.microsoft.com/Forums/lync/en-US/76ea2473-9a49-4037-a720-76aa8fa47663/replace-commas-present-in-quotes-in-a-csv-file?forum=winserverpowershell) which suggests this, `$Pattern = '(?<!"".+),(?!.+"")' $Text = '"abc, 123"' $Text -replace $Pattern , '-'` – Tiago Martins Peres Dec 11 '20 at 18:35

score 3 · Answer 7 · answered May 18 '18 at 16:09

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}'}

score 2 · Answer 8 · answered Jul 18 '19 at 02:09

I wrote this for my needs:

function ConvertTo-Delimited {

    [CmdletBinding()]
    param(
        [Parameter(ValueFromPipeline=$true,Mandatory=$true)]
        [psobject[]]$InputObject,
        [string]$Delimiter='|',
        [switch]$ExcludeHeader
    )
    Begin {

        if ( $ExcludeHeader -eq $false ) {
            @(
                $InputObject[0].PsObject.Properties | `
                Select-Object -ExpandProperty Name
            ) -Join $Delimiter          
        }

    }
    Process {

        foreach ($item in $InputObject) {
            @(
                $item.PsObject.Properties | `
                Select-Object Value | `
                ForEach-Object { 
                    if ( $null -ne $_.Value ) {$_.Value.ToString()} 
                    else {''} 
                }
            ) -Join $Delimiter
        }

    }
    End {}

}

Usage:

$Data = @(
    [PSCustomObject]@{
        A = $null
        B = Get-Date
        C = $null
    }
    [PSCustomObject]@{
        A = 1
        B = Get-Date
        C = 'Lorem'
    }
    [PSCustomObject]@{
        A = 2
        B = Get-Date
        C = 'Ipsum'
    }
    [PSCustomObject]@{
        A = 3
        B = $null
        C = 'Lorem Ipsum'
    }
)

# with headers
PS> ConvertTo-Delimited $Data
A|B|C
1|7/17/19 9:07:23 PM|Lorem
2|7/17/19 9:07:23 PM|Ipsum
||

# without headers
PS> ConvertTo-Delimited $Data -ExcludeHeader
1|7/17/19 9:08:19 PM|Lorem
2|7/17/19 9:08:19 PM|Ipsum
||

score 2 · Answer 9 · answered Nov 06 '20 at 17:31

Here's another approach:

Get-Process | ConvertTo-Csv -NoTypeInformation -Delimiter "," | 
    foreach { $_ -replace '^"|"$|"(?=,)|(?<=,)"','' }

This replaces matches with the empty string, in each line. Breaking down the regex above:

| is like an OR, used to unite the following 4 sub-regexes
^" matches quotes in the beginning of the line
"$ matches quotes in the end of the line
"(?=,) matches quotes that are immediately followed by a comma
(?<=,)" matches quotes that are immediately preceded by a comma

score 1 · Answer 10 · answered Feb 02 '18 at 05:27

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}'}

Nice, this took care of my data. – Tony Jan 05 '21 at 20:05 — Tony, Jan 05 '21 at 20:05

score 0 · Answer 11 · answered Jun 06 '14 at 04:49

0

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.

answered Jun 06 '14 at 04:49

ravikanth

24,922
4
60
60

I still get quotes using it. – Tiago Martins Peres Nov 20 '19 at 13:02
I'm not sure a workaround for what. Was there something other than removing quotes from the file in the OP question? – bkqc May 13 '20 at 20:28

score 0 · Answer 12 · answered Dec 12 '19 at 09:16

A slightly modified variant of JPBlanc's answer:

I had an existing csv file which looked like this:

001,002,003
004,005,006

I wanted to export only the first and third column to a new csv file. And for sure I didn't want any quotes ;-) It can be done like this:

Import-Csv -Path .\source.csv -Delimiter ',' -Header A,B,C | select A,C | ConvertTo-Csv -NoTypeInformation -Delimiter ',' | % {$_ -replace '"',''} | Out-File -Encoding utf8 .\target.csv

score 0 · Answer 13 · answered Jan 29 '20 at 20:40

Couldn't find an answer to a similar question so I'm posting what I've found here...

For exporting as Pipe Delimited with No Quotes for string qualifiers, use the following:

$objtable | convertto-csv -Delimiter "|" -notypeinformation | select -Skip $headers | % { $_ -replace '"\|"', "|"} | % { $_ -replace '""', '"'} | % { $_ -replace "^`"",''} | % { $_ -replace "`"$",''} | out-file "$OutputPath$filename" -fo -en ascii

This was the only thing I could come up with that could handle quotes and commas within the text; especially things like a quote and comma next to each other at the beginning or ending of a text field.

Greek2me · Answer 14 · 2020-08-18T21:04:34.893

This function takes a powershell csv object from the pipeline and outputs like convertto-csv but without adding quotes (unless needed).

function convertto-unquotedcsv {
    param([Parameter(ValueFromPipeline=$true)]$csv, $delimiter=',', [switch]$noheader=$false)
    begin {
      $NeedQuotesRex = "($([regex]::escape($delimiter))|[\n\r\t])"
      if ($noheader) { $names = @($true) } else { $names = @($false) }
    } 
    process {
      $psop = $_.psobject.properties
      if (-not $names) {
        $names = $psop.name | % {if ($_ -match $NeedQuotesRex) {'"' + $_ + '"'} else {$_}}
        $names -join $delimiter   # unquoted csv header
      }
      $values = $psop.value | % {if ($_ -match $NeedQuotesRex) {'"' + $_ + '"'} else {$_}}
      $values -join $delimiter    # unquoted csv line
    } 
    end {
    }
}

$names gets an array of noteproperty names and $values gets an array of notepropery values. It took that special step to output the header. The process block gets the csv object one piece at a time.

Here is a test run

$delimiter = ','; $csvData = @"
id,string,notes,"points per 1,000",number
4,"a delimiter$delimiter is in here,","test data 3",1,348
5,"a comma, is in here,","test data 4`r`nwith a newline",0.5,347
6,hello world2.,classic,"3,000",123
"@

$csvdata | convertfrom-csv | sort number | convertto-unquotedcsv -delimiter $delimiter

id,string,notes,"points per 1,000",number
6,hello world2.,classic,"3,000",123
5,"a comma, is in here,","test data 4
with a newline",0.5,347
4,"a delimiter, is in here,",test data 3,1,348

ConvertTo-Csv Output without quotes

14 Answers14

Linked

Related