Text processing using Linux

Question

I need to write a Linux program, which reads portions of data from a csv file and write into a text file in the following pattern.

NAME : FROM= -100 -346 -249 -125 TO= -346 -249 -125 100 COLOR= COLOR1 COLOR2 COLOR3 COLOR4

NAME will be a fixed row,

FROM and TO information should be retreived from csv file and

COLOR information can be hard coded array of colors from program itself.

From csv data below, the first value(-100) under MIN will be the first value(-100) under FROM of text file. The last value(100) from excel MAX column will be the last value(100) under text file TO column. The values under VALUE column in excel will be rounded and used as TO and FROM per pattern shown.

Data,VALUE,
100,-345.8756,
200,-249.3654,
300,-125.3554,
COUNT,MIN,MAX
1,-100,-98
93,84,86
98,94,96
99,96,98
100,98,100

Answer 1

An awk program:

BEGIN           { FS = ","; col = "COLOR1"; i = 1 }
!/^[0-9]/       { next }

$3 == ""        {
                    val = sprintf("%.0f", $2)
                    data = data ? data OFS val : val
                    col = col OFS "COLOR" ++i
                    next
                }

$2 < min        { min = $2 }
$3 > max        { max = $3 }

END {
        printf("NAME:\nFROM= %s %s\nTO= %s %s\nCOLOR= %s\n",
               min, data, data, max, col)
    }

Testing it:

$ awk -f script.awk file.csv
NAME:
FROM= -100 -346 -249 -125
TO= -346 -249 -125 100
COLOR= COLOR1 COLOR2 COLOR3 COLOR4

I'm assuming there may be more rows (but not columns) of data, both in the first and second section of the file, and that there should be as many COLOR entries on the COLOR row as there are data values on the FROM and TO lines in the output.

Non-numeric lines are skipped by the !/^[0-9]/ block.

The data that is repeated in the output is picked up by the third block ($3 == ""). That block creates a data and a col string with the appropriate values. Rounding is performed using sprintf() with a format specifying a floating point number with no decimal places.

The minimum and maximum values are picked up from the later section of the input file as the minimum of the second column and the maximum of the third column.

The END block prints out the resulting report.

Answer 2

awk solution (for your current input file):

awk 'NR>1 && NR<5{ 
         v=sprintf("%.0f", $2); values=(values)? values FS v : v; 
         lbl="COLOR"; col_item=lbl""++c; col=(col)? col OFS col_item : col_item 
     }
     NR==6{ from=$2 }
     END{ 
         print "NAME :"; print "FROM=",from,values; 
         print "TO=",values,$3; print "COLOR=",col,lbl""++c 
     }' file

The output:

NAME :
FROM= -100 -346 -249 -125
TO= -346 -249 -125 100
COLOR= COLOR1 COLOR2 COLOR3 COLOR4