Replacing double quotes in csv

Question

I have almost the following problem and could not find a solution. This could be my CSV file structure:

1223;"B630521 ("L" fixed bracket)";"2" width";"length: 5"";2;alternate A 1224;"B630522 ("L" fixed bracket)";"3" width";"length: 6"";2;alternate B 

As you can see, there are several " written for inches and "L" in the app. "

Now I am looking for a UNIX shell script to replace the double quotes " (inch) and "L" with two single quotes, for example, in the following example:

 sed "s/$OLD/$NEW/g" $QFILE > $TFILE && mv $TFILE $QFILE 

Can anybody help me?

Answer 1

Perhaps this is what you want:

 sed "s/\([0-9]\)\"\([^;]\)/\1''\2/g" 

Ie: Find the double quotes ( " ) after the number ( [0-9] ), but do not follow the semicolon ( [^;] ) and replace it with two single quotes.

Edit: I can expand my team (now it is getting quite long):

 sed "s/\([0-9]\)\"\([^;]\)/\1''\2/g;s/\([^;]\)\"\([^;]\)/\1\'\2/g;s/\([^;]\)\"\([^;]\)/\1\'\2/g" 

How do you use SunOS, I think you cannot use extended regular expressions ( sed -r )? So I did it like this: the first s command replaces all inches with " ,” the second and third s match. They replace all " that are not a direct neighbor of a ; with one. ' I have to do this twice to be able to replace the second " , for example. "L" , because there is only one character between " , and this character already matches \([^;]\) . Thus, you also replace "" with '' . If you have """ or """" etc., you need to put one more (but one more) s .

Answer 2

Update (using Perl is easy, as you get full-featured functionality)

 perl -pe 's/(?<!^)(?<!;)"(?!(;|$))/'"'"'/g' file 

Exit

 1223;"B630521 ('L' fixed bracket)";"2' width";"length: 5'";2;alternate A 1224;"B630522 ('L' fixed bracket)";"3' width";"length: 6'";2;alternate B 

Using only sed, grep

Just using grep, sed (and not perl, php, python, etc.), a not-so-elegant solution could be:

 grep -o '[^;]*' file | sed 's/"/`/; s/"$/`/; s/"/'"'"'/g; s/`/"/g' 

Output - for your input file, it gives:

 1223 "B630521 ('L' fixed bracket)" "2' width" "length: 5'" 2 alternate A 1224 "B630522 ('L' fixed bracket)" "3' width" "length: 6'" 2 alternate B 

• grep -o basically breaks the input into ;
• sed first replaces "at the beginning of the line"
  
• then it replaces "at the end of the line with another"
• it then replaces all remaining double quotes with " single pretty '
• finally, it returns everything " at the beginning and at the end

Answer 3

For "L", try the following:

  sed "s/\"L\"/'L'/g" 

For inches you can try:

 sed "s/\([0-9]\)\"\"/\1''\"/g" 

I'm not sure if this is the best option, but I tried and it works. Hope this will be helpful.