Replace a character of specified column(s) of all rows in a file

Shell Programming and Scripting
1

Hi - I have a file "file1" of below format. Its a comma seperated file. Note that each string is enclosed in double quotes.

"abc","-0.15","10,000.00","IJK"
"xyz","1,000.01","1,000,000.50","OPR"

I want the result as:

"abc","-0.15","10000.00","IJK"
"xyz","1,000.01","1000000.50","OPR"

I want to remove all the comma in the numeric string value of the 3rd column of all rows in the file file1.

I tried the below command.

awk ' BEGIN { FS = "\",\"" } ; { gsub(/,/, "", $42) }1' file1

Output is:

"abc -0.15 10000.00 IJK"
"xyz 1,000.01 1000000.50 OPR"

I used "," as field seperator. It correctly removed the commas in the 3rd column values, but it also replaced the "," with a space.

Request help in resolving this. Appreciate your help.

Thanks!
[/FONT][/COLOR]

2

Not so elegant solution, but here is one way of doing it:

sed 's/","/#/g' file | awk -F# '{gsub(/,/,x,$3)}1' OFS='","'
3

Set the OFS to the same as the FS:

# awk -F'","' '{gsub(/,/,"",$3)}1' OFS='","' input
"abc","-0.15","10000.00","IJK"
"xyz","1,000.01","1000000.50","OPR"
4

Or

awk -F\" '{gsub(/,/,"",$6)}1' OFS=\" file
"abc","-0.15","10000.00","IJK"
"xyz","1,000.01","1000000.50","OPR"
5

Thanks so much, the solutions worked good.

One thing is if I want to remove the commas from 2 or more columns, how can I change the cmd? Like, here, I want to remove the commas from 2nd and 3rd column values resulting in:

"abc","-0.15","10000.00","IJK"
"xyz","1000.01","1000000.50","OPR"
6

Try :

$ awk '{gsub(/,/,x,$2);gsub(/,/,x,$3)}1' FS='","' OFS='","' file
"abc","-0.15","10000.00","IJK"
"xyz","1000.01","1000000.50","OPR"
7

Thanks Akshay, it did worked but felt it is not optimized. It's not a problem I have at hand, but what if we have say 100+ columns in the file1 and we have 50+ numeric ones? It is not feasible to repeat gsub() function so many times.

Do we have any clean solution?

8

So, how do you want to identify the 50+ numeric columns that you want to modify? Done that, you'll still need to run gsub for every single one of them.

9 
$ awk '{for(i=1;i<=NF;i++)gsub(/,/,x,$i)}1' FS='","' OFS='","' file