CODEWITHSUNDEEP
bashshellawkscripting
Sandy
Sandy

Reputation: 449

replace particular column value using awk if found

How can I find and replace value for particular column using awk?

Say for example -> I have a file test having the content:

"abc":"100"::"new"

"xyz":"200":"mob":"old"

"lmn":"300"::"new"

"pqr":"400":"mob2":"new"

Now, if 3rd column is blank then I want to replace the blank value from "N/A" otherwise print the line as it is, so that the output would be like:

"abc":"100":"N/A":"new"

"xyz":"200":"mob":"old"

"lmn":"300":"N/A":"new"

"pqr":"400":"mob2":"new"

Although I got the output using awk through below command:

awk -F":" '{
    if ( $3 == "")
        print $1":"$2":\"N\/A\":"$4
    else
        print $0
}' test

But here I am using the hard coded values for each column like $1, $2, so if the blank column changes in other example from 3rd to xyz then have to change the same in command again. Is there any other way to get the same output using awk and without using hard coded values for columns? Thanks for your help.

Upvotes: 2

Views: 8913

Answers (4)

user1934428
user1934428

Reputation: 22366

First, let's simplifiy your present program a bit:

awk -F: 'BEGIN {OFS=FS} {       
  if ( $3 == "") $3="N/A"
  print $0
}' test

Now we can make two things variable: The column to test, and the replacement string. Hence, the body of the program will look something like

if ( $fieldnumber == "" ) $fieldnumber=replacement

What remains to be done, is to fill in the variables. If you look at the man page of awk, you see that the option -v allows us to specify the initial value for an awk variable. 

awk -F: -v fieldnumber=... -v replacement=...

This allows you to fill this variable from wherever you like - parameter of your shell script, environment variable etc.

UPDATE: Fix output field separator (OFS) UPDATE: Fix syntax error

Upvotes: 3

Ed Morton
Ed Morton

Reputation: 204731

The right way to do this, using GNU awk for FPAT and a modified input file to demonstrate that it works even when colons are present within quoted fields:

$ cat tst.awk
BEGIN {
    FPAT = "([^:]*)|(\"[^\"]+\")"
    OFS = ":"
}
$3 == "" { $3 = "\"N/A\"" }
{ print }

$ cat file
"abc:def":"100"::"new"
"xyz":"200":"mob":"old"
"lmn":"123:456:300"::"new"
"pqr":"400":"mob2":"new"
"stu":"600":"foo::bar":"more"

$ awk -f tst.awk file
"abc:def":"100":"N/A":"new"
"xyz":"200":"mob":"old"
"lmn":"123:456:300":"N/A":"new"
"pqr":"400":"mob2":"new"
"stu":"600":"foo::bar":"more"

Upvotes: 2

Yingyu YOU
Yingyu YOU

Reputation: 369

How about below piece of gawk code:

BEGIN {
    FS=":"
    OFS=":"
}
{
    for(i=1; i<=4; i++) {
        if ($(i) == "") field[i] = "N/A"
        else field[i] = $(i)
    }
    if ($0 != "") print field[1],field[2],field[3],field[4]
}

-- Or --
Maybe below piece of bash script is much simpler:

#!/bin/bash
export IFS=":"
while read a b c d; do
    echo "${a:-N/A}:${b:-N/A}:${c:-N/A}:${d:-N/A}"
done

with input redirection, i.e. this_bash_script.sh < your_test_input.txt

Upvotes: 0

oliv
oliv

Reputation: 13259

Using GNU awk:

awk -v RS='[:\n]'  '!NF{$0="\"N/A\""}{printf "%s%s",$0,RT}' test

The record separator RS is set to capture the data between the semi-colon :.

If there isn't any field (!NF), then set the want string.

The printf statement write the data and the record separator RT for the current record.

Upvotes: 0

Related Questions