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!
 

Answer 1

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.

Answer 2

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

Answer 3

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/

Answer 4

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.

Answer 5

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.

Answer 6

jwavila, thank you very much for the link...that will come in handy!

Answer 7

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

Answer 8

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