Solved

error exporting Excel to SQL

Posted on 2010-11-11
6
463 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

20 Experts available now in Live!

Get 1:1 Help Now