Solved

error exporting Excel to SQL

Posted on 2010-11-11
6
465 Views
Last Modified: 2012-05-10
im  trying to export excel worksheet to SQL server and create a table there.However
I am getting the error
"Keyword not supported:provider


Im using excel 2010 and sql server 2008

Here's my code:

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.Sql
Imports System.Data.OleDb

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
               Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;"

        'Create Connection to Excel Workbook
        Using connection As SqlConnection = New SqlConnection(excelConnectionString)---Error
           
            "Keyword not supported:provider' Error message

            Dim myCommand As New SqlCommand("Select Id,Location,Status,Areab from [Sheet1$]")

            connection.Open()

            '// Create DbDataReader to Data Worksheet
            Using dr As SqlDataReader = myCommand.ExecuteReader()
                '// SQL Server Connection String
                'Dim sqlConnectionString As String = "Data Source=TMA; Initial Catalog=VTAM;Integrated Security=True"
                Dim myconnection As New SqlConnection
                Dim sqlConnectionString As String = "server=Bear;User Id=Vtam;pwd=mnt;database=GMat"
                myconnection.ConnectionString = sqlConnectionString
                '// Bulk Copy to SQL Server
                Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(sqlConnectionString)
                    bulkCopy.DestinationTableName = "XData"
                    bulkCopy.WriteToServer(dr)
                End Using
            End Using
        End Using
    End Sub

thanks for your help
0
Comment
Question by:zachvaldez
6 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 34117900
Try

 Dim excelConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=" & "Excel 8.0;HDR=YES;"

0
 

Author Comment

by:zachvaldez
ID: 34118156
error "could not find installable ISAM on line

cnExcel.open()
oledbexception was unhandled by user code
0
 
LVL 5

Assisted Solution

by:Pabilio
Pabilio earned 150 total points
ID: 34119435
Hi zach,

Check this link: http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.connectionstring.aspx
It could be helpfull...

I get the same problem some time ago... what I did was:

1) With the Get external data tool in a new workbook in excel from any table in the Data base at the server you want to reach.
Once you have some sample data in the excel spreadsheet run the following code in sheet1:

2)  
Sub foo()
Range("F1").Value = Worksheets(1).QueryTables(1).Connection
End Sub

This will display at cell F1 the connection string used from excel and maybe it could help you to find the error...
You will see there something like:
ODBC;DRIVER=SQL Server;SERVER=SERVIDOR;UID=Administrador;APP=Microsoft Office 2003;WSID=SERVIDOR;DATABASE=CEDRO;Network=DBMSLPCN;Trusted_Connection=Yes

3)
And then you can change your connection method to something like:

Set cnn = New ADODB.Connection
   sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
       "Persist Security Info=False;" & _
       "Initial Catalog=CEDRO;" & _
       "Data Source=SERVIDOR"
   cnn.Open sConnString

Hope it works... it did it for me.

Good luck,
Roberto.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 33

Expert Comment

by:Norie
ID: 34121229
Are you using the correct driver/provider?

This is the connection string I use with XL2010 and SQL2008.

strConnection = "'Microsoft.ACE.OLEDB.12.0', " & _
        "'Data Source=C:Accountos.xls;" & _
        "Extended Properties=Excel 12.0'"

0
 

Author Comment

by:zachvaldez
ID: 34121235
I passed the error line : I did
Extended Properties=""Excel 8.0;HDR=YES"""

IM getting error here:

  Using dr As SqlDataReader = myCommand.ExecuteReader()
'connection property has not been initialized

0
 

Author Comment

by:zachvaldez
ID: 34121973
I fixed the error, however I noticed that if the column or field in excel has null values it errors out
How can I passed the query to ignore the nulls in the select statement?
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

947 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

16 Experts available now in Live!

Get 1:1 Help Now