mjhjmtu

I need help writing 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

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 %snTO= %s %snCOLOR= %sn",
 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.

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