Click here to Skip to main content
14,697,418 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 
            cn.Open()
            Using adapter As New OleDbDataAdapter( _
               "SELECT * FROM " & fileName, cn)
                dt = New DataTable("record")



                adapter.Fill(dt)
                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

Legend:
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?
Posted

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:
[record.txt]
Format=Delimited( )
ColNameHeader=False
MaxScanRows=0
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.
        InitializeComponent()

        ' 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
        Try

            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)
            con.Open()
            com = New OleDb.OleDbCommand(scom, con)
            rdr = com.ExecuteReader()
            dt = New Data.DataTable
            dt.Load(rdr)

            Me.DataGridView1.DataSource = dt

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error...")
        Finally
            rdr.Close()
            com.Dispose()
            con.Close()
            con.Dispose()
        End Try

    End Sub
End Class


As you see, connection string was changed ;)
   
v2
Comments
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...
JMAM 3-Mar-13 13:06pm
   
Done, Can you tell me where can i get good reference exploring schema.ini. my concern is what if columns change? for instance their is another model of biometrics where its first cloumn would be transaction type? how do i know if what should i put in scheme.
Maciej Los 3-Mar-13 13:13pm
   
Oooopppsss... Sory, i forgot to attach reference to Text File Reader...
Have a look here: http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353%28v=vs.85%29.aspx
You may need to consider manually parsing the data fields rather than using the OleDb classes. But see also http://www.connectionstrings.com/textfile[^] for options when reading from text files.
   
Comments
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