Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

String Replacement in a column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting String Replacement in a column
# 1  
Old 08-28-2014
String Replacement in a column
I have a text file with the following contents. I am trying to check the column "COL5". If the value is not "0" then I need to replace the value to NA only in COL5.
The problem is that I don't have a clear delimiter here, I have only the column names.

Code:
cat input.txt

-08/27/14:08:30:01--

NAME                 COl1   COL2 COL3   COL4  COL5
-------------        ------ ---- ------ ----- ----
NAME1234            NY      NA    777   77    0

NAME1235            RU      NA    100   90    NA

NAME1244            IN      NA    -     30    5
NAME3126            SG      NA    -     50    7

I need the output as below:
Code:
cat input.txt

-08/27/14:08:30:01--

NAME                 COl1   COL2 COL3   COL4  COL5
-------------        ------ ---- ------ ----- ----
NAME1234            NY      NA    777   77    0

NAME1235            RU      NA    100   90    NA

NAME1244            IN      NA    -     30    NA
NAME3126            SG      NA    -     50    NA

Last edited by ctrld; 08-28-2014 at 05:00 AM.. Reason: Format correction
# 2  
Old 08-28-2014
try
Code:
nawk '{if ($6 != "0" && $6 && $6 != "COL5" && $6 != "----") {gsub($6"$","NA",$0)}; { print $0}}' input.txt

Last edited by Makarand Dodmis; 08-28-2014 at 06:03 AM..
This User Gave Thanks to Makarand Dodmis For This Post:
ctrld
# 3  
Old 08-28-2014
The problem with above proposal is, it will replace any column having a value equal $6 with "NA". Try (with e.g col3 set to 5)
Code:
awk '$6 && D   {gsub($6"$","NA",$0)}
     $1~/---/  {D=1}
     1
    ' file

-08/27/14:08:30:01--

NAME                 COl1   COL2 COL3   COL4  COL5
-------------        ------ ---- ------ ----- ----
NAME1234            NY      NA    777   77    0

NAME1235            RU      NA    100   90    NA

NAME1244            IN      NA    5     30    NA
NAME3126            SG      NA    -     50    NA

---------- Post updated at 11:07 ---------- Previous update was at 10:53 ----------

@Makarand Dodmis: Please don't edit your original script
Quote:
Originally Posted by Makarand Dodmis
try
Code:
nawk '{if ($6 != "0" && $6 && $6 != "COL5" && $6 != "----") {gsub($6,"NA",$0)}; { print $0}}' input.txt

after someone pointed out a possible quirk.
These 2 Users Gave Thanks to RudiC For This Post:
ctrld drl
# 4  
Old 08-29-2014
Thanks Makarand and RudiC.

Unfortunately I am facing one issue. It was my mistake. I didn't tell that for some rows I have few more columns to the end.
The solution provided by RudiC is working only for rows with exactly six columns. If I have more columns the row is untouched.
# 5  
Old 08-29-2014
This matches column #6 in the table, you were not clear if the headings should be used to match the column or it's ordinal position.

Here I'm using the ----- ------ line to get the columns start and length:



Code:
awk '$6 && S {$0=substr($0,1,S) sprintf("%-*s", L, "NA") substr($0,S+L+1) }
/^--/ {
L=length($6)
F=x
for(i=1;i<6;i++) F=F $i" +"
match($0,F)
S=RLENGTH-1
print "S=" S " L=" L

}
1' infile

-08/27/14:08:30:01--

NAME                 COl1   COL2 COL3   COL4  COL5  COL..     COLn
S=45 L=4
-------------        ------ ---- ------ ----- ----  -------   ----
NAME1234            NY      NA    777   77    0     0         PI

NAME1235            RU      NA    100   90   NA     NA        0

NAME1244            IN      NA    5     30   NA     5         NA
NAME3126            SG      NA    -     50   NA     0         7

