Unix sed command - global replacement is not working

Rocky1989 0
Viewed 80

I have scenario where we want to replace multiple double quotes to single quotes between the data, but as the input data is separated with "comma" delimiter and all column data is enclosed with double quotes "" got an issue and the same explained below:

The sample data looks like this:

"int","","123","abd"""sf123","top"

So, the output would be:

"int","","123","abd"sf123","top"

tried below approach to get the resolution, but only first occurrence is working, not sure what is the issue??

sed -ie 's/,"",/,"NULL",/g;s/""/"/g;s/,"NULL",/,"",/g' inputfile.txt
  1. replacing all ---> from ,"", to ,"NULL",
  2. replacing all multiple occurrences of ---> from """ or "" or """" to " (single occurrence)
  3. replacing 1 step changes back to original ---> from ,"NULL", to ,"",

But, only first occurrence is getting changed and remaining looks same as below:

If input is :

"int","","","123","abd"""sf123","top"

the output is coming as:

"int","","NULL","123","abd"sf123","top"

But, the output should be:

"int","","","123","abd"sf123","top"
scripting awk sed unix shell
5 Answers

You may try this perl with a lookahead:

perl -pe 's/("")+(?=")//g' file

"int","","123","abd"sf123","top"
"int","","","123","abd"sf123","top"
"123"abcs"

Where input is:

cat file

"int","","123","abd"""sf123","top"
"int","","","123","abd"""sf123","top"
"123"""""abcs"

Breakup:

  • ("")+: Match 1+ pairs of double quotes
  • (?="): If those pairs are followed by a single "
anubhava0

Using sed

$ sed -E 's/(,"",)?"+(",)?/\1"\2/g' input_file
"int","","123","abd"sf123","top"
"int","","NULL","123","abd"sf123","top"
"int","","","123","abd"sf123","top"
HatLess0

In awk with your shown samples please try following awk code. Written and tested in GNU awk, should work in any version of awk.

awk '
BEGIN{ FS=OFS="," }
{
  for(i=1;i<=NF;i++){
    if($i!~/^""$/){
       gsub(/"+/,"\"",$i)
    }
  }
}
1
'  Input_file

Explanation: Simple explanation would be, setting field separator and output field separator as , for all the lines of Input_file. Then traversing through each field of line, if a field is NOT NULL then Globally replacing all 1 or more occurrences of " with single occurrence of ". Then printing the line.

RavinderSingh130

With sed you could repeat 1 or more times sets of "" using a group followed by matching a single "

Then in the replacement use a single "

sed -E 's/("")+"/"/g' file

For this content

$ cat file
"int","","123","abd"""sf123","top"
"int","","","123","abd"""sf123","top"
"123"""""abcs"

The output is

"int","","123","abd"sf123","top"
"int","","","123","abd"sf123","top"
"123"abcs"
The fourth bird0 
sed s'@"""@"@' file

That works. I will demonstrate another method though, which you may also find useful in other situations.

#!/bin/sh -x

cat > ed1 <<EOF
3s/"""/"/
wq
EOF

cp file stack
cat stack | tr ',' '\n' > f2
ed -s f2 < ed1
cat f2 | tr '\n' ',' > stack
rm -v ./f2
rm -v ./ed1

The point of this is that if you have a big csv record all on one line, and you want to edit a specific field, then if you know the field number, you can convert all the commas to carriage returns, and use the field number as a line number to either substitute, append after it, or insert before it with Ed; and then re-convert back to csv.

petrus40
Related