Solved

error exporting Excel to SQL

Posted on 2010-11-11
6
468 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

856 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