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

user_17356927 0
Viewed 376

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

csv cmd awk batch-file windows
4 Answers

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
user_17450010

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
user_58665800

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
user_15816580

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!
)
user_7785600
Related