how to do the sum of 2 columns adding that into new column by reading the csv file in shell script

Question

I'm trying to implement shell script to read csv file and do the sum of 2 columns and adding into new column called total. But it's not successfully done. please suggest how can I achieve this.

my input csv file

a,b,c
1,2,3
4,5,6

expected output

a,b,c,total
1,2,3,5
4,5,6,11

Here a is a primary key

I tried below code to achieve this

echo "First arg: 1"
awk "{print $3 $2 """""" $1}"
echo "First arg: 2
awk -F, "{$(NF+1)=Null;}1" OFS=, file.csv
awk -F "," "{print $1,$2,$3,$2+$3}"

my output is:

C:\Users\inrenan\NIFI\NIFI-1~1.2-B\NIFI-1~1.2>awk -F "," "{print $1,$2,$3,$2+$3}" 
a b c 0
1 2 3 5
4 5 6 11

only I'm facing the issue is column name

"But it's not successfully done." What did you try that didn't work? — SamBob, Nov 08 '21 at 12:59
Moderator: this question is not a duplicate of https://stackoverflow.com/questions/62980230/use-awk-to-sum-all-cells-in-a-csv as currently marked. This question sums each row individually, that questions sums all columns — SamBob, Nov 08 '21 at 13:46

Ed Morton · Accepted Answer · 2021-11-08T14:07:28.380

This is the awk script you need:

BEGIN{ FS=OFS="," } { print $0, (NR==1 ? "total" : $2+$3) }

Do whatever magical incantation you have to to use it in Windows.

In Unix you'd just do:

awk 'BEGIN{ FS=OFS="," } { print $0, (NR==1 ? "total" : $2+$3) }' input
a,b,c,total
1,2,3,5
4,5,6,11

but I hear Windows has some odd quoting rules that are best avoided and so the common advice I see given for Windows is to save the script in a file named, say, script.awk (which you could also do in Unix) and invoke it as:

awk -f script.awk input
a,b,c,total
1,2,3,5
4,5,6,11

Based on the last script in your question, this might work for you in Windows but I really don't know Windows quoting rules:

awk "BEGIN{ FS=OFS=\",\" } { print $0, (NR==1 ? \"total\" : $2+$3) }" input

score 1 · Answer 2 · answered Nov 08 '21 at 14:31

With your shown samples, please try following awk program. Here mentioning an awk variable named fields which has value of 2,3 we could mention field numbers there separated by , and it will take care of taking sum of all those fields.

awk -v fields="2,3" '
BEGIN{
  FS=OFS=","
  num=split(fields,arr,",")
  for(i=1;i<=num;i++){
    field[arr[i]]
  }
}
FNR==1 { print $0,"total"; next }
FNR>1{
  sum=0
  for(i=1;i<=NF;i++){
    if(i in field){ sum+=$i }
  }
  $(NF+1)=sum
}
1
'  Input_file

score 0 · Answer 3 · answered Nov 08 '21 at 13:12

0

For a very cumbersome one-liner:

awk -F',' '{if (NR==1) {printf "%s,total\n",$0;} else {sum=0; for(i=2; i<=NF; i++) {sum +=$i}; {printf "%s,%s\n",$0,sum;}}}' file.csv

answered Nov 08 '21 at 13:12

SamBob

849
6
17

i'm using `.bat` file to write shell script and I faced the above error, I changed the `'` to `"` but facing same issue – RaRaIn Nov 08 '21 at 13:41
I guess you are running in windows. Are you sure then that you are running `bash` and not `cmd.exe`? What are you running your script with (which program and what command)? – SamBob Nov 08 '21 at 13:44
yes I'm running in the windows by using the `.bat` format file and I updated the output of my code please check it – RaRaIn Nov 08 '21 at 13:47
"In the windows" from cmd? Powershell? WSL? If either of the first two this is not a bash question as tagged. This solution is for bash. – SamBob Nov 08 '21 at 13:48

score 0 · Answer 4 · answered Nov 08 '21 at 17:39

This is a pure Batch-file that get the desired output:

@echo off
setlocal EnableDelayedExpansion

set /P "header=" < input.txt
echo !header!,total
for /f "skip=1 tokens=1-3 delims=," %%a in (input.txt) do (
   set /A "total=%%b+%%c"
   echo %%a,%%b,%%c,!total!
)

how to do the sum of 2 columns adding that into new column by reading the csv file in shell script

4 Answers4