CODEWITHSUNDEEP
regexpowershell
Bjørn H. Sandvik
Bjørn H. Sandvik

Reputation: 543

Powershell regex to strip quotes

Removing double quotes altogether using ConvertTo-Csv is easy, but what I need is to only strip quotes singularly.

What I mean is that I have an output from Invoke-Sqlcmd with a query producing the following data: 1,"George Burns",18,0 (the SQL query adds quotes to strings)

The output from ConvertTo-Csv becomes: "1","""George Burns""","18","0"

So - I need to preserve the quotes around the string and get rid of the rest of them. Any suggestion how I can fix this?

Invoke-Sqlcmd -ServerInstance SQLSrv01 `
    -Database MyDatabase `
    -InputFile "c:\temp\MyQuery.sql" | `
ConvertTo-Csv -NoTypeInformation -Delimiter "," | ` 
Select-Object -Skip 1 |  % {$_ -replace '', ""} | `
Out-File ("C:\temp\test.csv") -Force -Encoding UTF8

Upvotes: 1

Views: 839

Answers (1)

briantist
briantist

Reputation: 47792

The answer depends on whether you only need fix the strings (remove extraneous quotes) or whether you must leave numbers unquoted.

The former is simpler:

Numbers can be quoted

In this case your best approach is strip the quotes from the input strings, then let ConvertTo-Csv continue doing the work (or even just use Export-Csv since it seems like that's all you want to do with it anyway. You can accomplish this in several ways but the easiest might just be with a calculated property on Select-Object:

(code sample note: you didn't give us the column names so I'm making them up; also I'm removing backticks because they aren't recommended and aren't needed after pipes)

Invoke-Sqlcmd -ServerInstance SQLSrv01 -Database MyDatabase -InputFile "c:\temp\MyQuery.sql" |
Select-Object -Property Id,@{Name='Actor';Expression={$_.Actor.Trim('"')}},Age,Warrants |
Export-Csv -LiteralPath C:\temp\test.csv -Encoding UTF8

Numbers must not be quoted

In this case, I recommend writing your own CSV conversion function (truthfully, you should probably do the same for the above case too), in which you don't use ConvertTo-Csv.

You can use the .PSObject property on the input object to enumerate the properties and get their values. Here's a function I whipped up that isn't well tested:

function ConvertTo-MyCsv {
[CmdletBinding()]
param(
    [Parameter(
        Mandatory, 
        ValueFromPipeline, 
        ValueFromPipelineByPropertyName
    )]
    [Object]
    $InputObject ,

    [Parameter()]
    [ValidateNotNullOrEmpty()]
    [String]
    $Delimiter = ',' ,

    [Parameter()]
    [Switch]
    $NoStrip
)
    Begin {
        $headers = $false
    }

    Process {
        if (-not $headers) {
            $InputObject.PSObject.Properties.Name.ForEach({'"{0}"' -f $_}) -join $Delimiter
        }

        $InputObject.PSObject.Properties.Value.ForEach({
            if ($_ -is [String]) {
                $value = if ($NoStrip) {
                    $_
                } else {
                    $_.Trim('"')
                }

                '"{0}"' -f ($value -replace '"','""')
            } else {
                $_
            }
        }) -join $Delimiter
    }
}

Use it like this:

Invoke-Sqlcmd -ServerInstance SQLSrv01 -Database MyDatabase -InputFile "c:\temp\MyQuery.sql" |
ConvertTo-MyCsv

Upvotes: 1

Related Questions