Solved

error exporting Excel to SQL

Posted on 2010-11-11
6
467 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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