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
scosta wrote:
atatestfilename".