Click here to Skip to main content
15,669,524 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Good Day,

I am writing a new program where i should transfer freshly extracted raw attendance record from a biometrics time recorder to windows based time keeping software

However when i tried importing it to my datatable/datagrid using the code below,

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim fileName As String = "record.txt"
        Dim dirName As String = "C:\Users\Biometric\Desktop\"
        Dim dt As DataTable
        Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
             "Data Source=" & dirName & ";" & _
             "Extended Properties=""Text;""")
            ' Open the connection 
            Using adapter As New OleDbDataAdapter( _
               "SELECT * FROM " & fileName, cn)
                dt = New DataTable("record")

                Dim ds As New DataSet

                Dim joy As New BindingSource
                joy.DataSource = dt
                DataGridView1.DataSource = joy

            End Using
        End Using
    End Sub

it just simple transfer whole row into one column.

1 2006-10-12 14:04:47 1 0 1 0
1 2006-10-12 14:05:08 1 1 1 0
1 2006-10-12 14:06:13 1 0 1 0

1st column User ID = Access ID #
2nd column Date & Time
3rd column Device number
4th column Transaction type
0 - check in
1 - check out
5 – OT in (available only in iClock260)
4 – OT out (available only in iClock260)
3 - break in (available only in U160, F4, iClock260)
2 - break out (available only in U160, F4, iClock260)
5th column 0 – use password
1 – use fingerprint
6th column work code (available only in U160)

I think this is a fix output of a time attendance device where i think columns has a fixed width - fields are aligned with spaces between each field.

What i need to do is to transfer three primary data to my database table

1st Column = Employee ID
2nd Column (Date&Time) = Attendance Record
4th Column = Transaction type if its a Time In or Out.

Does anyone here had a similar experience with this?

First of all, if your file is space delimited, you need to create schema.ini file.
Schema.ini file should be placed in the same directory where record.txt is placed.
Content of Schema.ini:
Format=Delimited( )
Col1=AccessID Long
Col2=AccessDate DateTime
Col3=AccessTime Text Width 8
Col4=DeviceNumber Long
Col5=TransactionType Long
Col6=UsePassword Long
Col7=WorkCode Long

Now, create new windows application (do not forget to add DataGridView). Then replace Form1 class code with:
Public Class Form1

    Public Sub New()

        ' This call is required by the Windows Form Designer.

        ' Add any initialization after the InitializeComponent() call.
        Dim scon As String = String.Empty, scom As String = String.Empty
        Dim con As OleDb.OleDbConnection = Nothing, com As OleDb.OleDbCommand = Nothing, rdr As OleDb.OleDbDataReader = Nothing
        Dim sFileName As String = String.Empty
        Dim dt As Data.DataTable = Nothing, r As DataRow = Nothing

            sFileName = "F:\Download\record.txt"
            scon = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & IO.Path.GetDirectoryName(sFileName) & ";Extended Properties='Text;HDR=Yes;Schema=schema.ini;';"
            scom = "SELECT * FROM " & sFileName
            con = New OleDb.OleDbConnection(scon)
            com = New OleDb.OleDbCommand(scom, con)
            rdr = com.ExecuteReader()
            dt = New Data.DataTable

            Me.DataGridView1.DataSource = dt

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")
        End Try

    End Sub
End Class

As you see, connection string was changed ;)
Share this answer
Richard MacCutchan 3-Mar-13 11:11am    
My 5. I have just been testing this myself on a tab delimited file.
Maciej Los 3-Mar-13 11:14am    
Thank you, Richard ;)
JMAM 3-Mar-13 12:50pm    
I tried this but when i run the app it only shows access id values in my datagrid all suceeding columns are empty
JMAM 3-Mar-13 12:52pm    
Got it, Thanks Marciej. :)
Maciej Los 3-Mar-13 12:59pm    
You're welcome ;)
If my answer was helpful, please, rate it...
You may need to consider manually parsing the data fields rather than using the OleDb classes. But see also[^] for options when reading from text files.
Share this answer
Maciej Los 3-Mar-13 10:47am    
Good tip. My 4, because of OleDb class provides options to read space-delimited text files.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900