powershellcsvquotation-marks

POWERSHELL - Remove Quotation Marks from CSV for just one column

I want to remove the quotation marks for only the first column of my csv file with powershell

So instead of:

"Username","Identifier"
"booker12","9012"   
"grey07","2070"

I want the result to be:

Username,"Identifier"
booker12,"9012" 
grey07,"2070

To remove all quotation marks the code would be

Import-Csv "test.csv" | ConvertTo-CSV -NoTypeInformation | % { $_ -Replace '"', ""} | Out-File C:\CSV_Tests\test.csv -fo -en ascii

But how to remove the marks just for the first column?

Thanks!

Solution

  • As commented, it is recommended to install the latest PowerShell version which has a new ConvertTo-Csv/Export-Csv cmdlets with a -quotefields.
    But as that appears not to be possible for your environment:

    $Csv = @'
"Username","Identifier"
"booker12","9012"   
"grey07","2070"
'@ -Split '\r?\n'

    $Csv -Replace '^"([^"]*)"', '$1'
Username,"Identifier"
booker12,"9012"
grey07,"2070"

    Explanation:

    1. The first ^ marks the beginning of a line
    2. ([^"]*) selects all successive characters that aren't double quotes
    3. '$1' is the placeholder the results of the sequence defined in 2.

    For your file this means:

    (Get-Content .\Test.csv) -Replace '^"([^"]*)"', '$1' |Set-Content .\Test.csv