How to replace double quote from a field in comma delimited file

My file is comma delimeted, only 3rd field is enclosed in doubled quotes, but in that filed getting multi double quotes, multi commas as well. How to replace double quotes with some rare chars like ~` without disturbing rest data
Input:

100,DAVID, "ABC, DEF, ("SSS")",900
100,DAVID, "PQR, "OK",JKN",900

Output

100,DAVID, "ABC, DEF, (~`SSS~`)",900
100,DAVID, "PQR,~`OK~`,JKN",900

I've edited your post to make it easier to comprehend.
What have you tried so far?

1 Like

Hi Friends, Can some one please help me on this query

This is really difficult.
Certainly solvable with perl (using perl-RE, perhaps extended perl-RE).

hi, can you please elaborate, how to solve

It's actually easy to do with PERL, PHP, Python, Javascript or any language which has basic text processing functions (split, string replace, etc):

Read each line as a string and then:

Split the string on commas into an array and create a new string from the elements of the array minus the first, second, and last element. This yields:

"ABC, DEF, ("SSS")"

Then remove the first and last char (the beginning and ending quotation marks) in the string, which yields:

ABC, DEF, ("SSS")

The replace each quotation mark remaining with your special chars:

ABC, DEF, (~`SSS~`)

Add the quotation marks back to the beginning and the end:

"ABC, DEF, (~`SSS~`)"

and reassemble with commas with the first, second and last elements of the array (this is called implode in PHP), i.e.

(PHP 4, PHP 5, PHP 7)
implode — Join array elements with a string

100,DAVID, "ABC, DEF, (~`SSS~`)",900

This little algorithm works for both test cases provided by @rajeshkumare , easily.

100,DAVID, "ABC, DEF, ("SSS")",900
100,DAVID, "PQR, "OK",JKN",900

and yields:

100,DAVID, "ABC, DEF, (~`SSS~`)",900
100,DAVID, "PQR,~`OK~`,JKN",900

@rajeshkumare you can pick your fav programming language to do this, line by line, to get the results you are looking for.

1 Like

use sed
something like this, \ is used to specify
sed s/\"/\~/g file

it will remove all double quotes, we need to remove only double quotes inside data

no, this will replace ALL " with ~ - this is not what the OP wanted.

Are you working with the passwd file?

no