how to validate text file, loading into listbox

[royaloba]

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

[Yomet]

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:

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
      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) & "')"
   End If
Wend

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

[royaloba]

sir jomet,

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

[Yomet]

hello sir it's me again.... i finish the validation on saving the textlog... i'm adding a new
field in the Table Its TimeOut here's the senario sir i want to put the first time in fields
TIMEIN, Then last record will be for TIMEOUT..base on their card number! did you get me sir??
i already try it sir, but the problem when i save it, the output is like this...

Date TimeIn Event Door CardNumber TimeOut
2005/11/24 09:32:42, Access, Door:entry, 2727 9:33:30
2005/11/24 09:33:30, Access, Door:entry, 2727
2005/11/24 09:33:42, Access, Door:entry, 2727
2005/11/24 09:34:04, Access, Door:entry, 2727
2005/11/24 09:42:05, Access, Door:entry, 2727
2005/11/24 09:51:13, Access, Door:entry, 2345
2005/11/24 09:51:19, Access, Door:entry, 2345
2005/11/24 09:51:26, Access, Door:entry, 2345
2005/11/24 09:51:37, Access, Door:entry, 2345
2005/11/24 09:51:47, Access, Door:entry, 2345
2005/11/24 09:55:25, Access, Door:entry, 2727
2005/11/24 16:30:50, Access, Door:entry, 2345
this is the output i want sir
Date TimeIn Event Door CardNumber TimeOut
2005/11/24 09:33:42 Access, Door:entry, 2727 9:55:25
2005/11/24 09:51:19 Acesss, Door:entry, 2345 16:30:50
i want to save only the first swipe of the card(first us timein) and the last swipe of the
card(last us TimetOut). i keep on asking if it is possible, cause i'm in stage of getting
familiar with vb not in the stage of a senior or junior programmer... i hope you can help me
sir..
or if you have a other way to do it sir...by the way again thanks to the previous code you gave
me..it's working well sir..;)

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

[Comatose]

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

[royaloba]

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

[Yomet]

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.

'You will need to add the following declaration
Dim rs As Recordset

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

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

Happy coding

Yomet

[royaloba]

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

[Yomet]

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

UPDATE <tablename> 
SET <field> = <value>[, <field> = <value>[,...]] 
[WHERE <where clause>]

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"... ;)

[royaloba]

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...

[royaloba]

sir yomet
hello sir i have some good news for you, i was able to set the TIMEOUT, but there is few error i want to fix..
SAMPLE OUTPUT mysql

--------------------------------------------------------------------------------
Date              TimeIn        Event        CardNumber      TimeOut
--------------------------------------------------------------------------------
2002-05-02 |  21:30:12  |  Access   |        451        |      21:32:09    |
2002-05-02 |  21:30:12  |  Access   |        451        |      21:32:09    |
2002-05-02 |  21:30:22  |  Access   |        451        |      21:32:09    |
2002-05-02 |  21:30:48  |  Access   |        546        |      21:32:09    |
2002-05-02 |  21:30:55  |  Access   |        546        |      21:32:09    |
2002-05-02 |  21:31:19  |  Access   |        546        |      21:32:09    |
2002-05-02 |  21:31:59  |  Access   |        234        |      21:32:09    |
2002-05-02 |  21:32:06  |  Access   |        234        |      21:32:09    |
2002-05-02 |  21:32:08  |  Access   |        234        |      21:32:09    |
2002-05-02 |  21:32:09  |  Access   |        234        |      21:32:09    |
---------------------------------------------------------------------------------

i just revise some of your sample code sir... as you see in the output i

the code read only the last TimeIn... the right output shoul be like this

--------------------------------------------------------------------------------
Date              TimeIn        Event        CardNumber      TimeOut
--------------------------------------------------------------------------------
2002-05-02 |  21:30:12  |  Access   |        451        |      21:32:22    |
2002-05-02 |  21:30:48  |  Access   |        546        |      21:31:19    |
2002-05-02 |  21:31:59  |  Access   |        234        |      21:32:09    |
--------------------------------------------------------------------------------

read only the first TimeIn and the LAST record and put this as TimeOut Base on their CARDNUMBER and the DATE...i need your suggestions sir...thanks..and if you can provide me a bit of code...thanks

