How to remove extra double quotes from string in a delimited file??

Shell Programming and Scripting
1

Hi Unix Gurus..
I am new to Unix. Please help me. The file I am getting is as follows:
Input File

"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"123985","SAW CUT CONCRETE SLAB 20"THICK",,"98.57","","EACH","N"
"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"204312","ARMAFLEX-1 3/8 X 3"",,"2.48","","PER FOOT","N"
"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"205745","MISTING HEAD HOLLOW CONE 1/8"",,"4.32","","EACH","Y"

I want to remove the extra " present and the output file should look like this:
Output File

"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"123985","SAW CUT CONCRETE SLAB 20THICK",,"98.57","","EACH","N"
"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"204312","ARMAFLEX-1 3/8 X 3",,"2.48","","PER FOOT","N"
"2011-07-06 03:53:23","0","I","NOT SET   ",,,,"205745","MISTING HEAD HOLLOW CONE 1/8",,"4.32","","EACH","Y"

Please help... and thanks in advance.

Jay

2

How about:

sed 's/\([^",]\)"\([^",]\)/\1\2/' < infile > outfile

That should only remove double-quotes that aren't bordered by , on either side

---------- Post updated at 09:45 AM ---------- Previous update was at 09:41 AM ----------

I guess that won't work since some of them do... Add clauses to handle those:

sed 's/\([^",]\)"\([^",]\)/\1\2/g;s/\([^",]\)""/\1"/g;s/""\([^",]\)/"\1/g' < infile > outfile

Also add the g's I forgot before, so it can handle more than one per line.

3

Thanks a LOT.. Appreciate your help :slight_smile:

Your solution is working perfectly fine...

Quick question.. To maintain data integrity if I want to leave the Inch sign as is like " and replace all the quotes " with | .. will it be possible?

Input File

"2011-07-06 03:53:23","0","I","NOT SET ",,,,"123985","SAW CUT CONCRETE SLAB 20"THICK",,"98.57","","EACH","N"
"2011-07-06 03:53:23","0","I","NOT SET ",,,,"204312","ARMAFLEX-1 3/8 X 3"",,"2.48","","PER FOOT","N"
"2011-07-06 03:53:23","0","I","NOT SET ",,,,"205745","MISTING HEAD HOLLOW CONE 1/8"",,"4.32","","EACH","Y"

I want to convert the quote " to pipe | and leave the inch " sign as is in description field. The output file will look like this:
Output File

|2011-07-06 03:53:23|,|0|,|I|,|NOT SET |,,,,|123985|,|SAW CUT CONCRETE SLAB 20"THICK|,,|98.57|,||,|EACH|,|N|
|2011-07-06 03:53:23|,|0|,|I|,|NOT SET |,,,,|204312|,|ARMAFLEX-1 3/8 X 3"|,,|2.48|,||,|PER FOOT|,|N|
|2011-07-06 03:53:23|,|0|,|I|,|NOT SET |,,,,|205745|,|MISTING HEAD HOLLOW CONE 1/8"|,,|4.32|,||,|EACH|,|Y|

Again.. appreciate your help :slight_smile:

---------- Post updated at 11:50 AM ---------- Previous update was at 10:05 AM ----------

Hi Corona,
Please help!
This command

sed 's/\([^",]\)"\([^",]\)/\1\2/g;s/\([^",]\)""/\1"/g;s/""\([^",]\)/"\1/g' < infile > outfile

is not formatting the record correctly:

Input:

"2011-07-06 04:01:58","0","I","NOT SET ",,,,"950849","The leak rate was exceeded for RACK "C" MULTI COMPRESSOR RAC",,"22.18","","EACH","Y"

Output:

"2011-07-06 04:01:58","0","I","NOT SET ",,,,"950849","The leak rate was exceeded for RACK C" MULTI COMPRESSOR RAC",,"22.18","","EACH","Y"

Desired Output:

"2011-07-06 04:01:58","0","I","NOT SET ",,,,"950849","The leak rate was exceeded for RACK C MULTI COMPRESSOR RAC",,"22.18","","EACH","Y"