Update All Column Value by 1 if exist

Shell Programming and Scripting
1

Guys,

Running on Linux Ubuntu.

I need to find a method to automatically update all the rows for 2 columns and only if the rows not null.

e.g : based on the example data below, I want to update all rows under column 2 and 3 (COL2, COL3) with an increment of 1 and only if the value is not null.

raw data :

COL1,COL2,COL3,COL4
ABC,111,222,DF89
DEF,333, , EH98
HIJ,,666,YJ87

expected output :

COL1,COL2,COL3,COL4
ABC,112,223,DF89
DEF,333,,EH98
HIJ,,666,YJ87

Thanks,

2

What about lines 3 and 4?

Is there really supposed to be that much whitespace in it?

3

apologies. I've edited the unwanted spaces.

4

If you only wanted to edit line 2 like you've shown:

awk -F, -v OFS=, 'NR==2 { for(N=1; N<=NF; N++) if($N ~ /^[0-9]+$/) $N++ } 1' inputfile > outputfile
5

cool! that works on line 2.
Further question. What if I want to apply the same formula on all lines?

and which part of this code that apply the increment by 1 ? In case I want to apply another value let say 5

awk -F, -v OFS=, 'NR==2 { for(N=1; N<=NF; N++) if($N ~ /^[0-9]+$/) $N++ } 1' inputfile > outputfile

COL1,COL2,COL3,COL4 
ABC,111,222,DF89 
DEF,333,444,EH98 
HIJ,444,666,YJ87
6

That's not what you showed, though. Your example data only showed line 2 being changed.

And changing all lines might do something to your header line, which would be bad, unless you don't actually have a header line.

To modify absolutely all lines, remove the 'NR==2', which means, 'only run this code block when NR (line number) is 2'.

-F, and -v OFS=, set the input and output separators, so you don't accidentally change your commas into spaces.

awk -F, -v OFS=, '{ for(N=1; N<=NF; N++) if($N ~ /^[0-9]+$/) $N=($N+1) } 1' inputfile > outputfile

I've highlighted the part which changes the column and made the code a little more obvious. ++ is an operator borrowed from the C language, which means 'add 1 to this variable', that's the longhand equivalent.

$ doesn't mean variable in awk, though. Any unquoted string of letters is a variable. $ means "use this variable or expression as a column" -- so when N=4, $N gives you column 4. And $N=5 sets column 4 to the value of 5. Etc.

So, for every line, this loops over all columns, and adds 1 to any which are purely numbers(i.e. match the regex /[1]+$/ ).

The 1 at the end of the line makes it print every single line.

  1. 0-9 ↩ī¸Ž

7

If I understood the problem correctly, I think this might be closer to what was wanted:

awk '
BEGIN { FS = OFS = "," }
{       gsub(/ /, "") }
$2 != "" && $3 != "" && NR > 1 {
        $2 += 1
        $3 += 5
}
1' raw

This increments the second field by 1 and the 3rd field by 5 as long as both fields are non-null (after removing spaces). Change the values shown in red if you want to use different increments.
If the file raw contains:

COL1,COL2,COL3,COL4
ABC,111,222,DF89
DEF,333, , EH98
HIJ,,666,YJ87
DEF,333,444,EH98 
HIJ,444,666,YJ87

the output will be:

COL1,COL2,COL3,COL4
ABC,112,227,DF89
DEF,333,,EH98
HIJ,,666,YJ87
DEF,334,449,EH98
HIJ,445,671,YJ87
1 Like
8

ok. you are right. I don't want the header to be changed.
so, this is exactly what I want to do.

To change from line 2 onwards (exclude line1) on column 2 (COL2) and column 4 (COL4). Add +5 to the value of the 2 columns

COL1,COL2,COL3,COL4
ABC,111,HJUH,222
DEF,333,EH98,444
HIJ,555,YJ87,666

expected output :

COL1,COL2,COL3,COL4
ABC,116,HJUH,227
DEF,338,EH98,449
HIJ,560,YJ87,671

---------- Post updated at 01:32 AM ---------- Previous update was at 01:27 AM ----------

Thanks Don. :b: