awk to format data into table


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers awk to format data into table
# 1  
Old 04-30-2020
awk to format data into table

Dear team,


Here is the input file from which in need to get output table with values from each blocks



as BC, DATE & TIME(Apr 29, 08:10),NSMSSMRLTOT ,NSMSSRSUCC ,NSMSCMTOT , NSMSCMRSUCC





Code:
more SHMSGSERV_FRMT
Thu Apr 30, 08:13 +03+0300 2020
GJ15_183_IPA03_EPA49

BC0 --------------------------------  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 08:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1       75718  NSMSSMRLTOT       BC0
     2       69284  NSMSSRSUCC        BC0
     3       71264  NSMSCMTOT         BC0
     4       70947  NSMSCMRSUCC       BC0
     5        4785  NSMSRDOTOT        BC0
     6         380  NSMSRAOSUCC       BC0
     7        4292  NSMSCDOTOT        BC0
     8         383  NSMSCAOSUCC       BC0
     9          28  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 09:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      132640  NSMSSMRLTOT       BC0
     2      117741  NSMSSRSUCC        BC0
     3      121283  NSMSCMTOT         BC0
     4      120752  NSMSCMRSUCC       BC0
     5        4414  NSMSRDOTOT        BC0
     6         391  NSMSRAOSUCC       BC0
     7        3913  NSMSCDOTOT        BC0
     8         393  NSMSCAOSUCC       BC0
     9          29  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 10:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      136221  NSMSSMRLTOT       BC0
     2      120417  NSMSSRSUCC        BC0
     3      124147  NSMSCMTOT         BC0
     4      123444  NSMSCMRSUCC       BC0
     5        4717  NSMSRDOTOT        BC0
     6         557  NSMSRAOSUCC       BC0
     7        4316  NSMSCDOTOT        BC0
     8         560  NSMSCAOSUCC       BC0
     9          43  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0
==================================================  BC0
Wed Apr 29, 11:10 +03+0300 2020 (OK)  BC0
--------------------------------------------------  BC0
SHMSGSERV: ''                         BC0
                                      BC0
Number       Value  Name              BC0
     1      148035  NSMSSMRLTOT       BC0
     2      131664  NSMSSRSUCC        BC0
     3      135324  NSMSCMTOT         BC0
     4      134636  NSMSCMRSUCC       BC0
     5        4914  NSMSRDOTOT        BC0
     6         684  NSMSRAOSUCC       BC0
     7        4483  NSMSCDOTOT        BC0
     8         686  NSMSCAOSUCC       BC0
     9          64  NSMSMARTOT        BC0
    10           0  NSMSNRFSMTOT      BC0
    11           0  NTBRSMS           BC0
    12           0  NTBRSMSFAIL       BC0
    13           0  NSMSNRFSMTOT3     BC0
    14           0  NSMSRDTOT2        BC0
    15           0  NSMSDBRS          BC0
==================================================  BC0
                                      BC0


From below awk command ,the output as below. for first line only date and time printed whereas it should have all four values



Quote:
aawk '/-/{print a["s"] " " a["t"] " " a["o"] " " a["p"] " " a["q"] " " a["r"];delete a}/NSMSRDOTOT/{a["s"]=$4;next}/OK/{a["t"]=substr($0,4,14)}/NSMSSMRLTOT/{a["o"]=$2;next}/NSMSSRSUCC/{a["p"]=$2;next}/NSMSCMTOT/{a["q"]=$2;next}/NSMSCMRSUCC/{a["r"]=$2;next}END{print a["s"] " " a["t"] " " a["s"] " " a["o"] " " a["p"] " " a["q"] " " a["r"];}' SHMSGSERV_FRMT > SHMSGSERV_ST1

eshaqur@SA-00001256 ~
$ more SHMSGSERV_ST1

