CODEWITHSUNDEEP
shellawk
CodeDevotion
CodeDevotion

Reputation: 307

AWK replace null column with the column value of next row

I have following sample file with missing column values for which I would like to replace with next available value from same column.

cat test.txt
11.2.0.1,ORA1,ORACLE
11.2.0.4,ORA2,ORACLE
11.2.0.3,ORA3,ORACLE
12.2.0.1,ORA4,ORACLE
,ORA5,ORACLE
,ORA6,ORACLE
12.2.0.2,ORA7,ORACLE
,MYS1,MYSQL
5.1,MYS2,MYSQL

Here is what I am trying to do:

 cat test.txt |awk '{printf("%s,%s,%s\n", $11,$3,$1);}'|awk -F',' 'BEGIN{OFS=","}
   {
        for (i=1; i<=NF; i++)
        if ($i=="")
            --Read next column value
            --If next column is null, read futhur next
            -- Assign next available value to $i
        print
    }'

Expected output:

11.2.0.1,ORA1,ORACLE
11.2.0.4,ORA2,ORACLE
11.2.0.3,ORA3,ORACLE
12.2.0.1,ORA4,ORACLE
12.2.0.2,ORA5,ORACLE
12.2.0.2,ORA6,ORACLE
12.2.0.2,ORA7,ORACLE
5.1,MYS1,MYSQL
5.1,MYS2,MYSQL

Thanks

Upvotes: 2

Views: 1160

Answers (2)

chaos
chaos

Reputation: 9272

With awk:

tac file | awk -F, '$1{l=$1} !$1{$1=l} OFS=","' | tac
  • tac is like cat but reverses the file line by line.
  • awk -F, sets the field separator to comma.
  • $1{l=$1} if the first field $1 is set, set the l variable and print the line
  • !$1{$1=l} if the first field $1 is not set, take the value from the l variable and print the line.
  • OFS="," sets the output field separator to comma.
  • tac finally reverse the file back.

The output:

11.2.0.1,ORA1,ORACLE
11.2.0.4,ORA2,ORACLE
11.2.0.3,ORA3,ORACLE
12.2.0.1,ORA4,ORACLE
12.2.0.2,ORA5,ORACLE
12.2.0.2,ORA6,ORACLE
12.2.0.2,ORA7,ORACLE
5.1,MYS1,MYSQL
5.1,MYS2,MYSQL

Upvotes: 2

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89547

You can do it like this:

awk -F, '$1==""{a[n++]=$0;next} n{for (i=0;i<n;i++) print $1 a[i]; n=0} 1' file

details:

$1=="" {          # if the first field is empty
    a[n++]=$0     # store the whole line at index n and increment n
    next          # jump to the next line
}

n {                    # if n isn't zero
    for (i=0;i<n;i++)  # loop over stored lines indexes
        print $1 a[i]  # and print lines starting with the current first field
    n=0                # set n to 0
}

1  # true, print the current line

Upvotes: 3

Related Questions