Re: Help to identify different strings

Hi John,

I agree - it is easy to parse each individual line in Table1 by applying
different queries checking whether a significant string is available or not.
The problem is that the number of combinations are about 300 pcs, so it must
be maintained in a table (Table2).

Table2 holds info about identifying string, character position where
parameter value starts and ends (alternatively a string which limits the
parameter value).

What I need is to read Table1 record-by-record and for each record start
checking the Table2 records, selecting the identifying string for each
individual alarm type, then check whether this string is available in the
Table1 line or not. If the string is found I then pick the other parameters
from Table2 which makes it possible to fully break down the Table1 line. If
the string is not found, I have to proceed to the next record in Table2 to
see if this suits. So it must be some sort of Do Until Loop running through
Table2 providing the each individual identifying string for comparison with
the Table2 record and, when the right record is found, also provide the other
parameters used to break-down the information in the Table 1 line.

Hope this clarifies.

Regards,
Gunnar

"John Nurick" skrev:

Hi Gunnar,

It sounds as if the number of lines in each log entry, the contents of
each line vary with the type of alarm, and therefore also the names,
number and order of the "parameters" you want.

In that situation, trying to fit everything on one line is not only
unnecessary, it actually makes both the parsing and the subsequent
statistical analysis much more difficult. I think you'd be much better
off using a normalised structure like this table, in which each entry
from the log is translated into several simple, identically structured
records:

tblLogParameters
ID* (autonumber, unique index, needed to ensure
that the items can be retrieved in the original order)
Timestamp (the timestamp of the log entry of which this
record is part)
EventType (e.g. "Boiler temp warning", "Generator stopped")
ParameterType (e.g. "Temperature at alarm", "Valve closed")
ParameterValue Double (e.g. 240, 1234)
ParameterUnits (e.g. "deg C", "deg/min", or nothing)

So these entries
1 * '* is the identifier for a new alarm message
2 Boiler temp warning. Actual value 240 degC
3 Increase rate 12.7 deg/min
4 Valve 1234 closed
5 *
6 Generator stopped
7 Valve 2345 and 678 opened

might produce these records:

ID, Timestamp, EventType, ParameterType, ParameterValue, ParameterUnits
1, 2006/04/15 18:06:55, "Boiler temp warning", "Temperature at alarm",
240.0, "deg C"
2, 2006/04/15 18:06:55, "Boiler temp warning", "Increase rate", 12.7,
"deg/min"
3, 2006/04/15 18:06:55, "Boiler temp warning", "Valve closed", 1234,
NULL
4, 2006/04/15 20:12:00, "Generator stopped", "Valve opened", 2345, NULL
5, 2006/04/15 20:12:00, "Generator stopped", "Valve opened", 678, NULL

To populate the table, I'd write code that reads the log file line by
line, parses the lines and appends the corresponding records to the
table. Your idea of using a second table with information on the lines
that will need to be parsed is sound - but if you parse individual lines
in the log file rather than the much more complicated ones produced by
stuffing a whole log entry into one line, you'll probably find that each
line fits one of a small number of patterns, such as

<Event type>
e.g. "Generator stopped"

<Event type> <Parameter type> <Parameter value> <Parameter units>
e.g. "Boiler temp warning. Actual value 240 degC"

<Parameter type> <Parameter value> <Parameter units>
e.g. "Increase rate 12.7 deg/min"

<Noun> <Identifier> <Verb>
e.g. "Valve 1234 closed"

Hope this helps,

John


On Sat, 15 Apr 2006 08:52:02 -0700, Gunnar
<Gunnar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi John and Marshall,

Below some more details about the tables

Table1 (alarm log)
1 * '* is the identifier for a new alarm message
2 Boiler temp warning. Actual value 240 degC
3 Increase rate 12.7 deg/min
4 Valve 1234 closed
5 *
6 Generator stopped
7 Valve 2345 and 678 opened
and so on

Table 2 (holds typical string identifiers in order to break down Table 1):
1 col1: "Boiler temp warning"; col2: 63 ('pos in string where temp figures
start
2 col1:"Increase rate" , col 2: 34 ('pos in string where rate starts
etc

What I need to achieve is a single line presentation of each complete alarm
message splitted up in different columns which will make it easier to analyze
from a statistical point of view.

As an example present the first alarm above on one line with different
parameters etc in different columns.
col 1: Boiler temp warning (main alarm message)
col 2: Temperature at alarm
col 3: 240 deg C ('parameter 1)
col 4: Temp increase rate
col 5: 12.7 deg/min ('parameter 2)
col 6: Valve closed
col 7: 1234 ('parameter 3)

I have solved how to arrange the alarms on one single line. The problem I
have is to have the query to analyze each row in Table 1 and present message
type and how to extract the parameter value in the text string. Table 2 has
to hold start position of parameter valute (pos in string normally) and end
of parameter value (normally some string delimiter).

I hope this explains and look forward to receive your comments.

Regards
/Gunnar
All alarms do have timestamps. These have been omitted here.
"John Nurick" skrev:

Hi Gunnar,

Without knowing anything about the format of these strings or the
structure of your tables it's not possible to give a firm answer except
that languages such as Perl (or Python or Ruby) offer much better
facilities than Access for parsing complicated log files.

Would you like to give some more information about the data, including
some sample records?


On Fri, 14 Apr 2006 15:32:01 -0700, Gunnar
<Gunnar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have a log file (Table1) from an alarm system which produces different
alarm outputs consisting of textstrings with embedded parameters, one record
per alarm. I need to identify each record type (there about 200 of these) by
identifying some significant text element and then, when the type is
recognized, dig out the interesting parameters within the message by use of
different string commands. The alarm record types are in separate table
(Table2) with columns for "significant string", character for identifier
start/stop etc.
Please help me to generate a query which for each record in Table1 generates
additional columns identifying type of alarm string and start/stop position
for identifier etc by comparison with all record types in Table 2.
All help is appreciated.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.




--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.


.

Relevant Pages

  • Re: Help to identify different strings
    ... Dim strLine As String ... 'XXX is the name of the field in Table2 that contains ... I agree - it is easy to parse each individual line in Table1 by applying ... individual alarm type, then check whether this string is available in the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help to identify different strings
    ... I advise against importing the log file into a "Table1" with one record ... Dim strLine As String ... I agree - it is easy to parse each individual line in Table1 by applying ... individual alarm type, then check whether this string is available in the ...
    (microsoft.public.access.modulesdaovba)
  • Re: NULL / NOT NULL Query
    ... Sammlung von Strings in Table1 eine Untergruppe ... Table2 abgelegt sind - nach folgendem Muster: ... INSERT INTO Table1 (string) values ...
    (microsoft.public.de.sqlserver)
  • NULL / NOT NULL Query
    ... Sammlung von Strings in Table1 eine Untergruppe ... Table2 abgelegt sind - nach folgendem Muster: ... INSERT INTO Table1 (string) values ...
    (microsoft.public.de.sqlserver)
  • Re: WAVs and API difficulty
    ... change the Alarm function as follows (I also included two parameters ... for your sound file names / The second one as optional): ... Function Alarm(Cell, Condition As String, sound_true_file As String, ... > On Error GoTo ErrHandler ...
    (microsoft.public.excel)