Remove a comma from a CSV file for a particular column only

Hi manny213 ,

It's unusual to being using a tool that cannot automatically handle the processing of the double-quotes. Still, here's some points to consider.

I'm not sure if you're aware, but there's two versions of PowerShell:

1. Windows PowerShell: The version that has been included with Windows for nearly two decades;
2. PowerShell: The downloadable version that does not ship with Windows.

The version of Export-Csv from Windows PowerShell does not allow you to exclude the double-quotes.

The version of Export-Csv from PowerShell does include additional parameters that can help control the behaviour of the double-quotes:

• Export-Csv (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Learn

There are other options as well.

If you're working with SQL Integration Services (IS), handling double-quotes isn't something you should have to worry about at the scripting level as IS can easily perform any necessary transformations.

You can apply a half-baked solution at the scripting level, as shown below, but the flaw is that if any value genuinely was supposed to contain a double-quote, it will be stripped out in taking this approach. As such, I don't recommend it, but I've included it for completeness only.

Get-ChildItem -Path "d:\data\temp\forum\*.csv" | ForEach-Object {
    # Name of the temporary working file.
    $tempFile = [regex]::Replace($_.FullName, ".csv$", "-temp.csv");

    try
    {
        # Parse the original file and pipe to the temporary file.
        Import-Csv -Path $_.FullName -ErrorAction:Stop | ForEach-Object {
            [PSCustomObject] @{
                SYMBOL = $_.SYMBOL;
                INTERVAL = $_.INTERVAL;
                NAME = $_.NAME.Replace(",", "");
                DATE = $_.DATE;
                OPEN = $_.OPEN;
                HIGH = $_.HIGH;
                LOW = $_.LOW;
                CLOSE = $_.CLOSE;
                VOLUME = $_.VOLUME;
            }
        } |
            ConvertTo-Csv -NoTypeInformation |
            ForEach-Object { $_.Replace('"', ""); } |
            Out-File -FilePath $tempFile -ErrorAction:Stop;

        # Remove original file.
        Remove-Item -Path $_.FullName -ErrorAction:Stop;

        # Rename temporary file to the original file's name.
        Rename-Item -Path $tempFile -NewName $_.Name -ErrorAction:Stop;
    }
    catch
    {
        Write-Error -Exception $_.Exception;
    }
}

Cheers,
Lain