Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

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

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace a character of specified column(s) of all rows in a file
# 1  
Old 12-15-2013
Replace a character of specified column(s) of all rows in a file
Hi - I have a file "file1" of below format. Its a comma seperated file. Note that each string is enclosed in double quotes.
Code:
"abc","-0.15","10,000.00","IJK"
"xyz","1,000.01","1,000,000.50","OPR"

I want the result as:
Code:
"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.
Code:
awk ' BEGIN { FS = "\",\"" } ; { gsub(/,/, "", $42) }1' file1

Output is:
Code:
"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!
Last edited by Franklin52; 12-15-2013 at 08:47 AM.. Reason: Please use code tags
# 2  
Old 12-15-2013
Not so elegant solution, but here is one way of doing it:
Code:
sed 's/","/#/g' file | awk -F# '{gsub(/,/,x,$3)}1' OFS='","'

# 3  
Old 12-15-2013
Set the OFS to the same as the FS:

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

Last edited by neutronscott; 12-15-2013 at 03:46 AM..
# 4  
Old 12-15-2013
Or
Code:
awk -F\" '{gsub(/,/,"",$6)}1' OFS=\" file
"abc","-0.15","10000.00","IJK"
"xyz","1,000.01","1000000.50","OPR"

# 5  
Old 12-15-2013
All worked
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:

Code:
"abc","-0.15","10000.00","IJK"
"xyz","1000.01","1000000.50","OPR"

# 6  
Old 12-15-2013
Quote:
Originally Posted by njny
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:

Code:
"abc","-0.15","10000.00","IJK"
"xyz","1000.01","1000000.50","OPR"

Try :

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

# 7  
Old 12-16-2013
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?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Character screening and paste into new file in columns instead of rows

QL10169_SAUJANA%SubNetwork=ONRM_ROOT_MO_R,SubNetwork=ERBS_KCRN11,MeContext=QL10169_SAUJANA_5 %External_Link_Failure %X2_link_problem_to_one_or_several_neighbouring_eNodeBs. QL10187_MATANG_JAYA_2%SubNetwork=ONRM_ROOT_MO_R,SubNetwork=ERBS_KUCHING,MeContext=QL10187_MATANG_JAY A_2_3... (2 Replies)
Discussion started by: Ankit Vyas
2 Replies

2. UNIX for Dummies Questions & Answers

Replace space in column with letter for several rows

I have a pbd file, which has the following format: TITLE Protein X MODEL 1 ATOM 1 N PRO 24 45.220 71.410 43.810 1.00 0.00 ATOM 2 H1 PRO 24 45.800 71.310 42.000 1.00 0.00 TER ENDMDL Column 22 is the chain... (5 Replies)
Discussion started by: Egy
5 Replies

3. Shell Programming and Scripting

Replace rows to column

I have a csv file and i want to convert its rows into columns sample file like this Row1,1,2,3,......,n row2,4,5,6,.......,n . . . . rown,7,8,9,........,n i want it like this row1,row2,....,rown 1,4,.............,7 (4 Replies)
Discussion started by: sagar_1986
4 Replies

4. Shell Programming and Scripting

[Solved] Replace character in 3rd column and leave 1rst and last

Hello to all, I have the following text where columns are separated by spaces. I want to have the 3rd column separating 3 strings with 2 "_" in the format below: LeftSring_CentralString_RightString So, in 3rd column I want to replace all "_" with "-", except the first and last "_" The... (5 Replies)
Discussion started by: Ophiuchus
5 Replies

5. Shell Programming and Scripting

How to replace a character in a specific column in a file?

This is a file that I have test line 1 (55) ) test line 2 (45) ) I would like to change all the parens in position 1 of this file to a ); i only want to check position 1 in every line of the file. I have tried different varations of sed, but cannot seem to be able to limit it to... (1 Reply)
Discussion started by: JoeG
1 Replies

6. UNIX for Dummies Questions & Answers

Replace character in certain column

I have a data with 4 columns separete with tab delimiter as following: A B {123} E{456} C D {789} T{159} My expected result as following: (become 5 columns) A B {123} E {456} C D {789} T {159} Is there any way to separate the 4th column to 2 field by using the tab delimiter? I... (6 Replies)
Discussion started by: newbie2011
6 Replies

7. UNIX for Dummies Questions & Answers

merging rows into new file based on rows and first column

I have 2 files, file01= 7 columns, row unknown (but few) file02= 7 columns, row unknown (but many) now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there e.g. file 01 James|0|50|25|10|50|30... (1 Reply)
Discussion started by: A-V
1 Replies

8. Shell Programming and Scripting

Combining rows in a text file with a character limit

I have a file that contains several thousands rows. Here is an example. ^411912$ ^487267$ ^643776$ ^682249$ ^687737$ ^692328$ ^693767$ ^695483$ ^697289$ ^757411$ ^776688$ ^778953$ ^806123$ ^872262$ ^877877$ ^839837$ ^76666$ ^72018$ ^23330$ (3 Replies)
Discussion started by: justinb_155
3 Replies

9. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

10. Shell Programming and Scripting

read in a file character by character - replace any unknown ASCII characters with spa

Can someone help me to write a script / command to read in a file, character by character, replace any unknown ASCII characters with space. then write out the file to a new filename/ Thanks! (1 Reply)
Discussion started by: raghav525
1 Replies
Login or Register to Ask a Question