I need to replace consecutive double quotes in a csv file, the data in the file is enclosed in double quotes but there are some places where the quotes are repeating
Example is below
Incoming data is :
"Pacific Region"|"PNG"|"Jimmy""|""|
Need output as:
"Pacific Region"|"PNG"|"Jimmy"|""|
Please Note that empty double quotes are perfectly valid here and I need to retain them
What have you tried to solve this problem?
This would be easy with awk . Why does it have to be done with sed ?
Can we assume that a vertical bar ( | ) will never appear inside a quoted field?
Hi Abhilash,
you can try below command.
cat test.txt|tr -d '"'|sed 's/\([^|]*\)/"&"/g'|sed 's/..$//'
[*****@***** script]$ cat test.txt|tr -d '"'|sed 's/\([^|]*\)/"&"/g'|sed 's/..$//'
"Pacific Region"|"PNG"|"Jimmy"|""|
Regards,
Rajib
I tried the code, it is removing the consecutive quotes but it is also removing the empty strings, which I don't want because that is a perfectly valid value
@abhilashnair, could you answer the Don Cragun's questions in post #2. Then we could likely provide you with a better answer.
(|) will never appear inside a quoted field. it is the delimiter, so acts like a separator between 2 columns
Your sample with a field separator at the end of the line and no data following doesn't seem right, does it?
The sample data you provided in post #1 shows the vertical bar character as a field terminator; not a field separator (since there is no data after the last vertical bar on a line). To produce the output you requested with sed , one could try:
sed 's/"//g
s/^/"/
s/|/"|"/g
s/"$//' filename
Is it possible that there are (numerical) fields that are not enclosed in double quotes?
For an easy substitution you can add a delimiter at the beginning and another one at the end of the line, and delete them afterwards.
sed '
# add extra delimiters
s/^/|/
s/$/|/
# replace all border "" by "
s/\([^|]\)""|/\1"|/g
s/|""\([^|]\)/|"\1/g
# delete the extra delimiters
s/^|//
s/|$//
' filename
The above code worked perfectly.