Import Excel using VB 2005 into SQL by button click

Posted on 2007-08-02
Last Modified: 2013-11-26
I am working on a front-end for a SQL database.  I'm using VB 2005.  The SQL server is 2000.  I need a solution for a user to click on a button to bring up a file open window.  Then the user will navigate to the Excel file, which needs to be imported into SQL.  The Excel file will have the same fields as the SQL database.  It's just the file name will be different each time so they have to navigate to the correct file through the file open window.  Once the user chooses the file and click on open.  I need the Excel file to be imported into the table on the SQL server.  Any help is appreciated very much.
Question by:hatforce
    LVL 96

    Expert Comment

    by:Bob Learned
    1) Use OleDb to read the Excel file into a DataTable

    2) Use the SqlBulkCopy to insert the data into the SQL Server table.


    Author Comment

    I guess I need to work on the code to actually import the data first before moving onto creating a file open window for the users.  

    Ok.  I have the following code to open a specific file on my C:\ drive:

    Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click

            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Product Add Spreadsheettest.xls;Extended Properties=""Excel 8.0;HDR=Yes""")
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=(local);Data Source=A\DATASERVER;Initial Catalog=DATA1]; SELECT * FROM [DATA Add];", ExcelConnection)

        End Sub

     When I try to click the button I get an error "OleDbException was unhandled"  Syntex error in INSERT INTO statement.  The highlighted error references to "ExcelCommand.ExecuteNonQuery()".  

    My SQL server is local (for now) it will be remote when the app is launched for production.
    LVL 96

    Accepted Solution

    You are going to have to read the values into a DataTable, using and OleDbDataAdapter, and then use the SqlBulkCopy to quickly add the records to SQL Server.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    754 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

    22 Experts available now in Live!

    Get 1:1 Help Now