THE CODE I USE SIR

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
        If InStr(InLine, "Event:Access") > 0 Then
        data() = Split(InLine)
        fields = UBound(data())

        conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
                   "VALUES ('" & data(0) & "', " & _
                           "'" & Left(data(1), 8) & "', " & _
                           "'" & Mid(data(2), 7) & "', " & _
                           "'" & Card(data(5)) & "')"

        Set rs = New ADODB.Recordset
        squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
        Call ExecuteCommand
        If rs.EOF Then ' DATA HAS BEEN USED
            conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'"
      End If
      rs.Close
      Set rs = Nothing
    End If

[Yomet]

royaloba,

I can see where you code is going wrong and there are two quite simple errors.
1) What you do is adding a record every time you read a line wihtout first verifying if that card has already been used today.
2) The UPDATE statement later on will update all the records - not only the ones for that specific card and day. This error comes from my own code so I take full responsibility for it, I forgot to add the WHERE clause in the code I gave you.

Using your own code I will show you what I am talking about and how to correct it.
BTW, please use the code tags like this
[ code ]
Here goes all your code.
Please remove the spaces between the square brackets "[" and "]" and the "code" and "/code" parts
[ /code ]

OK, here goes

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
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())

'**********************
'At this point you have fresh data in your array and what you 
'WANT to do is verify if the current card, in Card(data(5)), has 
'already been used today. 

'What you need here is a condition to see if the card has already 
'been used. Actually the IF statement you use later is almost there, i.e.

Set rs = New ADODB.Recordset
squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
If NOT rs.EOF Then  'Data has NOT been used so insert it.
'**********************

conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"

'**********************
'Now you have inserted the data into your database since it was 
'not there to begin with.

'However, if the data was already in the table you need to update 
'the TimeOut with the current time. So we scrap the If statement 
'(since we used it above) and use the else clause.

Else
'**********************

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

'conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'"
'**********************
'The new UPDATE statement
conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'" & _
WHERE Date='" & 'data(0) & "' and CardNumber='" & data(5) & "'"
'**********************
End If
rs.Close
Set rs = Nothing
End If

Seeing my last answer I would like to say sorry for misleading you, I was tired and didn't read the code correctly, there was no syntax error, just my eyes seeing double... :o

As far as I can see this should do the trick. Try it out and tell me how it works.

Happy coding

Yomet

[royaloba]

hello sir Yomet
first thanks for helping me with this project
the problem is when i put the "if not rs.eof then" the return record in the data base in empty... and when i replace it with "if rs.eof" the ouput for all the timeout is the same..sir i attach a sample program, i convert it to access so you could determine where is the problem occurs..thanks sir..

[Yomet]

royaloba,

Thanks for taking the time to convert your database into Access, it helped.

Now for what I found:
1) There seems to be some problem with the "ExecuteCommand" sub since it never gives back a recordset with records in it. However when I replaced that line with rs.Open squery, conn it gave the expected results.
2) I had to revert to your original code for inserting and updating records by using conn.Execute instead of db.Execute because when using the mix between DAO and ADODB the record was not saved by DAO before the ADODB went to find it. Weird I know but by using the following code everything went well.

Open sFile For Input As #inFile
'    Set db = DBEngine.OpenDatabase(App.Path & "\db1.mdb")
    While Not EOF(inFile)
        Line Input #inFile, InLine
        If InStr(InLine, "Event:Access") > 0 Then
        data() = Split(InLine)
        fields = UBound(data())
        
        Set rs = New ADODB.Recordset
        squery = "": squery = "Select * from tblattendance where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
'        Call ExecuteCommand
        rs.Open squery, conn
        If rs.EOF Then
             
            conn.Execute "INSERT INTO tblattendance ([Date], [TimeIn], CardNumber) " & _
                       "VALUES (#" & data(0) & "#, " & _
                              "'" & Left(data(1), 8) & "', " & _
                               "'" & Card(data(5)) & "')"
                               
            'Set rs = New ADODB.Recordset
            'squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
            'Call ExecuteCommand
            'If rs.EOF Then ' DATA HAS BEEN USED
        Else
            conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "' where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
        End If
        rs.Close
        Set rs = Nothing
        End If
    Wend
    MsgBox "Record successfully saved       ", vbInformation + vbOKOnly, "Record Saved"
    Unload Me
    Me.Show
    Close #inFile
    Exit Sub

You were correct in using If rs.EOF instead of If Not rs.EOF , typical brain-fart on my behalf.

Hope this code works and keep up the good work.

Yomet

[royaloba]

Sir Yomet,
thank you very much it's working very well i just replace the # because it's not valid in mysql... i can now move on to my project...
again THANKS;)

[Yomet]

royaloba,

I am happy that things worked out well.

Good luck with the rest of the project

Happy coding

Yomet