Using database to enterpret logfile

[EarthWorm]

Hi!

I am totally new to Access, but easy to learn.
I want to interpret a logfile with codes like this:
1: 51910 00D00 or just 2: 71910B.
The syntax for this is 1: 5(call from)1910(car nr) 00D00(Start of shift) and 2: 7(selective call from)1910(car nr)B(unknown) There are allso garbagecode like this: B E 51 wich needs to be deleted.

How do I use a table to substitute these codes and be able to update the inputfile live...

This is a big one I know...

Well...
Thanks for any hel at all...
Regards!

 

[neileg]

If you hold these codes as a text field you can then use the string chopper functions (left, right, mid) to carve these into separate fields. This will depend on a fixed format for the data. Beware, particularly, using Right on a variable length field.

If your data is too variable, then automated import and parsing may be impossible. Sometimes manual data cleansing, perhaps in Excel, is needed before getting the data into Access.

 

[EarthWorm]

Hmm...

As i said, not too familiar with Access.
Could you be moore specific?

 

[neileg]

OK, since you have a nice cat…

Read your logfile into an Access table. Use the ‘Get external data’ wizard from the main File menu. This should import your data, line by line into a table with just one field with a text format. If this does not happen using the wizard, then create a table like this and re-run the wizard importing into this table. Let’s say this table is called LogFile and the single field called LogEntry

Then create a query based on the LogFile table. In the Field line of the design window, you need to create the fields that contain the sections of your log entry that represent different pieces of data. Do this by putting the Field name first and then the expression that extracts this data, eg
Field1: Left([LogEntry],4)
Field2: Mid([LogEntry],5,4)
Field3: Mid([LogEntry],9,42)
This last expression has 42 as a value on the assumption that there are 50 characters in the definition of the LogEntry field.

This will separate your data into three fields. The problem is that although the first bit seems constant, ie the call type, and the car, the final bit seems variable. You are going to have to figure out how to deal with that.

So, this query will parse your data into three fields. You can turn this query into a maketable query which will create a new table with these fields in or an append query that will add this data to an existing table. Then you can import your fresh logfile each time, and run this query to add the new data to your existing data.

You say you also have garbage. Is this a new record, or is it stuck on the end of the valid records? You are going to have to find a way of identifying this garbage and deleting it. You might use a delete query on the LogFile table to cleanse this before you run the query to parse the data.

 

[EarthWorm]

Ok; I'll try somthing like this, but i think i need som IF THEN programming an so. Then there might allso be an extended piece of code after the 51910 wich is like 00D00: like this "51920 00D00". The garbage is quite easily identifyed by that if there is less than 4 chars it is no good.

I way i am thinking of is to do multiple passes...
If i pass the string through the list of codes and the list is sorted in falling order then i would be able to get the first one out eg. if i have 71910B and ir run it trough the database i could get the 7 out and than run it trough again whitout the 7 and get 1910 and than run it agan with no match and get B just because it was connected to 71910. And say i have a full code like this 00D00 then it would match compleatly in the database and return the value.

What do you think??

 

[neileg]

I think you are saying that the garbage is at the end of the record. I would prefer to deal with all the variations at one go. You can run multiple passes if you like, but that usually doesn't make it easier. If you get the logic right, one pass should do it.

Post some sample log entries and show what is good and what is garbage. There's not enough info for me to understand.

 

[EarthWorm]

I can attach the files with the message...

This is The Database

 

[EarthWorm]

And this is the log... With some comments.

 

[neileg]

Ok, I’m still a bit confused, but here goes.

The logfile looks manageable. You can use the approach I suggested to extract the date. The duration I would convert to seconds by extracting the minute value, multiplying by 60 and adding the seconds value. The tones are more difficult because although the valid data starts with a string of 4 chars, this may be followed by other chars in a non-fixed format. This is further complicated by the example you identify as valid except for a trailing B. I assume that if the tone values are garbage, the whole record is of no value. I think I would extract the tone values as one string. I would test the tone values data to see if all four chars at the start of the string are not null

Thus, my query might look like this

Date: Left([LogEntry],8)
Time: Mid([LogEntry],10,8)
Duration: Mid([LogEntry],30,2)*60+Mid([LogEntry],36,2)
Tones: Mid([LogEntry],50,25)
ToneCh1: Mid([LogEntry],50,1)
ToneCh2: Mid([LogEntry],51,1)
ToneCh3: Mid([LogEntry],52,1)
ToneCh4: Mid([LogEntry],53,1)
In the criteria row for ToneCh1 to 4 enter Is Not Null

I hope I counted the character positions correctly.

This should give you just a set of valid data (except for that one with the trailing B). You need to think about that one.

When you are reading in the raw data, you are going to need to chop off the header and trailer bits. I'm afraid I've never needed to do this in Access, so I can't help there. I'm pretty sure it can be done, though.

I don’t know what you wanted to do with the file you call the database.

 

[EarthWorm]

Well, the file I in that vrsion call "Database" I've renamed to CodeTable, it contains the translations for the numbers. The main purpouse for this proj. is that i want to interpret the rawkodes with this table.