how to read excel file in access atble using oledbconnection

[djmd60]

Hello everyone, 
I am write a program, which have to read an excel file and save it in an access table. The excel file will be upload. The data will be read and saved in an access table named smsbestand. Can anyone one help here is the code:

  extension = Mid(txtFileUpload.Text, pos, (Len(txtFileUpload.Text) + 1 - pos))
            If extension = "xls" Or extension = "xlsx" Then
                Dim conn1 As System.Data.OleDb.OleDbConnection
                conn1 = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source=                 '" & txtFileUpload.Text & " '; " & "Extended Properties=Excel 8.0;")
                conn1.Open() 'connection to excel file
                filename = System.IO.Path.GetFileNameWithoutExtension(txtFileUpload.Text)
                Dim ExcelQuery As String = "Select * from " & "[" & filename & "$" & "]"
                Dim cmd1 As New System.Data.OleDb.OleDbCommand(ExcelQuery, conn1)
                Dim rdr As OleDbDataReader = cmd1.ExecuteReader
                Console.WriteLine(vbCrLf & filename & vbCrLf & "=============")
                Dim connectionString As String = GetConnectionString() 'connection to access db, table = smsbestand
                Dim queryString As String = _
                    "SELECT volgnr, nummer  FROM smsbestand;"
                Using connection As New OleDbConnection(connectionString)
                    Dim command As OleDbCommand = connection.CreateCommand()
                    command.CommandText = queryString
                    Try
                        connection.Open()
                        Dim Adapter As New OleDbDataAdapter(ExcelQuery, conn1)
                        Dim NewSmsSet As New DataSet("smsbestand")
                        Dim smstabel As New DataTable("NewSmsSet")
                        Dim SmsRow As DataRow = smstabel.NewRow()
                        Adapter.Fill(NewSmsSet, "smsbestand")
                        Dim intCount As Integer
                        intCount = NewSmsSet.Tables("smsbestand").Rows.Count
                        MessageBox.Show("No. of Rows are : " + SmsRow.ToString())
                        Dim SmsColumn As Integer = NewSmsSet.Tables("smsbestand").Columns.Count
                        MessageBox.Show("No. of columns are : " + SmsColumn.ToString())
                        Dim NummerColumn As DataColumn = NewSmsSet.Tables("smsbestand").Columns("nummer")
                        Adapter.Update(NewSmsSet)
                        Dim telnr As String
                        Do While rdr.Read()
                            telnr = (rdr.GetString(0))
                            SmsRow("nummer") = telnr
                            smstabel.Rows.Add(SmsRow)
                            Console.WriteLine(telnr)
                        Loop
                        Adapter.Update(NewSmsSet, "smsbestand")
                        rdr.Close()
                        conn1.Close()
                        Dim Table As New DataTable
                        Adapter.Fill(NewSmsSet, "smsbestand")
                        Dim Getrokkennr As Integer
                        Table = NewSmsSet.Tables("smsbestand")
                        Table.PrimaryKey = New DataColumn() {Table.Columns("volgnr")}
                        Getrokkennr = RandomNumber(intCount, 1)
                        recaantal.Text = Str(intCount)
                        Dim s As String = Str(Getrokkennr)
                        Dim foundRow As DataRow = NewSmsSet.Tables("smsbestand").Rows.Find(s)
                        If foundRow IsNot Nothing Then
                            MsgBox(Getrokkennr.ToString())
                            MsgBox(foundRow(1).ToString())
                            txtgetrokken.Text = foundRow(1).ToString
                            txtvolgnr.Text = s
                        Else
                            MsgBox("A row with the primary key of " & s & " could not be found")
                        End If
                    Catch ex As Exception
                        Console.WriteLine(ex.Message)
                    End Try
                End Using
            Else
                Dim msg As String
                Dim style As MsgBoxStyle
                Dim response As MsgBoxResult
                msg = "Het gekozen bestand is geen excel bestand. Zoek opnieuw !             "   ' Define message.
                style = MsgBoxStyle.DefaultButton2 Or _
                MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly
                response = MsgBox(msg, style)
                txtFileUpload.Text = ""
                btZoek.Select()
            End If
        Else
            Dim msg As String
            Dim style As MsgBoxStyle
            Dim response As MsgBoxResult
            msg = "U dient het te verwerken bestand op te zoeken !             "   ' Define message.
            style = MsgBoxStyle.DefaultButton2 Or _
               MsgBoxStyle.Critical Or MsgBoxStyle.OkOnly
            response = MsgBox(msg, style)
            btZoek.Select()
        End If
    End Sub

[TomW]

Here are is a function I use, it will import an Excel worksheet into a dataset/datatable for you. After you get that much working, you can manipulate/format your data and then transfer it to your database.

'Calling Sub

Dim dtExcel As New DataTable
dtExcel = ImportExcelIntoDataTable(YourFileName)

'Just to view the imported results
DataGridView1.DataSource = dtExcel

Private Function CreateExcelConnectionString(ByVal strFile As String, Optional ByVal blnHeader As Boolean = True) As String

        Dim bldrCon As New OleDb.OleDbConnectionStringBuilder
        bldrCon("Provider") = "Microsoft.Jet.OLEDB.4.0"
        bldrCon("Data Source") = strFile

        'blnHeader specifies whether a header row is included or not
        'IMEX=1; - Tells the driver to read everything as intermixed text

        If blnHeader = True Then
            bldrCon("Extended Properties") = "Excel 8.0;HDR=YES"
        Else
            bldrCon("Extended Properties") = "Excel 8.0;HDR=NO"
        End If

        Return bldrCon.ConnectionString

    End Function

Public Function ImportExcelIntoDataTable(ByVal strFile As String, Optional ByVal strWorkSheet As String = "Sheet1", Optional ByVal blnHeaderRow As Boolean = True, Optional ByVal blnDataAsText As Boolean = True) As DataTable

        Dim dtXls As New DataTable
        Dim strDbCon As String = ""

        'Connection string to Excel file
        strDbCon = CreateExcelConnectionString(strFile, blnHeaderRow)

        Using con As New OleDbConnection(strDbCon)
            Dim daXls As New OleDbDataAdapter("Select * From [" & strWorkSheet & "$]", con)
            daXls.Fill(dtXls)
        End Using 'con

        Return dtXls

    End Function