Need Help - comma inside double quote in comma separated csv,

Hello there,

I have a comma separated csv , and all the text field is wrapped by double quote. Issue is some text field contain comma as well inside double quote. so it is difficult to process.

Input in the csv file is ,

1,234,"abc,12,gh","GH234TY",34

I need output like below,
1~234~"abc,12,gh"~"GH234TY"~34 (separator to be replaced by ~)

or
1,234,"abc|12|gh","GH234TY",34 (comma inside double quote to be replaced by |)

Note : Comma inside double quote can present in any column. It is not fixed that Comma inside double quote will present in 3rd column only.

I have created one script to do this. It is working fine but time consuming.

Can I get any special command like sed, which will directly give me this output.

Many Thanks
Uttam

awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' input_file

Or if you want "~" as field seperator

awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' input_file

@msabhi: solutions working fine, except that you need to start first soln's for loop at i=2 to produce what the OP requested.

Hello there,

I tried both the command , but they are giving error .

1) awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' RA_003.txt

awk: syntax error near line 1 
awk: illegal statement near line 1

2) awk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' RA_003.txt

awk: syntax error near line 1 
awk: illegal statement near line 1
awk: syntax error near line 1 
awk: bailing out near line 1 
 

Could you please help ?

Actually I am not able to rectify such big awk command in unix.

Thanks
Uttam

use nawk instead of awk if you are in solaris.

Cheers,
Ranga

awk -F"\"" '{for(i=1;i<=NF;i++) {if(NF%2){ gsub(",","~",$i) }};print}' OFS=\" filename

---------- Post updated at 04:07 PM ---------- Previous update was at 04:02 PM ----------

OR TRY THIS

awk 'BEGIN{ORS=RS="\""}NR%2{gsub(",","~")}{print}' filename

Thanks a lot , :-), below two are working properly.

nawk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"|",$i);} print $0;}' inputfile

nawk -F\" 'BEGIN{OFS=FS;} {for(i=1;i<=NF;i=i+2){gsub(/,/,"~",$i);}}1' inputfile

Don't forget this:

Am sorry for late reply...the requesting person needed either one of the cases..
i.e.. either different delimiter inside the quotes or different delimiter outside the quotes..i targeted only one case..i.e.. having delimiter outside the quotes...i presented two solutions for this case which might have caused the confusion here...