Separating fields and values from log files

[eduardoamfm]

Hi everybody!

I have the following files: (MSC.080806.00, MSC.080806.01, MSC.080806.02...) each one has the same pattern, with two different START and STOP times and values:

...
START:2008/08/06 08:30:00 WED; STOP: 2008/08/06 09:00:00 WED;
...
TRK
KEY (COMMON_LANGUAGE_NAME)
INFO (OM2TRKINFO)
INCATOT PRERTEAB INFAIL NATTMPT NOVFLATB GLARE
OUTFAIL DEFLDCA DREU PREU TRU SBU
MBU OUTMTCHF CONNECT TANDEM AOF ANF
TOTU NANS ANSU ANSWER ACCCONG NOANSWER
...
75 RJOTLP1_TLMARC
2W 709 709
2272 0 0 3588 0 0
0 0 0 0 3672 0
0 0 3588 1200 0 0
3672 3369 2810 0 0 0

76 SPOBV2_FLO
2W 711 649
2272 0 0 3493 0 0
2 0 0 0 3707 0
0 0 3491 1216 0 0
3707 3334 3196 0 0 0
...
START:2008/08/06 09:00:00 WED; STOP: 2008/08/06 09:30:00 WED;
...
TRK
KEY (COMMON_LANGUAGE_NAME)
INFO (OM2TRKINFO)
INCATOT PRERTEAB INFAIL NATTMPT NOVFLATB GLARE
OUTFAIL DEFLDCA DREU PREU TRU SBU
MBU OUTMTCHF CONNECT TANDEM AOF ANF
TOTU NANS ANSU ANSWER ACCCONG NOANSWER
...
75 RJOTLP1_TLMARC
2W 709 709
3101 0 0 5095 0 0
4 0 0 0 5172 0
0 0 5091 1582 0 0
5172 4775 4025 0 0 0

76 SPOBV2_FLO
2W 711 649
3018 0 0 4960 0 0
8 0 0 0 5193 0
0 0 4952 1534 0 0
5193 4624 4402 0 0 0
...

Using another very valuable hint from you, I could separate the lines with START and STOP patterns to record them into MySQL after some cuts:

edu@ubuntu:~/routes# awk '/START/{ print; }' MSC.080806.20
START:2008/08/06 08:30:00 WED; STOP: 2008/08/06 09:00:00 WED;
START:2008/08/06 09:00:00 WED; STOP: 2008/08/06 09:30:00 WED;

And then, I separated the lines to save as columns names for my MySQL fields :

edu@ubuntu:~/routes# awk '/OM2TRKINFO/{ getline; print; }' MSC.080806.20
INCATOT PRERTEAB INFAIL NATTMPT NOVFLATB GLARE
INCATOT PRERTEAB INFAIL NATTMPT NOVFLATB GLARE

But, I have more 3 lines to do that:
edu@ubuntu:~/routes# awk '/OM2TRKINFO/{ getline; getline; print; }' MSC.080806.20
OUTFAIL DEFLDCA DREU PREU TRU SBU
OUTFAIL DEFLDCA DREU PREU TRU SBU

edu@ubuntu:~/routes# awk '/OM2TRKINFO/{ getline; getline; getline; print; }' MSC.080806.20
MBU OUTMTCHF CONNECT TANDEM AOF ANF
MBU OUTMTCHF CONNECT TANDEM AOF ANF

edu@ubuntu:~/routes# awk '/OM2TRKINFO/{ getline; getline; getline; getline; print; }' MSC.080806.20
TOTU NANS ANSU ANSWER ACCCONG NOANSWER
TOTU NANS ANSU ANSWER ACCCONG NOANSWER

I don´t think this getline; ...; getline way is the better way!!! It doesn’t look elegant or professional.

But the worst problem is how to get the fields values:

The file has:
75 RJOTLP1_TLMARC
2W 709 709
3101 0 0 5095 0 0
4 0 0 0 5172 0
0 0 5091 1582 0 0
5172 4775 4025 0 0 0

As an example, I need: values(FIELDS)

RJOTLP1_TLMARC (TRK)

3101 (INCATOT) 0 (PRERTEAB) 0 (INFAIL) 5095 (NATTMPT) 0 (NOVFLATB) 0 (GLARE)
And so on:
4 0 0 0 5172 0
0 0 5091 1582 0 0
5172 4775 4025 0 0 0

can anyone please help me with that? Sorry about the long post.

Best regards,

Eduardo

[edwardrsmith]

I have never done anything like that before but if I were you I would look into a scripting language like Python. I know you could do it with c but as I don't know if you have any programming experience python might be easier. I will look into it a little more when I get home and see if I can give any specific suggestions.

A couple quick questions though:

First, are you greating a new MySQL database or are you using an existing one? If you are using an existing one, are you making new tables or using existing tables?

Second, is this a one time exercise or do you need a method to repeat this process in the future?

Edward

[eduardoamfm]

Hi Edward,

In fact, I noticed I might use Perl. I am studying Perl to implement that (I have many years of programming experience in VB and just some months ago I installed an Ubuntu LAMP.) I think Perl fits well for that, but it seems that Perl has some problem running Awk embebed... So it means some more code lines.

1. It´ll be a new table as that or something like that: CREATE TABLE routes(DATE, TIME, TRK, INCATOT, PRERTEAB, INFAIL, NATTMPT, NOVFLATB, GLARE, OUTFAIL, DEFLDCA, DREU, PREU, TRU, SBU, MBU, OUTMTCHF, CONNECT, TANDEM, AOF, ANF, TOTU, NANS, ANSU, ANSWER, ACCCONG, NOANSWER) and then INSERT INTO routes VALUES (mm/dd/yyyy, 08:30, RJOTLP1_TLMARC, 2272, 0, 0, 3588, 0, 0, 0, 0, 0, 0, 3672, 0, 0, 0, 3588, 1200, 0, 0, 3672, 3369, 2810, 0, 0, 0)

2. I´ll run that at crontab every half hour 24 x 7! These files have around 1.5Mb. Nowadays, it is done by excell VB and recorded on the net a sheet with data and charts. Now, I have to upgrade that to LAMP! I always used Windows, so now I am a kind of happy and exciting about the company agreed that we have to kill all legacies and put everything on the www, so I got this Ubuntu LAMP to do that.

Thank you so much!

Eduardo