How to search and replace a value in a particular column in unix

Question

I am working on a shell script in AIX which has some code to update certain values in a text file. The file is the format as shown below:

# cat $FILE
instance1   13  16
instance2   14  12
instance4   58  76
instance15  44  91
instance102 31  10
instance112 12  38

I am trying to search by instance name and replace the values in column 2 and column 3 with new values I was able to do it using perl as follows:

# for i in `cat $FILE|awk '{print $1}'`;do 
# perl -pi -e "s/`grep -i $i $FILE|awk '{print $2}'`/$NEW_VAL_2/ if /$i/" $FILE

# perl -pi -e "s/`grep -i $i $FILE|awk '{print $3}'`/$NEW_VAL_3/ if /$i/" $FILE
# done

This worked but then I realized that it is replacing every occurrence of the value. For example, in the last row it is replacing the value in column 2 but also the last two characters of the instance name.

Example:

    # i = instance112
    # NEW_VAL_2 = 99
    # perl -pi -e "s/`grep -i $i $FILE|awk '{print $2}'`/$NEW_VAL_2/ if /$i/" $FILE

    Output:

        instance199 99  38

How can I search for the row by instance name and replace only the values in a particular column?

score 0 · Answer 1 · answered Oct 16 '19 at 00:03

In awk;

awk -v newval="$NEW_VAL_2" -v i="$i" '
  BEGIN{OFS="\t"}
  $1 == i { $2 = newval } 1' $FILE > newfile

See How do I use shell variables in an awk script for more about -v and why it's better than trying to insert variables in the middle of a script string.

score 0 · Answer 2 · answered Oct 16 '19 at 14:55

Here is a perl one-liner solution. It is easier and faster to use pure perl here (with pattern matching) than a mixture of grep, awk and perl.

$ export FILE=foo

$ cat $FILE
instance1       13      16
instance2       14      12
instance4       58      76
instance15      44      91
instance102     31      10
instance112     12      38

$ export i=instance112
$ export NEW_VAL_2=99
$ export NEW_VAL_3=11

$ perl -lne 'if ( m{ \A $ENV{i} \t }xms ) { $_ = join "\t", @ENV{ qw( i NEW_VAL_2 NEW_VAL_3 ) }; } print; ' foo
instance1       13      16
instance2       14      12
instance4       58      76
instance15      44      91
instance102     31      10
instance112     99      11

score 0 · Answer 3 · answered Oct 18 '19 at 09:52

Let ITEM be the instance line to be modified. Let NV2 and NV3 be the new values of the 2nd and 3rd fields.

Here's an example:

ITEM=instance102; NV2=100; NV3=80; sed -E -e "s/^${ITEM}(\\s+)(\\S+)(\\s+)(\\S+)(\\s*)\$/${ITEM}\\1${NV2}\\3${NV3}\\5/" $FILE

It also preserves the original spacing characters: be it tab, space, orcombination of tabs and spaces. It keeps original spacing even when different spacing styles are used for different lines.

How to search and replace a value in a particular column in unix

3 Answers3