How to remove all quotations mark in the csv file using powershell script?

Solution 1:

Next time you make one, export-csv in powershell 7 has a new option you may like:

export-csv -UseQuotes AsNeeded

Solution 2:

It seems many of us have already explained that quotes are sometimes needed in CSV files. This is the case when:

• the value contains a double quote
• the value contains the delimiter character
• the value contains newlines or has whitespace at the beginning or the end of the string

With PS version 7 you have the option to use parameter -UseQuotes AsNeeded. For older versions I made this helper function to convert to CSV using only quotes when needed:

function ConvertTo-CsvNoQuotes {
    # returns a csv delimited string array with values unquoted unless needed
    [OutputType('System.Object[]')]
    [CmdletBinding(DefaultParameterSetName = 'ByDelimiter')]
    param (
        [Parameter(Mandatory = $true, ValueFromPipeline = $true, ValueFromPipelineByPropertyName = $true, Position = 0)]
        [PSObject]$InputObject,

        [Parameter(Position = 1, ParameterSetName = 'ByDelimiter')]
        [char]$Delimiter = ',',

        [Parameter(ParameterSetName = 'ByCulture')]
        [switch]$UseCulture,
        [switch]$NoHeaders,
        [switch]$IncludeTypeInformation  # by default, this function does NOT include type information
    )
    begin {
        if ($UseCulture) { $Delimiter = (Get-Culture).TextInfo.ListSeparator }
        # regex to test if a string contains a double quote, the delimiter character,
        # newlines or has whitespace at the beginning or the end of the string.
        # if that is the case, the value needs to be quoted.
        $needQuotes = '^\s|["{0}\r\n]|\s$' -f [regex]::Escape($Delimiter)
        # a boolean to check if we have output the headers or not from the object(s)
        # and another to check if we have output type information or not
        $doneHeaders = $doneTypeInfo = $false
    }

    process {
        foreach($item in $InputObject) {
            if (!$doneTypeInfo -and $IncludeTypeInformation) {
                '#TYPE {0}' -f $item.GetType().FullName
                $doneTypeInfo = $true
            }
            if (!$doneHeaders -and !$NoHeaders) {
                $row = $item.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Name -match $needQuotes) { '"{0}"' -f ($_.Name -replace '"', '""') } else { $_.Name }
                }
                $row -join $Delimiter
                $doneHeaders = $true
            }
            $item | ForEach-Object {
                $row = $_.PsObject.Properties | ForEach-Object {
                    # if needed, wrap the value in quotes and double any quotes inside
                    if ($_.Value -match $needQuotes) { '"{0}"' -f ($_.Value -replace '"', '""') } else { $_.Value }
                }
                $row -join $Delimiter
            }
        }
    }
}

Using your example to remove the unnecessary quotes in an existing CSV file:

$File = "c:\programfiles\programx\file.csv"
(Import-Csv $File) | ConvertTo-CsvNoQuotes | Set-Content $File

Solution 3:

keeping in mind that this may trash your data if you have embedded double quotes in your data, here is yet another variation on the idea ... [grin]

what it does ...

• defines the input & output full file names
• grabs the *.tmp files from the temp dir
• filters for the 1st three files & only three basic properties
• creates the file to work with
• loads the file content
• replaces the double quotes with nothing
• saves the cleaned file to the 2nd file name
• displays the original & cleaned versions of the file

the code ...

$TestCSV = "$env:TEMP\Ted.Xiong_-_Test.csv"
$CleanedTestCSV = $TestCSV -replace 'Test', 'CleanedTest'

Get-ChildItem -LiteralPath $env:TEMP -Filter '*.tmp' -File |
    Select-Object -Property Name, LastWriteTime, Length -First 3 |
    Export-Csv -LiteralPath $TestCSV -NoTypeInformation

(Get-Content -LiteralPath $TestCSV) -replace '"', '' |
    Set-Content -LiteralPath $CleanedTestCSV

Get-Content -LiteralPath $TestCSV
'=' * 30
Get-Content -LiteralPath $CleanedTestCSV

output ...

"Name","LastWriteTime","Length"
"hd4130E.tmp","2020-03-13 5:23:06 PM","0"
"hd418D4.tmp","2020-03-12 11:47:59 PM","0"
"hd41F7D.tmp","2020-03-13 5:23:09 PM","0"
==============================
Name,LastWriteTime,Length
hd4130E.tmp,2020-03-13 5:23:06 PM,0
hd418D4.tmp,2020-03-12 11:47:59 PM,0
hd41F7D.tmp,2020-03-13 5:23:09 PM,0