Solved

error exporting Excel to SQL

Posted on 2010-11-11
6
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

726 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