0

I need to parse a CSV file for certain fields, and based on matching a pattern I need to add fields together. I've succeeded in setting variables, but need help figuring out how to add them when there may be 1-20 variables. (Or possibly another, simpler way to approach this.)

Source file contents example:

Server-Name,Volume-Name,Vol-Size,Logical-Space-In-Use
FTWTRAQNETSQL01,FTWTRAQNETSQL01_e,2008,1989
FTWTRAQNETSQL01,FTWTRAQNETSQL01_f,106,63.698
FTWTRAQNETSQL02,FTWTRAQNETSQL02_e,2008,1989
FTWTRAQNETSQL02,FTWTRAQNETSQL02_f,106,4.155
ftwvocmpsqln01,ftwvocmpsqln01_1,1002,21.047
ftwvocmpsqln01,ftwvocmpsqln01_2,104,55.379
ftwspsqln02,ftwspsqln02_H,501,0
ftwvocmpsqln02,ftwvocmpsqln02_1,1002,20.732
ftwvocmpsqln02,ftwvocmpsqln02_2,104,55.380

Output should be one line for each each unique server name, and adding all the field 3 values and all the field 4 values. Servers can have many volumes, some as many as 20. Desired file output would be:

Server-Name,Vol-Size,Logical-Space-In-Use
FTWTRAQNETSQL01,2114,2052.698
FTWTRAQNETSQL02,2114,1993.155
ftwvocmpsqln01,1106,76.426
ftwspsqln02,501,0
ftwvocmpsqln02,1106,76.112

I can do this in about 7 seconds in Excel, but so far haven't figured out a solution for automating with bash (or other shells.)

This is the code I have so far, only looking at field 3. It correctly sets variables for each iteration of unique servers, but I can't figure out how to do the addition with a variable number of variables.

for i in $( awk -F , '{print $1}' $REPORT | grep -v Server-Name | uniq )
do
    c=0
    for num in $( grep $i $REPORT | awk -F , '{print $3}' )
        do
        eval "var$c=$num";
        c=$((c+1));
    done
done
1

Not a shell, but "unix way":

awk -F',' 'NR==1; NR>1{s3[$1]+=$3; s4[$1]+=$4} END { for(i in s3){printf("%s,%s,%s\n",i,s3[i],s4[i])} }' file

The order of the output will (probably) not match the input order.

Description:

awk                   # use awk.
-F','                 # set the field separator as comma (,)
'                                         # start an awk script.
   NR==1;                                 # print first line (header)
   NR>1{                                  # for lines other than first
         s3[$1]+=$3;                      # add values on third field
         s4[$1]+=$4                       # add values on fourth field
       }                                  # close the previous {
         END {                            # after all lines have been read
               for(i in s3){              # for each index of the array
                                          # (all unique values of field $1)
                             printf("%s,%s,%s\n",i,s3[i],s4[i])   # print values.
                           }              # close the for loop.
             }                            # close the END loop.
' file                                    # end script code and name the file.
  • That seems to work! Thanks! (Now I just have to take it apart so I understand what you're doing.) – TeresaM Jun 20 at 19:00
  • @TeresaM Description added .... – Isaac Jun 20 at 19:47
  • Thank you, @Isaac. Much appreciate it, so I don't have to ask the next time I need this type function. – TeresaM Jun 20 at 21:31
1

With GNU datamash:

$ datamash -t, --header-in groupby 1 sum 3,4 < file.csv
FTWTRAQNETSQL01,2114,2052.698
FTWTRAQNETSQL02,2114,1993.155
ftwvocmpsqln01,1106,76.426
ftwspsqln02,501,0
ftwvocmpsqln02,1106,76.112
  • The datamash command isn't available. I'm working on an Isilon cluster, which has a scaled-down Linux kernel for the OS. Thanks, though! – TeresaM Jun 20 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.