Import Excel 2007 to Sql Server 2005

[ecom1]

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??

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 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!

 

[mentalhard]

usually i am doing that by implementing the BulkCopy class. Check this link for more info http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

 

[ecom1]

Mentalhard, you have done this to retrieve data from excel? do you have a code sample to post?

thanks.