Last edited by Chubler_XL; 08-29-2014 at 03:08 AM..
This User Gave Thanks to Chubler_XL For This Post:
ctrld
# 6  
Old 08-29-2014
Thanks Chubler! It worked fine.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters

input "A","B","C,D","E","F" "S","T","U,V","W","X" "AA","BB","CC,DD","EEEE","FFF" required output: "A","B","C,D","C,D","F" "S", T","U,V","U,V","X" "AA","BB","CC,DD","CC,DD","FFF" tried using awk but double quotes not preserving for every field. any help to solve this is much... (5 Replies)
Discussion started by: khblts
5 Replies

2. Shell Programming and Scripting

String replacement

Hi, I have a text file where all records come in one line (single line file), each record starts with 'BUCH' and ends with '@&' and if data is not there we get space instead. between '@&' and next record there might be some spaces, now I want to remove those spaces between '@&' and 'BUCH'. ... (4 Replies)
Discussion started by: maks475
4 Replies

3. UNIX for Dummies Questions & Answers

Excluding a specific column from sed replacement

Hi, I would like to replace all 0's to 1's throughout my text file. However I do not want column 3 altered. How do I go about doing that? Thanks! (2 Replies)
Discussion started by: evelibertine
2 Replies

4. Shell Programming and Scripting

String replacement.

Dear Friends, I want to replace following line with given line. It should grep/search following string in a file (input.txt) M/M SRNO: 000M/6200-0362498 COSMETIC PRO MALE FEMALE Once found it should replace it to following string. T_DLHNNO: 000M/6200-0362498 ... (7 Replies)
Discussion started by: anushree.a
7 Replies

5. Shell Programming and Scripting

String replacement

Hi All, I have below file which has data in below format. #$ | AB_100 | AB_300 ()| AB_4 @*(% | AB-789 i want o/p as below format. | AB_100 | AB_300 | AB_4 | AB-789 So here there is no standard format. How we can achieve the same in unix ? Regards, (3 Replies)
Discussion started by: gander_ss
3 Replies

6. Shell Programming and Scripting

String replacement

I have one string string1=user/password:IP_ADDR:Directory I need to replace string1 value like store into string2 string2=user password:IP_ADDR:Directory i.e replace "/" character by '<space>' character But i wouldn't use any file in the meantime. Please help me......................... (6 Replies)
Discussion started by: mnmonu
6 Replies

7. Shell Programming and Scripting

String replacement

Hi I am new to shell scripting but i manage to do some simple things. I am trying to replace a string in one file. I am using sed to replace but it is not permanently writing to the file, rather it is temporary. I want to know whether is there any another method to replace a string in a file... (7 Replies)
Discussion started by: reddybs
7 Replies

8. Shell Programming and Scripting

Replacement of string

Hi I have a text file which contains the following. AAA,BBB,CCC,DDD AAA,BBB,CCC,DDD AAA,BBB,CCC,DDD How can I replace all CCC with 888, with other contents inside the file remain unchange? Please advice Desired output: AAA,BBB,888,DDD AAA,BBB,888,DDD AAA,BBB,888,DDD (1 Reply)
Discussion started by: c0384
1 Replies

9. Shell Programming and Scripting

AWK String replacement

I have an xml file with following tags <NewTag>value123</xyz> <NewTag>value321</abcd> I have to replace the values in between the tags with some value ( VAL1/VAL2) but the thing the ending tag can be any thing, for this i need a awk command currently i am using this but it... (5 Replies)
Discussion started by: subin_bala
5 Replies

10. Shell Programming and Scripting

sed problem - replacement string should be same length as matching string.

Hi guys, I hope you can help me with my problem. I have a text file that contains lines like this: 78 ANGELO -809.05 79 ANGELO2 -5,000.06 I need to find all occurences of amounts that are negative and replace them with x's 78 ANGELO xxxxxxx 79... (4 Replies)
Discussion started by: amangeles
4 Replies
Login or Register to Ask a Question