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

How to implement remote Data Entry then upload to corp. database?

I'm looking for different solution people have implemented to solve this
problem.
Problem: People need to work remotely. While working remotely they want to
gather information. This information might be survey info, or inspection
info, etc. This information is to be stored remotely on a handheld, or
laptop device. After they get back to the office/homeo office they want to
upload the information to our corporate database, which is SQL Server 2000.
This upload process should be very easy, like a like of a button, or done
automatically when the devices is docked into the corporate network. This
solution has to be supported over the Internet, and intranet.
What I'm looking for is what technologies and processes have people put in
places to implement a solution to handle the problem I stated above.
If you have more questions about what I am asking for please let me know.Greg Larsen wrote:
> I'm looking for different solution people have implemented to solve this
> problem.
> Problem: People need to work remotely. While working remotely they want t
o
> gather information. This information might be survey info, or inspection
> info, etc. This information is to be stored remotely on a handheld, or
> laptop device. After they get back to the office/homeo office they want t
o
> upload the information to our corporate database, which is SQL Server 2000
.
> This upload process should be very easy, like a like of a button, or done
> automatically when the devices is docked into the corporate network. This
> solution has to be supported over the Internet, and intranet.
> What I'm looking for is what technologies and processes have people put in
> places to implement a solution to handle the problem I stated above.
> If you have more questions about what I am asking for please let me know.
This is all supported through Merge Replication. Take a look at the
replication topics in Books Online.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--