I have a log file that i need to be read and the information to be
placed in an access database, repeat the process every time new
information is added to the log.
Sample of the format of the log:
3/28/2005 11:51:15 AM - Order On -10- MS6/DEN G/ROD/
3/28/2005 11:51:51 AM - Order On -10- MS6/DEN G/ROD/
3/28/2005 12:02:12 PM Lights Off -26- cleared by Bro, Gay
3/28/2005 12:02:40 PM Lights Off -10- cleared by Bro, Gay
Thank you |
Request for Question Clarification by
hammer-ga
on
28 Mar 2005 09:30 PST
Which version of Access?
What is adding the information to the log?
How often should the program check for new information?
Should the log be cleared out after the information is read into the database?
What fields does the database have?
- Hammer
|
Clarification of Question by
amy123456-ga
on
28 Mar 2005 09:45 PST
1-Version 2000 0r 2003.
2- A c# program
3-every hour
4-The log could be cleared only if the information was being kept accurately.
5-date/time/pm or am/orders/state/room/floor/ptfirstname/ptlastname/
datecl/timecl/pmcl or amcl/orderscl/statecl/roomcl/floorcl/cleared
by/userlastname/userfirstname
Thank you
|
Request for Question Clarification by
hammer-ga
on
28 Mar 2005 11:16 PST
Is the data in the log file delimited by a character that does not
appear in the data itself? If not, can it be?
I'm asking because I'm looking for a way to reliably parse the line of
log data into the individual Access fields.
- Hammer
|
Clarification of Question by
amy123456-ga
on
28 Mar 2005 12:39 PST
The folowing is a copy of the log files as it exists.
3/28/2005 11:51:15 AM - Order On -10- MS6/DEN G/ROD/
3/28/2005 11:51:51 AM - Order On -10- MS6/DEN G/ROD/
3/28/2005 12:02:12 PM Lights Off -26- cleared by Bro, Gay
3/28/2005 12:02:40 PM Lights Off -10- cleared by Bro, Gay
I could add a delimeter. for example.
3/28/2005 11:51:51 AM / Order / On /10 MS6/DEN G/ROD/
3/28/2005 12:02:12 PM / Lights /Off /26/ cleared by /Bro/ Gay
Thank you
|
Request for Question Clarification by
hammer-ga
on
28 Mar 2005 13:42 PST
I can write you code that does this in Access.
Could you please detail for me which pieces of information in your
sample log file go into which fields in the Access database?
For this to work reliably, each line in the log file needs to have the
same number of values, separated by a delimeter, even if some of the
values are blank.
- Hammer
|
Clarification of Question by
amy123456-ga
on
28 Mar 2005 17:46 PST
date time / pm or am/ orders/state /room/
3/28/2005 11:51:51/ AM or PM/ Order / On / 1-28/
floor /ptfirstname/ptlastname/
MS6-8 /DEN G /ROD
datecl timecl /pmcl or amcl/orderscl/statecl /roomcl/floorcl/
3/28/2005 12:02:12/AM or PM / Lights / OFF / 1-28/ MS6-8 /
cleared by /userlastname/userfirstname
cleared by/Bro /Gay
|
Clarification of Question by
amy123456-ga
on
29 Mar 2005 03:42 PST
I known the fields look ackward- but that's so we can relate better
with the logfiles. The information that we capture from this log file
is - orders for a patient is enter into a hospital system. A log of
that transaction is logged. (The following is logged)
date_time / pm_am / orders/state /room/
3/28/2005 11:51:51/ AM or PM/ Order / On / 1-28/
floor /ptfirstname/ptlastname/
MS6-8 /DEN G /ROD
When a nurse verifies the order, it logs that info also. (The following is logged)
datecl_timecl /pmcl_amcl /orderscl/statecl /roomcl/floorcl/
3/28/2005 12:02:12/AM or PM / Lights / OFF / 1-28/ MS6-8 /
cleared_by /userlastname/userfirstname
cleared by/Bro /Gay
The data types are all text except date/time
Thank you
|
Request for Question Clarification by
hammer-ga
on
29 Mar 2005 05:06 PST
Amy123456,
It looks like your Access records have fields for an original log
transaction, then a second set of fields for a log line that appears
later when the transaction is "cleared". I don't see any way in your
data to reliably tell a "clear" from a regular other than the words
"cleared by" might appear somewhere in it. I also don't see a way to
locate the original transaction that is being cleared to complete it,
rather than creating a new record.
What if we do the following:
1. Add a TrxType field to your database which would have values of
either ORDER or CLEAR.
2. Remove the "cleared" fields from your database and have a single
set of fields - trxDateTime, trxAmPm, trxTag, trxState, trxRoom,
trxFloor, trxFirstName, trxLastName
Then, we can bring in your log file. It would be best if you could
include a tag in your log file that clearly idenitified the type of
trx, such as "OR" and "CL". Otherwise, I'll have to look for the
phrase "cleared by" in the line and assume that, if I find it, it's a
CLEAR.
It would also be good if you could assign a unique id to your order
that is also attached to the clear, so you can match them up, but I'm
not sure what you can control for whatever is writing out the log.
Does the above sound good to you? Let me know.
- Hammer
|
Clarification of Question by
amy123456-ga
on
29 Mar 2005 08:27 PST
That sounds good. But on the iniquick id (OR Cl. You can tell weather
it is an order or a clear. Orders will have a state of ON always.
Cleared will have a state of OFF always.
Example. When an order comes in, it logs that action. for me to know
when the CLEARED in initiated i will look for the first OFF state for
the same room number.
I could have several orders , but only one clear action for that room #.
|
Request for Question Clarification by
hammer-ga
on
29 Mar 2005 08:41 PST
Amy123456,
Good. That last clarifiaction gave me what I needed to know. I'll
write you up some code and a sample mdb to play with.
- Hammer
|
Clarification of Question by
amy123456-ga
on
29 Mar 2005 10:18 PST
Thank you
|
Amy123456,
Assumptions:
1. You can format your log file as needed.
2. The program that writes the log file will create the file if it
does not already exist.
3. Your log file always has the same name.
If any of the above assumptions are incorrect, please let me know.
Please download the zip file from
http://www.hammerdata.com/Google/amy.zip . It contains an mdb and a
text file (amy.txt) which is an example of how your log file needs to
be formatted. The log file is tab-delimited, meaning there is a tab
between each piece of information. As the log fiel now needs to be
machine (rather than human) readable, I've gotten rid of all the
formatting. The log file should contain only the info you want to
import. The code currently expects both files to be in the same
location, but you can easily adjust that.
The sample log file looks like this (assuming that lines don't wrap):
3/28/2005 11:51:15 AM Order On 10 MS6 DEN G ROD
3/28/2005 11:51:51 AM Order On 10 MS6 DEN G ROD
3/28/2005 12:02:12 PM Lights Off 26 Bro Gay
3/28/2005 12:02:40 PM Lights Off 10 Bro Gay
Note that there is a tab delimiter, even if a particular column is empty.
The mdb contains one table (tblTRX) and one Form (frmGetLogs).
The table has the 8 fields we discussed, which matches the number of
columns in the log file.
The Form has a button you click to Start/Stop collection of the logs.
The Form also has two fields to show some status information. A Timer
event is used to collect the logs once per hour. The commented code is
pasted below. I'm not sure what your level of expertise is, so please
ask for clarification if you don't understand what I'm doing, or if
you need instructions on how to adjust this for your own use.
The code copies the current log file to a temporary file, then imports
the temporary file using TransferText. Currently, the temporary files
are retained until you clear them out so you have them if you need to
confirm the imported information. If you want to, you can add a line
of code to delete the temporary file after importing. If you test
this, you will need to recreate amy.txt after each import in order for
the next one to work, since the code moves the original file to a
different name and expects it to be recreated by the program which
writes the log.
Note that TransferText uses a saved import specification (Log Import
Spec) to do the import. If you need to recreate this in a different
mdb, please ask for clarification and I'll give you step-by-step
instructions.
Here is the code for the Form. Note that lines may need to be unwrapped.
Option Compare Database
Option Explicit
Private TimerCount As Integer
Private GetState As Integer
Private Sub Command0_Click()
' Toggle log retrieval on and off
If GetState = 0 Then
' Get the current log entries
GetLog
' Set the timer to 1 minute
Me.TimerInterval = 60000
Command0.Caption = "Stop Getting Logs"
GetState = 1
Else
Me.TimerInterval = 0
Command0.Caption = "Start Getting Logs"
GetState = 0
End If
End Sub
Private Sub GetLog()
Dim fs As Object
Dim LogFile As String
Dim ImportLog As String
' Change this to the location and
' filename of your log file.
LogFile = "amy.txt"
' Reset timer
TimerCount = 1
' Create a file system object so we
' can do file operations.
Set fs = CreateObject("Scripting.FileSystemObject")
' Make sure the log file is there.
If fs.FileExists(LogFile) Then
' Ask the system for a temp file name
ImportLog = fs.GetTempName()
' Move the log file to the temp file
fs.MoveFile LogFile, ImportLog
'Import the temp file
DoCmd.TransferText acImportDelim, "Log Import Spec", "tblTRX",
ImportLog, False
' Update the status info on the Form
LastImport = Now()
NextImport = DateAdd("h", 1, Now())
Else
MsgBox "The log file " & LogFile & " cannot be found."
End If
End Sub
Private Sub Form_Load()
' Initialize variables
TimerCount = 1
GetState = 0
End Sub
Private Sub Form_Timer()
Dim LogInterval As Integer
' Set number of minutes between
' log retrievals
LogInterval = 60
' Increase the Timer count
TimerCount = TimerCount + 1
' If it's been 60 minutes, get
' the current log entries
If TimerCount >= LogInterval Then
GetLog
End If
End Sub
Please look over the sample and let me know how I can help you to
integrate this technique with your existing system.
- Hammer |
Request for Answer Clarification by
amy123456-ga
on
30 Mar 2005 04:49 PST
I am geeting the following error in the database (rad73372_ImportError)
when trying to upload the file to the database.
Error | Field | Row
Type Conversion Failure |txtDateTime| 1
Thank you
|
Clarification of Answer by
hammer-ga
on
30 Mar 2005 05:04 PST
Amy123456,
That error means that Access cannot convert the value provided into
the specified type. In your case, probably a date/time value.
1. Do any of the rows import, or does the whole thing fail?
2. Can I see the text file you are importing?
3. Can I see the mdb into which you are importing?
- Hammer
|
Clarification of Answer by
hammer-ga
on
30 Mar 2005 06:18 PST
Amy123456,
I cannot remove any information, however I have written to the Google
Answers editors to see if they can do so.
The problem is that your log file has not been reformatted. The
routine wants a tab delimited file to import. Your log file is still
in the original format, so it does not import. You indicated that you
could change your log file output to a delimited format, so I created
the routines with that in mind. Without the tabs, Access has no idea
where one piece of data ends and the next one begins.
To see what I mean, compare the log file sample in my answer with your
current log file.
This process is written so that you can change your current process to
one where you start automatically importing your logs every hour. If
you also need to pull in your logs to date as a starting point, a
one-time process will need to be run on the existing log file to parse
out all the pieces and get them into the right places. There's no
point in doing this, however, until your log files are being written
out in the new format, which allows import. Otherwise, we'll just keep
getting old format logs that have to be munged.
- Hammer
|
Clarification of Answer by
hammer-ga
on
30 Mar 2005 08:06 PST
Amy123456,
Is it okay if the timstamp appears in the trxDateTime field as
11/17/2004 3:27:15 PM
... and the PM or AM is not also split out into a separate field?
- Hammer
|
Clarification of Answer by
hammer-ga
on
30 Mar 2005 10:22 PST
Amy1234546,
Never mind the prior RFC. It's irrelevant. Access will not recognize
your timestamp in its current format, so we have no choice but to
bring it into a text field, instead of a date/time field. Based on
what you are doing, I don't think will cause you any problems.
I've uploaded another zip file for you.
http://www.hammerdata.com/Google/amy2.zip
This one is almost identical to the first. I've made the following chages:
1. Changed the trxDateTime field to type Text.
2. Removed the trxAmPm field
3. Updated the saved import spec to use | instead of Tab.
Give this one a try.
- Hammer
|
Request for Answer Clarification by
amy123456-ga
on
31 Mar 2005 13:18 PST
Hi Hammer. Excellent, it worked
PS- I have a small question-- can i change the file name from amy to
something else (LogFile = "c:\amy.txt")
Thank you very much
|
Clarification of Answer by
hammer-ga
on
31 Mar 2005 13:33 PST
Absolutely. That's why I put the LogFile in a variable. You can change
it to whatever (and wherever) your log file actually is.
- Hammer
|
