VB.net SQL Client version of OledB code to get data from Excel to SQL

Posted on 2012-09-04
Medium Priority
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 VB.net 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:Murray Brown
LVL 83

Expert Comment

ID: 38363390
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

YZlat earned 2000 total points
ID: 38363759
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

by:Murray Brown
ID: 38377364

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

599 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