To group the text (rows) by similar columns-names in a file

Shell Programming and Scripting
1

As part of some report generation, I've written a script to fetch the values from DB. But, unluckily, for certain Time ranges(1-9.99,10-19.99 etc), I don't have data in DB.
In such cases, I would like to write zero (0) instead of empty. The desired output will be exported to csv file.
Possibility of fixed columns are: DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+

I've a file with below content:

DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  2017-07-18  10  1-9.99    1:100% 
 2017-07-18  11  60+       3:100%  
2017-07-18  12  40-49.99  1:50%     60+       1:50% 
 2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%    30-39.99  2:4.88%  60+  28:68.29%  
2017-07-18  14  40-49.99  1:14.29%  60+       6:85.71% 
 2017-07-18  16  60+       1:100%  
2017-07-18  17  1-9.99    1:7.69%   30-39.99  1:7.69%   60+       11:84.62%

Desired output is:

DATE:HH:1-9.99,10-19.99,20-29.99,30-39.99,40-49.99,50-59.99,60+
---------------------------------------------------------------------
  **2017-07-18  10  1-9.99    1:100%  0                   0                       0                       0                   0  
2017-07-18  11  0                   0                   0                       0                       0                   60+  3:100%  
2017-07-18  12  0                   0                   0                       0                       40-49.99  1:50%     60+  1:50%  
2017-07-18  13  1-9.99    5:12.2%   10-19.99  4:9.76%   20-29.99  2:4.88%       30-39.99  2:4.88%       0                   60+  28:68.29%  
2017-07-18  14  0                   0                   0                       0                       40-49.99  1:14.29%  60+  6:85.71%  
2017-07-18  16  0                   0                   0                       0                       0                   60+  1:100% 
 2017-07-18  17  1-9.99    1:7.69%   0                   0                       30-39.99  1:7.69%       0                   60+  11:84.62%**
2

Is this a homework assignment? Homework and coursework questions can only be posted in the Homework & Coursework Questions forum under special homework rules.

Please review the rules, which you agreed to when you registered, if you have not already done so.

If you did not post homework, please explain the company you work for and the nature of the problem you are working on.

If you did post homework in the main forums, please review the guidelines for posting homework and repost.

And, if this is not homework, please tell us what operating system and shell you're using and show us what you have attempted to do to solve this problem on your own.

With what you have shown us, I don't understand what you're trying to do. You say you want CSV output, but you show us desired output that has colons and commas as separators only on the heading line. The data lines following the heading sometimes have leading a leading space, sometimes two leading spaces and two asterisks, and one line has two trailing asterisks. And the fields in all of the data lines are separated by varying numbers of spaces, not by commas (or any other consistent field separator character.

Please clearly explain what you are trying to do and show us desired sample output that matches that description (in CODE tags).

1 Like