How to Import data from Excel into MS SQL in .Net?

Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.

I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work.

- Most simple and direct way I've tried is creating a oledb connection to the Excel file in the following codes...

Function TestUploadData1(ByVal vFile As String) As String
Dim Oleda As System.Data.OleDb.OleDbDataAdapter
Dim Olecn As System.Data.OleDb.OleDbConnection
Dim dt1 As DataTable

Olecn = New System.Data.OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & vFile & ";" & _ "Extended Properties=Excel 8.0;HDR=Yes")
Olecn.Open()

Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn)
ExcelCommand.ExecuteNonQuery()

Olecn.Close()
End Function

But from the above codes I kept getting the error "Could not find installable ISAM." at the line Olecn.Open(). I've tried to set the registry to C:\Windows\System32\msexcl40.dll but to no avail...

- I've tried another method using the OpenRowSet in the following codes
Function TestUploadData2(ByVal vFile As String) As String
Dim cn As SqlConnection
Dim cm As SqlCommand
Dim strSQL As String cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")

cn.Open()

strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
cm = New SqlCommand(strSQL, cn)
cm.ExecuteNonQuery()

cn.Close()
End Function

For the above codes, I kept getting the error "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." at the line cm.ExecuteNonQuery() and I've tried to disable the Ad Hoc settings in the registry and tried to create a link server but to no avail as well...

- I've tried DTS but I'd need to DTS package to run whenever the users upload a file. Is there an easy way that I can run the designated DTS package to run through my .Net web application?

Any help would be deeply appreciated.
Thanks & Regards,
Keith Chang

Here is a very simple article which you can extend to your needs.

http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx

There are some solutions available for this problem. You already tried one. Have a look at this post also.

http://www.dotnetspider.com/qa/Question32454.aspx

Sometimes, even the connections string can be a problem (yours looks fine though)

|||

Here is a small exerpt from one of my projects:

If FileUpload1.HasFileThen

Dim filenameAsString

Dim cmdAs OleDbCommandDim daAsNew OleDbDataAdapter

filename = System.IO.Path.GetTempFileName

FileUpload1.SaveAs(filename)

Dim connAsNew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename &";Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")

' Find Sheet Names

Dim SheetsAs Generic.List(OfString)

Sheets = GetSheets(conn)

' Find Header Row

cmd =New OleDbCommand("SELECT * FROM [" & Sheets(0).ToString &"$A:A]", conn)

Dim dshAsNew DataSet

da =New OleDbDataAdapter(cmd)

da.Fill(dsh)

And the GetSheets function:

Function GetSheets(ByVal connAs OleDbConnection)As Generic.List(OfString)

Dim SheetsAsNew Generic.List(OfString)

Dim dtAs DataTable

If conn.State = ConnectionState.ClosedThen

conn.Open()

EndIf

dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,NewObject() {Nothing,Nothing,Nothing,"TABLE"})

' Sheets end in $, but may have quotes around the name, this gets rid of named ranges from the list

Dim dvAsNew DataView(dt,"TABLE_NAME LIKE '%$' OR TABLE_NAME LIKE '%$'''","TABLE_NAME", DataViewRowState.Unchanged)

ForEach drAs DataRowIn dv.ToTable.Rows

If dr("TABLE_NAME").ToString.StartsWith("'")AndAlso dr("TABLE_NAME").ToString.EndsWith("'")Then

'Remove beginning quote, and ending dollar sign quote

Sheets.Add(dr("TABLE_NAME").ToString.Substring(1, Len(dr("TABLE_NAME").ToString) - 3))

Else

'Remove ending dollar sign

Sheets.Add(dr("TABLE_NAME").ToString.Substring(0, Len(dr("TABLE_NAME").ToString) - 1))

EndIf

Next

Return Sheets

EndFunction

|||

Thanks for replying Motley... But I believe your scripts were to extract the data from excel into a datatable. Well I can do that, no problem... Problem is how do I put this data into my MS SQL server?! Is there any way that I can just upload this dataset or datatable straight into the designated table in the MS SQL server?

I'm using .Net 1.1 so I can't use the SQLBulkCopy which is for 2.0 only... but still thanks bullpit.

|||

Once the data is in a dataset, you can then use a sqldataadapter or just iterate through the rows and issue the insert commands.

|||I have thought of that... but problem is that I've around 56 columns and around 600-700 records which will take quite some time to import. And I have around 15 files that need to go through this process... It'd take half a day just to import all these files...|||

Using sqldataadapter in batch mode will be the fastest way to import the data.

|||

Thanks for all help. But I've finally found my own solution to it. I used the DTS and the following code to execute the DTS package prepared to do the transfer in asp.net which did not take more than 2 seconds. Just that I need to open the excel file and adjust the formatting to assure correct importing which takes much longer time around 30 seconds.

'Update respective table (add reference to Microsoft DTSPackage Object Library)
vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

Hope this is useful to whoever facing the same problem as me.

|||

Hi KeithChang,

I am getting same problem as you mentioned on the first post message. I tried the OPENROWSET AND OPENDATASORUCE Command in .Net 1.1. But its not working.

Please tell me, how to implement this above code .

vDTS.LoadFromSQLServer("localhost","dbid","dbpassword", DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"pkgpassword", , ,pkgDaysin", )
vDTS.Execute()
vDTS.UnInitialize()
vDTS =Nothing

This is helpful for me

Thank you

Sadheesh