Apr 29, 08:10
BC0 Apr 29, 09:10 75718 69284 71264 70947
BC0 Apr 29, 10:10 132640 117741 121283 120752
BC0 Apr 29, 11:10 136221 120417 124147 123444
BC0 Apr 29, 12:10 148035 131664 135324 134636
BC0 Apr 29, 13:10 152827 135744 140179 139368
BC0 Apr 29, 14:10 150850 133703 138401 137207
BC0 Apr 29, 15:10 163147 145137 150564 149213
BC0 Apr 29, 16:10 155973 139644 144903 143648
BC0 Apr 29, 17:10 128839 113721 118061 116733
BC0 Apr 29, 18:10 109105 94244 98666 97336
BC0 Apr 29, 19:10 275173 253999 261961 260140
BC0 Apr 29, 20:10 117569 103414 107990 107031
BC0 Apr 29, 21:10 154075 140012 145490 143989
BC0 Apr 29, 22:10 147183 132533 138084 136705
BC0 Apr 29, 23:10 116536 103502 108550 107136
BC0 Apr 30, 00:10 90627 81116 84834 83888
BC0 Apr 30, 01:10 91603 81704 85411 84404
BC0 Apr 30, 02:10 48717 41932 44387 43707
BC0 Apr 30, 03:10 25568 19609 21621 21069
BC0 Apr 30, 04:10 24134 18141 20369 19818
BC0 Apr 30, 05:10 27281 21039 23085 22602
BC0 Apr 30, 06:10 20197 14548 16425 16006
BC0 Apr 30, 07:10 64339 57536 59944 59421
BC0 Apr 30, 08:10 67924 60928 63454 62923
BC0 75084 66988 69505 69025
Apr 29, 08:10
BC1 Apr 29, 09:10 73217 66623 68597 68300
BC1 Apr 29, 10:10 132352 117378 120546 120090
BC1 Apr 29, 11:10 136173 120310 123935 123225
BC1 Apr 29, 12:10 149633 132913 136477 135684
BC1 Apr 29, 13:10 155227 137945 142211 141269
BC1 Apr 29, 14:10 151757 134944 139330 138133
BC1 Apr 29, 15:10 163520 145789 150753 149422
BC1 Apr 29, 16:10 156308 140660 145262 144054
BC1 Apr 29, 17:10 128118 113240 117500 116231
BC1 Apr 29, 18:10 107601 92917 97077 95934
BC1 Apr 29, 19:10 275424 254697 262130 260353
BC1 Apr 29, 20:10 116571 103111 107399 106415
BC1 Apr 29, 21:10 151778 137831 143047 141578
BC1 Apr 29, 22:10 144230 130270 135890 134411
BC1 Apr 29, 23:10 116580 103719 108494 107286
BC1 Apr 30, 00:10 90498 81319 84828 83850
BC1 Apr 30, 01:10 90167 81645 84790 84000
BC1 Apr 30, 02:10 47141 40665 42801 42239
BC1 Apr 30, 03:10 26517 20543 22467 21901
BC1 Apr 30, 04:10 22754 16956 18901 18460
--More--(18%)



# 2  
Old 04-30-2020
That's because if your first printout trigger /-/ is hit, only that array element is set yet. On top, all further date/time stamps do not match the data printed with them; the data are shifted one line down.
Try finding another print trigger.

Last edited by RudiC; 04-30-2020 at 11:14 AM..
This User Gave Thanks to RudiC For This Post:
# 3  
Old 04-30-2020
A possible solution :
Code:
$ cat ./eshaqur.sh
awk '
    function printValues() {
        if (Values) {
            print  S, Date,   Value["NSMSSMRLTOT"],
                            Value["NSMSSRSUCC"],
                            Value["NSMSCMTOT"],
                            Value["NSMSCMRSUCC"];
        }
        delete Value;
    }
    $7=="(OK)" {
        printValues()
        Date = $2 " " $3 " " $4;
        S = $NF;
        next;
    }
    $1+0 {
        Value[$3] = $2;
        Values++;
        next ;
    }
    END {
        printValues()
    }
' SHMSGSERV_FRMT
$ ./eshaqur.sh
BC0 Apr 29, 08:10 75718 69284 71264 70947
BC0 Apr 29, 09:10 132640 117741 121283 120752
BC0 Apr 29, 10:10 136221 120417 124147 123444
BC0 Apr 29, 11:10 148035 131664 135324 134636

jpgir@LAPTOP-KJOA2D44 ~
$

This User Gave Thanks to aigles For This Post:
# 4  
Old 05-01-2020
Thanks a lot jgir


This one very quick/fast one liner.


After this awk i used seperate awk to process it further in order to insert new column which has values equal to ($6/$5)*100 or so on. Is it possible to include this as well in your one liner.


Quote:
awk '( $5 ~ /^[1-9]/ ){ newcol=(($6/$5)*100); newpos=length } { printf "%*-s %s\n", newpos, $0, newcol }' file
awk '( $5 ~ /^[1-9]/ ){ newcol=(($8/$5)*100); newpos=length } { printf "%*-s %s\n", newpos, $0, newcol }' file
awk '( $5 ~ /^[1-9]/ ){ newcol=(($8/$7)*100); newpos=length } { printf "%*-s %s\n", newpos, $0, newcol }' file
# 5  
Old 05-01-2020
Dear RudC,


