Importing Excel file to SQL Server (Opinions please)

Dear All,

I am writing a procedure to importdaily the customer excel file to SQL server 2000, I managed to do that where the excel file will be imported directly to the SQL server after creating the new data table, & then I need to read the created table & import it row by row to my original data table.
The problem:

I. The original excel file has the following:
a. a protection password
b. The contents has two merged headers (which effecting the import procedure)
c. And last line is a totals line

Before importing the file I have manually to remove (a – b & c)!!

The Solution:

II. I am trying to find a way to do the above points automatically inside the project.

III. Also I thought of importing the excel file to a DataGrid first then:
a. Let the user approve the file contents &
b. Remove manually point (I.b.) above (I don't now how yet, need to try it).
c. Then import the DataGrid the the SQL server.

I think I prefer solution (III), any suggestions are highly appreciated

BR

Try this thread and read my post there is code and a link to all you will need including free Video tutorials from Microsoft. Hope this helps

http://forums.asp.net/928520/ShowPost.aspx

|||

Dear Caddre,

Thank you for your reply,
I have checked the posted links. they are talking about exporting to excel from sql server/browser.
this not what I want.

Anyhow thanks again.

|||

Hi again,
I desided to preview the excel sheet in a datagrid when the user selects the file, worked fine.
but still I am stuck if the excel file has a password, I tried to do the following :

oOLEDBConn =New OleDb.OleDbConnection
oOLEDBDA = New OleDbDataAdapter("SELECT * FROM " & sDataSheet & " ", oOLEDBConn)
oDS =New DataSet
oOLEDBConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFilePath & sFileName & ";Extended Properties=Excel 8.0;Password='123';"
oOLEDBConn.Open()
oOLEDBDA.Fill(oDS, "Sheet")

In this case i am getting the following error whether the file has a password or not:
(Cannot start your application. the workgroup information file is missing or opened exclusively by another user)

Any suggestions please.