Solved SQL Client version of OledB code to get data from Excel to SQL

Posted on 2012-09-04
Last Modified: 2012-09-07
Sub Excel_to_SQL()

        ' Create Data Table for MS-Office 2007 or 2003

        Dim dtExcel As New System.Data.DataTable()

        dtExcel.TableName = "MyExcelData"


What would the equivalent of the following code be?


        Dim SourceConstr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Sachindra\Desktop\MyExcel2003.xls';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'"

        Dim con As New OleDbConnection(SourceConstr)

        Dim query As String = "Select * from [Sheet1$]"

        Dim data As New OleDbDataAdapter(query, con)

        MsgBox("Data Imported Successfully into DataTable")

    End Sub
Question by:murbro
    LVL 83

    Expert Comment

    You mean VBA? The above code uses ADO.NET which is not available in VBA. You would need to use recordsets.
    LVL 35

    Accepted Solution

    use this to read Excel data into a datatable:

     Function ReadDataFromExcel(ByVal path As String) As System.Data.DataTable
            Dim ds As New DataSet
            Dim da As OleDbDataAdapter
            Dim conn As OleDbConnection = Nothing
            Dim sheet As String
                conn = New OleDbConnection( _
                     "provider=Microsoft.ACE.OLEDB.12.0; " & _
                     "data source=" & path & "; " & _
                     "Extended Properties=Excel 12.0;")
                ''get sheet name
                sheet = GetSheetName(path)
                da = New OleDbDataAdapter("SELECT * FROM [" & sheet & "]", conn)
                ''get the name of the last column
                Dim lastCol As DataColumn
                lastCol = ds.Tables(0).Columns(ds.Tables(0).Columns.Count - 1)
                ''remove the last column from a DataTable
                '' ds.Tables(0).Columns.Remove(lastCol)
            Catch ex As Exception
                If conn.State = ConnectionState.Open Then
                End If
            End Try
            ReadDataFromExcel = ds.Tables(0)
        End Function

    Open in new window

    copy the code above and then call it as

    Dim dt as New Datatable
    dt=ReadDataFromExcel("excel file path here")

    Open in new window


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now