2005/11/09 18:32:11, Event:Access, Door:entry, Card No.:2727, Description:266:55748
Date time event door card no. description
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2005/11/09 18:32:11, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:13, Event:Access, Door:entry, Card No.:2345, Description:266:55732
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:33:58, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/24 17:31:27, Eventownload Success!
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:32:21, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2006/01/24 17:31:27, Eventownload Success!
2005/11/09 18:33:58, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:34:00, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/24 17:31:27, Eventownload Success!
2005/11/09 18:33:20, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:32:36, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:46, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:54, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:33:42, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:34:36, Event:Card No. Error, Door:entry, Description:266:51792
hello everyone... can you please help me with my problem, i have a project time and attendance system using a reader which the reader creates a textlog. want i want to do is to save only the row that have a format above, and those row that mismatch the format discard it.. i already know how to save it in database, i just want to validate the saving..hope you can help me
Dim inFile As Integer
Dim InLine As String
Dim data() As String
Dim fields As Integer
Dim sFile As String

sFile = txtFile.Text
inFile = FreeFile
Open sFile For Input As #inFile

Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=localhost;" & " DATABASE=time_and_attendance;" & "UID=root;PWD=admin; OPTION=3"
While Not EOF(inFile)
Line Input #inFile, InLine
data() = Split(InLine)
fields = UBound(data())
On Error GoTo badfile
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber, Description) " & _
"VALUES ('" & data(0) & "', " & _
"' " & Left(data(1), 8) & "', " & _
"' " & Mid(data(2), 7) & "', " & _
"' " & Card(data(5)) & "', " & _
"' " & data(6) & "')"
Wend
MsgBox "Record successfully saved ", vbInformation + vbOKOnly, "Record Saved"
Unload Me
Me.Show
Close #inFile
Exit Sub
badfile:
MsgBox "Error type of file ", vbCritical + vbOKOnly, "Error"
or if somebody help me on how to load the textfile in listbox, same validation ingnore the data that not match the format above....
========================code to open the textfile in list box, just help to validate the format above=======================
Dim sFile As String
Dim inFile As Integer
Dim sTemp As String


Open sFile For Input As inFile
While Not EOF(inFile)
Line Input #inFile, sTemp
olist.AddItem sTemp
Wend
Close inFile

royaloba,

In order to parse out the data you do not want I suggest validating the input line before splitting it into the data() array.

Assuming that you only want the "Access" events in your database I would change your code to the following:

You should now be able to change the condition to include whatever events you want, i.e. "Card No. Error" or others, by simply adding conditions to the "If ... Then" statement.

Similarly you can screen out certain doors by using the same technique,
i.e. If InStr(InLine, "Door:entry") > 0 Then

Hope this helps

Yomet

sir jomet,

hello sir, i try it already... and it is working...thanks.

royaloba,

As I said in my answer to your PM I am posting your question and my answer here for everybody to profit from the information exchanged.

Without giving you the code I will give you the steps I would take to do this.

1) Every time you read a log line you need to see if that card has already been used during that day
HINT use a SELECT statement with date and card number in the WHERE clause

2) If the card has not been used then you need to add an entry into your table with the card number, date and time as Time In - this is the code you already have.

3) If the card has already been used you need to update the table with the current time as Time Out - this is the code you need to create
HINT use an UPDATE statement

Hope this helps and please tell me how it works out

Yomet

I want to give a thumbs up to Yomet here.... Keep up the good work.

sir jomet,

hello sir, sorry but i cannot do it, without your sample code sir.. would you please provide sir... because i didn't know where to put the select case..thanks sir

Hi again royaloba,

When I said a SELECT statement I did not mean a SELECT CASE statement but rather a SQL SELECT statement.

Here is the gist of what I would do, this might not work correctly in your program but with minor tweaks it should.


Hope this code sheds some light on the solution to your problem.

Happy coding

Yomet

hello sir jomet its me again.. still i can't solve my problem before.. please help. this is regarding with the timein timeout issue that i tell you before i include the program so you could tell where the problem is..please help me..i will also include the mysql database

username=admin
pasword=admin

Hi royaloba,

Sorry to hear that things are not working out. I would like to know what is going wrong. Just saying "It's not working" does not help me a lot since there are a million things that could be not working. For instance, I found a syntax error in your UPDATE statement, you coded "UPDATE INTO" whereas the correct syntax is so if your error is a syntax error that might be your problem.

I made an error, that you corrected nicely, by assuming you were using an Access database, thanks for not getting me into trouble...

I do not have MySQL installed on my computer so I cannot run your program, it gives me an error about the ODBC data source not being defined etc.

If you could give me a better description of what is going wrong it would be very helpful.

Thanks

Yomet

P.S. My nick is spelled with "Y" not "J"...

Code:
---------
'You will need to add the following declaration
Dim rs As Recordset
---------
Code:
---------
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
Data() = Split(InLine)
Fields = UBound(Data())
On Error GoTo badfile

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblattendance
WHERE [Date] = '" & Data(0) & "' AND CardNumber = '" & Data(5) & "'")
If rs.EOF Then 'No data returned, i.e. first time this card is
used
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event,
CardNumber, Description) " & _
"VALUES ('" & Data(0) & "', " & _
"' " & Left(Data(1), 8) & "', " & _
"' " & Mid(Data(2), 7) & "', " & _
"' " & Card(Data(5)) & "', " & _
"' " & Data(6) & "')"
Else 'Data has been returned, i.e. the card has already been
used today
conn.Execute "UPDATE tblattendance SET TimeOut = '" &
Left(Data(1), 8 & "'")
End If
rs.Close
Set rs = Nothing

End If
Wend
---------
sir YOMET,
hello sir that is the sample code that you given to me, before...my problem is the set rs=currentdb.OpenRecordset is not working with mysql database. i just modify your code into
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
'On Error GoTo badfile

Set rs = New ADODB.Recordset
squery = "": squery = ("Select * from tblattendance where Date='" & data(0) & "' AND CardNumber = '" & data(5) & "'")
Call ExecuteCommand
If rs.EOF Then ' NO DATA RETURNED

conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"
Else ' DATA HAS BEEN USED
conn.Execute "UPDATE tblattendance set TimeOut='" & Left(data(1), 8 & "'")
End If
rs.Close
Set rs = Nothing
End If
but still the output is the same... see the sample pic that i attach...