How to escape the unescaped quotes in a csv file string using sed

usery1z9 0
Viewed 75

I have received a file (size > 1GB) which is CSV, separated by | When I was importing it to bigquery we found out there are some unescaped " in the file. How can I fix the file by using sed command

This|is|test only|to check quotes||123|""
second|line|"with quotes"|no text|""|345|""
third line|with|"start quote" and another " "|a word after quotes|" "|677|""

expected (no change in line 1,2)

This|is|test only|to check quotes||123|""
second|line|"with quotes"|no text|""| 345|""
third line|with|"start quote"" and another "" "|a word after quotes|" "|677|""

My idea is if there is a " that doesn't start or ends with | should be replaced with ""

sed regex
2 Answers

With Perl and lookarounds:

$ perl -pe 's/(?<!^)(?<![|"])"(?![|"])(?!$)/""/g' ip.csv
This|is|test only|to check quotes||123|""
second|line|"with quotes"|no text|""|345|""
third line|with|"start quote"" and another "" "|a word after quotes|" "|677|""

(?<![|"]) and (?![|"]) are negative lookarounds, helps here to match " only if there's no " or | characters to the left/right.

(?<!^) and (?!$) are there to prevent matching a " at the start/end of lines (since | won't be present at the start of the first field or the end of the last field.

Use perl -i for inplace editing.

Sundeep0

As a nice perl solution has been provided, here is a sed alternative:

sed -E 's/([^|"])"([^|"])/\1""\2/g' file

The regex [^|"] matches any characters other than | and ".
If a double quote is surrounded by the characters above, replace it with "".

tshiono0
Related