Got it, After changing /-/ hit to /=/ issue resolved. Smilie
Thanks for really great support




Shanul
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

awk to convert dict file to table format

HI I have a file that looks like below abc {Seq('GATAGC', SingleLetterAlphabet()): 1, Seq('ATAGCG', SingleLetterAlphabet()): 1, Seq('TAGCGG', SingleLetterAlphabet()): 1} BBC {Seq('AGGATA', SingleLetterAlphabet()): 1, Seq('GGATAG', SingleLetterAlphabet()): 1, Seq('GATAGC',... (2 Replies)
Discussion started by: biofreek
2 Replies

2. Shell Programming and Scripting

Format Data - awk ..

/clusters/cluster-1/exports/storage-views/M1_CRE03_SV: Name Value ------------------------ --------------------------------------------------------------------------------------------------- caw-enabled true controller-tag - initiators ... (7 Replies)
Discussion started by: greycells
7 Replies

3. Shell Programming and Scripting

Represent the data in table format as in mail content

hi all, I want to Represent the data in table format in mail content sample data should be like this...but i m trying to send this table to mail using unix command...hw do i do????even i echoed the table contents ...doesnt work.... help me <table style="background-color:lightblue;">... (8 Replies)
Discussion started by: navsan
8 Replies

4. Shell Programming and Scripting

Help with perl script to output data in table format...

Hello, I need help with a perl script that will process a text file and match virtual server name to profile(s). the rest will be ignored. Virtual server name follows the word "virtual" in the begging of the line. There could be multiple profiles assigned to one virtual server. For example, ... (3 Replies)
Discussion started by: besogon
3 Replies

5. Shell Programming and Scripting

Need help getting data into legible format...maybe awk?

Good morning, I am still learning the powers of awk and perl. I am in need of a bit of help. I have a script on one of my launch systems...if that is even the word for it. Basically you can only ssh to a system if you are connected to this system due to firewalls. So from that system, I... (2 Replies)
Discussion started by: brianjb
2 Replies

6. Shell Programming and Scripting

Perl Script for reading table format data from file.

Hi, i need a perl script which reads the file, content is given below. and output in new file. TARGET DRIVE IO1 IO2 IO3 IO4 IO5 ------------ --------- --------- --------- --------- --------- 0a.1.8 266 236 ... (3 Replies)
Discussion started by: asak
3 Replies

7. Shell Programming and Scripting

Arrange / format data using awk

Input 217:fngadi4osa:fngadi4osa:M 217:415744:N/A 227:fngadi4osa:fngadi4osa: M 227:51200:N/A 228:fngadi4osa:fngadi4osa: M 228:102400:N/A 65:sapgt04:sapgt04: M 65:104448:N/A 228:fngadi4osa:fngadi4oma: M 228:102400:N/A Output 217:fngadi4osa:fngadi4osa:M 217:415744:N/A... (3 Replies)
Discussion started by: greycells
3 Replies

8. Shell Programming and Scripting

Using Awk for extracting data in specific format

please help me writing a awk script 001_r.pdb 0.0265185 001_r.pdb 0.0437049 001_r.pdb 0.0240642 001_r.pdb 0.0310264 001_r.pdb 0.0200482 001_r.pdb 0.0146746 001_r.pdb 0.0351344 001_r.pdb 0.0347856 001_r.pdb 0.036119 001_r.pdb 1.49 002_r.pdb 0.0281011 002_r.pdb 0.0319908 002_r.pdb... (5 Replies)
Discussion started by: phoenix_nebula
5 Replies

9. Shell Programming and Scripting

How to transpose a table of data using awk

Hi. I have this data below:- v1 28 14 1.72414 1.72414 1.72414 1.72414 1.72414 v2 77 7 7.47126 6.89655 6.89655 6.89655 6.89655 v3 156 3 21.2644 21.2644 20.6897 21.2644 20.6897 v4 39 3 1.72414 1.72414 1.72414 1.72414 1.72414 v5 155 1 21.2644 23.5632 24.1379 23.5632 24.1379 v6 62 2 2.87356... (2 Replies)
Discussion started by: ahjiefreak
2 Replies

10. Shell Programming and Scripting

data format from (4.56 0.7) -> 4.6(7) awk?!

Hi, I have to manipulate a data file which say reads like this {$index $value $error_on_value} aa 4.56 0.7 bb 123.456 0.00987 cc 987654 321 . . in easily human readable format of type aa 4.6(7) bb 123.456(1) cc 9.877(3)e+05 value rounded to 4.6 with error of 0.7 on the last... (4 Replies)
Discussion started by: ahan
4 Replies
Login or Register to Ask a Question