import into Access datavase from a text file using VB 2010

[elie.karkafy]

i have a txt file that is exported from an attendance software as follow :

Date        Time         s1    s2      EmpiD              IN\OUT

07-24-13    12:23:52     001   07      0000020009         0300000000
07-24-13    12:24:02     001   07      0000020010         0300000000 

between the columns of this txt file different spaces for example between date and time 1 space , between time and s1 4 spaces .
i want to import this txt file into my access database using oledb connection
my database contain a table named import (Date, Time, S1, s2, Empid, in\out)

any help please thank you

Elie

[Begginnerdev]

You will want to open a stream reader to the file and pull all lines in from the file. You will then have to remove the spaces from the lines to extract the infromation you want. This can be done like so:

Private Function FillStringList(ByVal sFileName As String) As List(Of List(Of String))
    Dim sr As New StreamReader(sFileName)
    Dim lstAllStrings As New List(Of String)
    Dim lstFinalStrings As New List(Of List(Of String))
    Try
        Do While sr.Peek <> -1
            lstAllStrings.Add(sr.ReadLine())
        Loop

        'Split on spaces
        'Should look something like
        '(0) = 07-24-13
        '(1) = 12:23:52
        '(2) = 001
        '(3) = 07

        For i = 2 To lstAllStrings.Count - 1 'Skip the title row and the empty row
            lstFinalStrings.Add(lstAllStrings(i).Split(" ").ToList)
        Next

        Return lstFinalStrings
    Catch ex As Exception
        MsgBox(ex.ToString)
        Return Nothing
    Finally
        sr.Close()
        sr = Nothing
        lstAllStrings = Nothing
        lstFinalStrings = Nothing
    End Try
End Function


Private Sub InsertToDatabase(ByVal lstStrings As List(Of List(Of String)))
    Dim da As New OleDbDataAdapter(New OleDbCommand("SELECT * FROM Import WHERE 1=2", _
                                   New OleDbConnection("myConnectionSTRINGHERE"))) ' Gets the table structure
    Dim ds As New DataSet

    Try
        da.Fill(ds, "Import")

        If Not IsNothing(ds.Tables("Import")) Then
            For Each item As List(Of String) In lstStrings
                Dim dr As DataRow = ds.Tables("import").Rows.Add()

                dr("Date") = item(0) 'Need fault protection
                dr("Time") = item(1) 'This code sample assumes data will be consistent
                dr("s1") = item(2) 'You will need to put your own handling in
                dr("s2") = item(3) 'This is not guarenteed
                dr("EmpiD") = item(4)
                dr("IN\OUT") = item(5)
            Next

            da.InsertCommand = New OleDbCommandBuilder(da).GetInsertCommand
            da.Update(ds.Tables("Import")) 'Update will issue the insert
        Else
            Exit Sub
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    Finally
        da = Nothing
        ds = Nothing
    End Try
End Sub

This code assumes the above data is the 100% consitent standard that will be passed in. If not, I hope that this will help you on your way to solving your problem.

[elie.karkafy]

the txt file did not contains any title even blank lines , i putted the titles just for explanation. just spaces between columns

txt structure

    07-24-13    12:23:52     001   07      0000020009         0300000000
    07-24-13    12:24:02     001   07      0000020010         0300000000 

can u explain more about the select command and the condition\

Thnak you

[Begginnerdev]

When you issue this select command:

"SELECT * FROM Table WHERE 1=2"

You are just retrieving the table structure simply because 1 will never equal 2.

This is a nifty little trick to get your table structure without having to recreate it in code.

Just makes life a little easier. :)

As for the column headers, just set the start of i to 0 and your problem should be solved. :)

For i = 0 To lstAllStrings.Count - 1 'Skip the title row and the empty row
  lstFinalStrings.Add(lstAllStrings(i).Split(" ").ToList)
Next

Just a note, if the spaces are tabs you can change the split from:

.Split(" ")

To

.Split(vbTab)

[oussama_1]

Project - add reference - com - Microsoft access 12.0 object library

