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

Excellent Job.

Hi, Amy. Those aren't very good field names. Are you sure that's what
they are? And not captions? Can you open the table into which your
data will be imported, and hit Design View. Then tell Hammer the field
names as you see them there, and their data types. Better yet, a
screen capture of it uploaded to a server somewhere would probably be
easier for you depending on the number of fields.

Just trying to help hammer. :)