locked
Import Excel Data Into SQL Server 2008

    Question

  • I appreciate any help you can lend!

    I've been trying to write code to import Excel files into a SQL Table using the following code (from http://support.microsoft.com/kb/321686) with little success.

    This is a VB.Net app using Visual Studios 2008 with SQL Server 2008 on a Vista Ultimate 64 machine.

    The runtime error encountered is:
    "COMException was unhandled...Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

    After searching online I discovered two work arounds from Microsoft:

    1. editing the registry...the instructions were out of data (2005) and I wasn't comfortable making guesses at registry changes. 
    2. double checking the connection string...the connection string is fine, it is used throughout the app successfully and I double checked the variables to ensure they're correctly populated at runtime


    Here is my code:

            Dim cn As ADODB.Connection
            Dim strSQL As String
            Dim lngRecsAff As Long
            cn = New ADODB.Connection
            cn.Open("Server=ADAMS-LAPTOP-PC\SQLEXPRESS; integrated security = true;" & "database=" & strm)

            'Import by using OPENROWSET and object name.
            strSQL = "INSERT * INTO " & moduletableCB.Text & " FROM " & _
                "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
                "'Excel 8.0;Database=" & importpath & "', " & _
                "[Sheet1$])"

            cn.Execute(strSQL, lngRecsAff, ADODB.ExecuteOptionEnum.adExecuteNoRecords)
            'Debug.Print("Records affected: " & lngRecsAff)

            cn.Close()
            cn = Nothing

    Thank you very much for any help!
     
    Friday, December 04, 2009 12:12 AM

Answers

  • Hi Adam,

    I don't have SQL Server 2008, so sorry i can't reproduce your scrnario. But i test the following code using SQL Server 2000, it works well.
    You can get a try.
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Const constr As String = "server=(local);database=TestDB;uid=sa;pwd=sa;"
            Dim fileName As String = "C:\Test\xltest.xls"
            If File.Exists(fileName) Then
                Dim conn As SqlConnection = New SqlConnection(constr)
                conn.Open()
                Try
                    Dim strSQL As String
                    'insert into a existing table
                    strSQL = "INSERT INTO zz SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & fileName & ";HDR=YES','SELECT * FROM [test$]')"
                    'create a new table
                    'strSQL = "SELECT * INTO zz FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & fileName & ";HDR=YES','SELECT * FROM [test$]')"
                    Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
                    cmd.ExecuteNonQuery()
                    conn.Close()
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End If
        End Sub
    Besides, i can open all the links in the link i provide, would you please try it again, maybe use other webbroswer?

    Regards
    Jeff Shan
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Jeff Shan Friday, December 11, 2009 6:57 AM
    Thursday, December 10, 2009 6:57 AM

All replies

  • Hi Adam,

    Here is a thread about how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases you can refer to
    http://social.msdn.microsoft.com/Forums/en/vbgeneral/thread/84a27418-5f8a-4e1a-b425-09bf33d8e92d

    Hope this helps

    Regards
    Jeff Shan


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, December 09, 2009 8:49 AM
  • Jeff, thank you very much for your response.

    I tried the links in the link you recommended and two of them are for C but I'm using VB.Net and don't am not skilled at translating the two.  Another link led to a site that had links that didn't work any more, and the last was the link that I reference in my original post that causes the "COM" error.

    Can you help me get past this "COM" error mentioned in the original post at the top?

    Thanks again

    Adam
    Wednesday, December 09, 2009 9:59 PM
  • Adam

    here is a converter tool. Just copy the c code, paste it in the textbox and click the button


    http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Wednesday, December 09, 2009 10:51 PM
  • Hi Adam,

    I don't have SQL Server 2008, so sorry i can't reproduce your scrnario. But i test the following code using SQL Server 2000, it works well.
    You can get a try.
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Const constr As String = "server=(local);database=TestDB;uid=sa;pwd=sa;"
            Dim fileName As String = "C:\Test\xltest.xls"
            If File.Exists(fileName) Then
                Dim conn As SqlConnection = New SqlConnection(constr)
                conn.Open()
                Try
                    Dim strSQL As String
                    'insert into a existing table
                    strSQL = "INSERT INTO zz SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & fileName & ";HDR=YES','SELECT * FROM [test$]')"
                    'create a new table
                    'strSQL = "SELECT * INTO zz FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & fileName & ";HDR=YES','SELECT * FROM [test$]')"
                    Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
                    cmd.ExecuteNonQuery()
                    conn.Close()
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End If
        End Sub
    Besides, i can open all the links in the link i provide, would you please try it again, maybe use other webbroswer?

    Regards
    Jeff Shan
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Jeff Shan Friday, December 11, 2009 6:57 AM
    Thursday, December 10, 2009 6:57 AM
  • Don't forget to import the following namespaces
    Imports System.Data.SqlClient
    Imports System.IO

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, December 10, 2009 7:00 AM
  • jwavila, thank you very much for the link...that will come in handy!
    Wednesday, December 16, 2009 7:24 PM
  • Jeff, thank you very much for your help...I still have the error and have abandon the this technique for SQLBulkCopy which doesn't give me an error.

    Thanks again for your help!!!!!

    Adam
    Wednesday, December 16, 2009 7:26 PM
  • First of all, tou have to register the Excel archive as a data server:

    EXEC sp_addlinkedserver @server = N'ExcelDataSource', 
    @srvproduct=N'ExcelData', @provider=N'Microsoft.ACE.OLEDB.12.0', 
    @datasrc=N'C:\path to your excel file',
    @provstr=N'EXCEL 12.0' ;

    After, you just need to refer this server (and the sheet) in your query:

    SELECT * FROM ExcelDataSource...[Sheet1$];

    GO

    Friday, July 13, 2012 7:52 PM