I have a .csv in the following file format:
In: "bob","1234 Main St, New York, NY","cool guy"
I am looking to remove double quotes that don't have a comma inside:
Out: bob,"1234 Main St, New York, Ny",cool guy
Is there a way to do this in Powershell?
I have checked:
csv file. Use the Foreach-Object cmdlet (% is an alias) to read each line as it comes from the file. Inside the script block for the Foreach-Object command, use the $_ automatic variable to reference the current line and the replace operator to replace a quotation mark with nothing.
You can import double quotation marks using CSV files and import maps by escaping the double quotation marks. To escape the double quotation marks, enclose them within another double quotation mark.
Since CSV files use the comma character "," to separate columns, values that contain commas must be handled as a special case. These fields are wrapped within double quotation marks. The first double quote signifies the beginning of the column data, and the last double quote marks the end.
If the text within a field contains quoted text and a comma, then it starts to get ugly as double quotes are now needed to prevent confusion as to what each quote character means.
Adapting the code from "How to remove double quotes on specific column from CSV file using Powershell script":
$csv = 'C:\path\to\your.csv'
(Get-Content $csv) -replace '(?m)"([^,]*?)"(?=,|$)', '$1' |
Set-Content $csv
The regex (?m)"([^,]*?)"(?=,|$)
is matching any " + 0 or more non-commas + "
before a comma or end of line (achieved with a positive look-ahead and a multiline option (?m)
that forces $
to match a newline, not just the end of string).
See regex demo
I don't know exactly what the rest of your script looks like. Try something along these lines though
(("bob","1234 Main St, New York, NY","cool guy") -split '"' |
ForEach-Object {IF ($_ -match ",") {'"' + $_ + '"' } ELSE {$_}}) -join ","
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With