0

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

enter image description here

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.

  • Thanks @Kusalananda for the script. Yes, your assumptions on rows and colors are accurate. I might be doing something incorrect, but I get output as NAME: FROM= TO= COLOR= COLOR1. I am testing with the exact same data provided above saved in a file. Can you think of anything that I might be missing? Also, I provided COLOR1, COLOR2, COLOR3 as sample data, is there anyway to pick colors as (RED, GREEN, etc.). There can be a maximum of 16 colors? Thanks for your help! – NewCoder Sep 25 '17 at 16:08
  • @NewCoder Save the script exactly as it is to a file and run it as I did above. I have tested it with the given text data with GNU awk and a couple of other awk implementations. As for the colors; since there are no discernible information in the data as to what these should actually be, I can't really do much more than what I did to output them. – Kusalananda Sep 25 '17 at 16:12
  • I followed your instructions as-is @Kusalananda. I think the problem is - if I use the text provided in the question I get the required output but if I use the csv file provided in the question (screenshot) it doesn't pull the from and to values. – NewCoder Sep 25 '17 at 17:28
  • @NewCoder Then update the question with the correct data. – Kusalananda Sep 25 '17 at 17:29
  • Oh okay @Kusalananda, I will try it. I updated the question with correct data. – NewCoder Sep 25 '17 at 21:35
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
  • Thanks @RomanPerekhrest for the script. I might be missing something, but got this output when I executed it exactly as provided on sample file. NAME : FROM= 0 0 0 TO= 0 0 0 COLOR= COLOR. I have to automate the number of COLOR's based on number of values present on FROM and TO columns. For example, in the above output, there has to be four colors. There can be a maximum of 16 colors. Thanks for your help! – NewCoder Sep 25 '17 at 16:18
  • @NewCoder, but you have written COLOR information can be hard coded array of colors – RomanPerekhrest Sep 25 '17 at 16:33
  • @NewCoder, look here ibb.co/eQPUyk – RomanPerekhrest Sep 25 '17 at 16:39
  • Sorry for not being very clear @RomanPerekhrest. I thought if there is a way to pick the colors based on hard coded values (max 16 colors) in the file, it would help. Thanks for the screenshot. It helped me figure out the issue - if I use the text provided in the question I get the required output but if I use the csv file provided in the question (screenshot) it doesn't pull the from and to values. – NewCoder Sep 25 '17 at 17:31
  • @NewCoder, can you post the actual/real output of your csv file to the question? Run cat yourfile in the command line, then copy-paste – RomanPerekhrest Sep 25 '17 at 20:35

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.