Read Text File Specific Columns

Question

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,

VB

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 2-Mar-13 4:39am

JMAM

Add a Solution

2 solutions

Solution 1

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.

Posted 2-Mar-13 5:05am

Richard MacCutchan

Comments

Maciej Los 3-Mar-13 10:47am

Good tip. My 4, because of OleDb class provides options to read space-delimited text files.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-03-03T04:44:00

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:

VB

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