Code :

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & "YOURPATH\access.accdb" & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")

        Dim date1 As New TextBox
        Dim time1 As New TextBox
        Dim s1 As New TextBox
        Dim s2 As New TextBox
        Dim Empid As New TextBox
        Dim InOut As New TextBox
        Dim textfile As New RichTextBox

        textfile.LoadFile("YOUR TEXT FILE PATH.txt", RichTextBoxStreamType.PlainText)

        For Each line In textfile.Lines
            line = line.Replace(" ", "\").Replace("\\\\", "\").Replace("\\\", "\").Replace("\\", "\")
            ' now you have a (\) between each item instead of spaces
            date1.Text = line.Substring(0, line.IndexOf("\"))
            line = line.Replace(date1.Text & "\", "")
            time1.Text = line.Substring(0, line.IndexOf("\"))
            line = line.Replace(time1.Text & "\", "")
            s1.Text = line.Substring(0, line.IndexOf("\"))
            line = line.Replace(s1.Text & "\", "")
            s2.Text = line.Substring(0, line.IndexOf("\"))
            line = line.Replace(s2.Text & "\", "")
            Empid.Text = line.Substring(0, line.IndexOf("\"))
            line = line.Replace(Empid.Text & "\", "")
            InOut.Text = line.Substring(0, line.Length).Replace("\", "")
            line = line.Replace(InOut.Text, "")

            Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO import ([Date], [Time], [S1], [s2], [Empid], [in\out]) VALUES ('" & date1.Text & "','" & time1.Text & "','" & s1.Text & "','" & s2.Text & "','" & Empid.Text & "','" & InOut.Text & "')", Connection)
            Try
                Connection.Open()
                insertCommands.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show(ex.Message & " - " & ex.Source)
            Finally
                Connection.Close()

            End Try

        Next

        MsgBox("Done")

    End Sub
End Class

Proof :

elie iza badak shi tene 2ele

[elie.karkafy]

thx you oussamma i appreciate , akid man ra7 jareb hayda el code for now , glad fi 7adan bye7ke 3arabe bi hal web :P

[elie.karkafy]

07-24-13 12:23:52  001   07      0000020009         0300000000 ( spaces)           
07-24-13 12:24:02  001   07      0000020010         0300000000 ( spaces)          
07-24-13 12:27:00  001   07      0000020005         0300000000          
07-25-13 05:51:22  001   07      0000020009         0100000000          
07-25-13 05:57:50  001   07      0000020001         0100000000          
07-25-13 06:00:02  001   07      0000020015         0100000000          
07-25-13 06:03:16  001   07      0000020003         0100000000          
07-25-13 06:03:24  001   07      0000020014         0100000000          
07-25-13 06:07:02  001   07      0000020005         0100000000          
07-25-13 09:17:50  001   07      0000020016         0100000000          
07-25-13 13:01:16  001   07      0000020009         0300000000          
07-25-13 13:02:06  001   07      0000020001         0300000000          
07-25-13 13:04:56  001   07      0000020014         0300000000          
07-25-13 13:05:06  001   07      0000020015         0300000000          
07-25-13 13:05:14  001   07      0000020003         0300000000          
07-26-13 04:52:50  001   07      0000020015         0100000000          
07-26-13 05:05:58  001   07      0000020009         0100000000          
07-26-13 05:06:12  001   07      0000020001         0100000000          
07-26-13 05:09:34  001   07      0000020014         0100000000          
07-26-13 05:10:08  001   07      0000020005         0100000000          
07-26-13 05:10:22  001   07      0000020003         0100000000          
07-26-13 06:58:32  001   07      0000030123         0100000000          
07-26-13 07:46:22  001   07      0000020016         0100000000          
07-26-13 12:01:02  001   07      0000020009         0300000000          
07-26-13 12:01:10  001   07      0000020003         0300000000          
07-26-13 12:02:02  001   07      0000020014         0300000000          
07-26-13 12:05:34  001   07      0000020015         0300000000          

Ooussma 3am ya3mol insert la awal 4 lines than 3am ya3tine hayda el error

String cannot be of zero length. Parameter name: oldValue ( line 29 ) in the code

i think oussama ano in ekher columns in\out bel txt file fi kamen spaces ...

give me ur email beb3atlal el txt file la tchufo

[oussama_1]

meshe l 7al

Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & "YOURPATH\access.accdb" & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")

        Dim date1 As New TextBox
        Dim time1 As New TextBox
        Dim s1 As New TextBox
        Dim s2 As New TextBox
        Dim Empid As New TextBox
        Dim InOut As New TextBox
        Dim textfile As New RichTextBox

        textfile.LoadFile("TEXT FILE PATH .txt", RichTextBoxStreamType.PlainText)

        For Each line In textfile.Lines
            If line = "" Or Nothing Then
            Else
                line = line.Replace(" ", "\").Replace("\\\\", "\").Replace("\\\", "\").Replace("\\", "\")
                ' now you have a (\) between each item instead of spaces
                date1.Text = line.Substring(0, line.IndexOf("\"))
                Dim a As String = Replace(line, date1.Text & "\", "", 1, 1)
                line = a
                time1.Text = line.Substring(0, line.IndexOf("\"))
                Dim b As String = Replace(line, time1.Text & "\", "", 1, 1)
                line = b
                s1.Text = line.Substring(0, line.IndexOf("\"))
                Dim c As String = Replace(line, s1.Text & "\", "", 1, 1)
                line = c
                s2.Text = line.Substring(0, line.IndexOf("\"))
                Dim d As String = Replace(line, s2.Text & "\", "", 1, 1)
                line = d
                Empid.Text = line.Substring(0, line.IndexOf("\"))
                Dim f As String = Replace(line, Empid.Text & "\", "", 1, 1)
                line = f
                InOut.Text = line.Substring(0, line.Length).Replace("\", "")
                Dim g As String = Replace(line, InOut.Text, "", 1, 1)
                line = g.Replace("\", "")


                Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO import ([Date], [Time], [S1], [s2], [Empid], [in\out]) VALUES ('" & date1.Text & "','" & time1.Text & "','" & s1.Text & "','" & s2.Text & "','" & Empid.Text & "','" & InOut.Text & "')", Connection)
                Try
                    Connection.Open()
                    insertCommands.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show(ex.Message & " - " & ex.Source)
                Finally
                    Connection.Close()

                End Try
            End If
        Next

        MsgBox("Done")
    End Sub
End Class

[elie.karkafy]

okey man i will try it w eza fi chi b2elak ,
btw ur form lebanon right men wein ? ma t3arafna 3leik heik mensir na3mol exchange bi hal coding :P thx anw

[elie.karkafy]

one more question oussamma how to add a progress bar for the import job ?

[oussama_1]

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Connection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source='" & "C:\Users\User\Desktop\Database3.accdb" & "';" & "Persist Security Info=False;" & "Jet OLEDB:Database Password=" & ";")
        Dim date1 As New TextBox
        Dim time1 As New TextBox
        Dim s1 As New TextBox
        Dim s2 As New TextBox
        Dim Empid As New TextBox
        Dim InOut As New TextBox
        Dim textfile As New RichTextBox
        Dim progress As New ProgressBar
        Me.Controls.Add(progress)
        'fill these out
        'progress.Location = New Point( , )
        'progress.Size = New Size ( , )


        textfile.LoadFile("C:\Users\User\Desktop\New Text Document.txt", RichTextBoxStreamType.PlainText)

        progress.Maximum = textfile.Lines.Count - 1

        For Each line In textfile.Lines
            If line = "" Or Nothing Then
            Else
                line = line.Replace(" ", "\").Replace("\\\\", "\").Replace("\\\", "\").Replace("\\", "\")
                ' now you have a (\) between each item instead of spaces
                date1.Text = line.Substring(0, line.IndexOf("\"))
                Dim a As String = Replace(line, date1.Text & "\", "", 1, 1)
                line = a
                time1.Text = line.Substring(0, line.IndexOf("\"))
                Dim b As String = Replace(line, time1.Text & "\", "", 1, 1)
                line = b
                s1.Text = line.Substring(0, line.IndexOf("\"))
                Dim c As String = Replace(line, s1.Text & "\", "", 1, 1)
                line = c
                s2.Text = line.Substring(0, line.IndexOf("\"))
                Dim d As String = Replace(line, s2.Text & "\", "", 1, 1)
                line = d
                Empid.Text = line.Substring(0, line.IndexOf("\"))
                Dim f As String = Replace(line, Empid.Text & "\", "", 1, 1)
                line = f
                InOut.Text = line.Substring(0, line.Length).Replace("\", "")
                Dim g As String = Replace(line, InOut.Text, "", 1, 1)
                line = g.Replace("\", "")
                Dim insertCommands As New OleDb.OleDbCommand("INSERT INTO import ([Date], [Time], [S1], [s2], [Empid], [in\out]) VALUES ('" & date1.Text & "','" & time1.Text & "','" & s1.Text & "','" & s2.Text & "','" & Empid.Text & "','" & InOut.Text & "')", Connection)
                Try
                    Connection.Open()
                    insertCommands.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show(ex.Message & " - " & ex.Source)
                Finally
                    Connection.Close()
                End Try
                progress.Value += 1
            End If
        Next
        MsgBox("Done")
    End Sub
End Class