Import Excel 2007 to Sql Server Using VB.Net

Question

Hello,

 

I have been pulling my hair out for forever now trying to figure this out! I'm trying to import an excel 2007 file to sql server using vb.net. Here is my code:

 

Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myExcelFile.xlsx;Extended Properties=Excel 12.0;")

excelConnection.Open()

 

Dim excelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ODBC; Driver={SQL Server};Server=myServer;Database=myDB;Trusted_Connection=yes].[ExcelSource] FROM [Sheet1$];", excelConnection)

 

I keep getting the error: ODBC--call failed.

 

Please help!

Thanks!

Answer 1

I have been trying different things and I seem to be going in circles, not knowing which way is going forward and which is backwards. I either get the ODBC failed msg, the error that "Can't find installable ISAM" or "external table is not in the expected format"! There must be someone out there who successfully imported ".xlsx" files into sql server 2005 tables via code??

 

I am willing to try different code, even to import via t-sql if that is necessary. Unfortunately, it must be in code since I have to allow the user to specify the file to import, the server and login credentials.

 

This is URGENT!!!! Any help would be appreciated!

Thank-you!

Answer 2

e_com1,

 

How is your problem going? According to the question on importing the Excel 2007 data to SQL Server 2005, I would like to provide you the suggestions as follows:

 

1. Connection string issue: Please check if you are able to connect both Excel 2007 and SQL Server 2005 as the data source successfully. The excelConnection seems to be find as you provided. Then you can also add "HDR=YES" to indicate that the first row contains columnnames, not data. "HDR=No;" indicates the opposite. This can avoid the first row confused when importing to the SQL tables.

 

2. Please test your ODBC SQL Server connection string in order that the connection to SQL Server is correct. Except the ODBC, you can also use SqlConnection and execute the SqlCommand object with the statement that includes the excelConnection in your VB.NET code, but not just integrate the whole in one SQL statement. The following article shows you the way:

 

Import Excel sheet into Table

 

3. Except the above methods, you can consider to use SqlBulkCopy class to import. The following article shows you the example:

 

Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

4. There is a KB article in MSDN on the topic. Although the article is not applied for SQL Server 2005, you can also learn on SQL Server Data Transformation Services (DTS), Microsoft SQL Server 2005 Integration Services (SSIS), SQL Server linked servers and SQL Server distributed queries. They are helpful on your work.

 

How to import data from Excel to SQL Server

Hope that can help you.

Answer 3

Thanks Bruno for your response. Here is the solution that worked for me:

 

Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myExcel.xlsx;Extended Properties=Excel 8.0")

 

excelConnection.Open()

 

Dim excelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [ODBC; Driver={SQL Server};Server=myServer;Database=myDB;Trusted_Connection=yes].[ExcelSource] FROM [Sheet1$];", excelConnection)

 

The other most important piece was I had to have the excel file that I was importing open during the import, otherwise, I received the error: "External table is not in the expected format"

 

Hope this helps out others who may been struggling with this as much as I had been!

 

 

Answer 4

Thanks e_com1 for the code but can you tell me which method u used to execute the command?

I am getting the following Error even though there are some rows in the excel sheet.

"Query input must contain at least one table or query."

I used ExecuteScalar.

Thanks in advance!

Answer 5

I guess I got that error because "Select Into" creates a new table and inerts data into the newly created table but i want to insert data into an already existing table.

How can I do this?

Correct me if i am wrong about my assumption.

Answer 6

I'm using the executenonquery.

Answer 7

tstop,

 

You are correct, the select into creates a new table. The insert into will insert into an existing table. Here's the code:

 

Dim excelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [OBDC; Driver={SQL Server};Server=myServer;Database=myDB;Trusted_Connection=yes].[myTable] SELECT * FROM [Sheet1$];", excelConnection)

 

 

Answer 8

Thanks for the help e_com1.

Answer 9

Hi,

e_com1 is right, but you could try this script too (is better to the case of sheet having a new name (renamed) or if the language of the Excel isn't English):

Dim strConnection As System.Data.OleDb.OleDbConnection

Dim myPath As String = "c:\sample.xls"

Dim dsData as New DataSet

Dim dt As New DataTable

Dim objAdapter As System.Data.OleDb.OleDbDataAdapter

strConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & myPath & " '; " & "Extended Properties=Excel 8.0;")

strConnection.Open()

dt = strConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

objAdapter = New System.Data.OleDb.OleDbDataAdapter(String.Format("SELECT * FROM [{0}]",

dt.Rows(0).Item("TABLE_NAME")), strConnection)

objAdapter.Fill(dsData)

Answer 10

Why for G_D S_ke was bcp invented?????????????

(I hope that this was usefull.)

---

ADH

Answer 11

Thanks e_com1 

 

Answer 12

I'm glad I stumbled across this thread.  Thanks for posting your solution e_com1.

I am having one issue though.  What if the Excel spreadsheet has a header row, but the names don't match the SQL columns?  Here's the error I get:
The INSERT INTO statement contains the following unknown field name: 'Card Prefix'.  Make sure you have typed the name correctly, and try the operation again.

Here's my connection strings:
Excel...
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Spreadsheet.xls;Extended Properties="Excel 8.0;IMEX=1;HDR=Yes;"
OleDB...
INSERT INTO [ODBC; Driver={SQL Server};Server=SERVER;Database=DBNAME;UID=USER;PWD=PASSWORD].[tblTEST] SELECT * FROM [Sheet1$];

Any thoughts?  I've tried it with HDR=No, but it doesn't seem to like that either.  Same error but it references cell F1.

---

Digital Deviation - www.DigitalDeviation.com

Answer 13

Justin D_,

 You may try replacing the ".... SELECT * FROM [Sheet1$]'..." with the following statement:

... SELECT ExcelColumnName1 AS SqlColumnName1,  ExcelColumnName2 AS SqlColumnName2, ExcelColumnName3 AS SqlColumnName3 (...and so on) FROM [Sheet1$]...