467,927 Members | 1,845 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,927 developers. It's quick & easy.

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...
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData1(ByVal vFile As String) As String
  2.         Dim Oleda As System.Data.OleDb.OleDbDataAdapter
  3.         Dim Olecn As System.Data.OleDb.OleDbConnection
  4.         Dim dt1 As DataTable
  5.  
  6.         Olecn = New System.Data.OleDb.OleDbConnection( _
  7.             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.             "Data Source=" & vFile & ";" & _
  9.             "Extended Properties=Excel 8.0;HDR=Yes")
  10.         Olecn.Open()
  11.  
  12.         Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Connection=yes].[tblOutstanding] FROM [Report$];", Olecn)
  13.         ExcelCommand.ExecuteNonQuery()
  14.  
  15.         Olecn.Close()
  16.     End Function
  17.  
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
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData2(ByVal vFile As String) As String
  2.         Dim cn As SqlConnection
  3.         Dim cm As SqlCommand
  4.         Dim strSQL As String
  5.  
  6.         cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")
  7.         cn.Open()
  8.  
  9.         strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
  10.  
  11.         cm = New SqlCommand(strSQL, cn)
  12.         cm.ExecuteNonQuery()
  13.  
  14.         cn.Close()
  15. End Function
  16.  
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 disble 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
Jun 15 '07 #1
  • viewed: 8141
Share:
8 Replies
kenobewan
Expert 4TB
I'd stick with ado.net but have you tried using sqloledb?
Jun 15 '07 #2
sqloledb is just the normal declaration like sqlConnection, sqlCommand isn't it? If so, yea I did.

I'd stick with ado.net but have you tried using sqloledb?
Jun 18 '07 #3
Hi Keith,

Did you solve this issue you had. Cause i am doing a similar thing and i am getting the same error. One thing i found is that when uploading and using save as the file is saved as a html format with extension .xls.. How did u over come this issue ?? pls let me know
Hari V

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...
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData1(ByVal vFile As String) As String
  2.         Dim Oleda As System.Data.OleDb.OleDbDataAdapter
  3.         Dim Olecn As System.Data.OleDb.OleDbConnection
  4.         Dim dt1 As DataTable
  5.  
  6.         Olecn = New System.Data.OleDb.OleDbConnection( _
  7.             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.             "Data Source=" & vFile & ";" & _
  9.             "Extended Properties=Excel 8.0;HDR=Yes")
  10.         Olecn.Open()
  11.  
  12.         Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Connection=yes].[tblOutstanding] FROM [Report$];", Olecn)
  13.         ExcelCommand.ExecuteNonQuery()
  14.  
  15.         Olecn.Close()
  16.     End Function
  17.  
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
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData2(ByVal vFile As String) As String
  2.         Dim cn As SqlConnection
  3.         Dim cm As SqlCommand
  4.         Dim strSQL As String
  5.  
  6.         cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")
  7.         cn.Open()
  8.  
  9.         strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
  10.  
  11.         cm = New SqlCommand(strSQL, cn)
  12.         cm.ExecuteNonQuery()
  13.  
  14.         cn.Close()
  15. End Function
  16.  
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 disble 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
Oct 12 '07 #4
Dim Str_MdbConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "data source= " & ExlFilename & ";" _
& "Extended Properties=Excel 8.0;"
Dim conn As New OleDbConnection(Str_MdbConn)
Try
If conn.State <> ConnectionState.Open Then
conn.Open()
Oct 12 '07 #5
Dim xlApp As New Excel.Application
Dim xlWrkBk As Excel.Workbook
xlWrkBk = xlApp.Workbooks.Open(XLSFileName)
Dim xlWrkSht As Excel.Worksheet
xlWrkSht = xlWrkBk.Worksheets(1)
To_find_Last_row = xlWrkSht.Cells.Find(What:="*", SearchDirection:=Excel.XlSearchDirection.xlPreviou s, SearchOrder:=Excel.XlSearchOrder.xlByRows).Row
Album_Name = xlWrkSht.Cells(4, 2).value
UPC = xlWrkSht.Cells(4, 3).value
Volume = xlWrkSht.Cells(4, 6).value
Pic_Path = Path.GetDirectoryName(XLSFileName) & "\" & xlWrkSht.Cells(4, 14).value
Album_Type = xlWrkSht.Cells(4, 17).value
Oct 12 '07 #6
[UPC] Is column name [Shock Digital Release$] is Excel form name

Dim Str_MdbCmd As String = "Select Distinct [UPC] from [Shock Digital Release$]"
Dim Str_MdbConn As String = "Provider=Microsoft.Jet.OLEDB.4.0; "data source= " & ExlFilename & ";" _
& "Extended Properties=Excel 8.0;"
Dim conn As New OleDbConnection(Str_MdbConn)
If conn.State <> ConnectionState.Open Then conn.Open()

MdbCmd = New OleDbCommand(Str_MdbCmd, Mdbcon)
MdbDr = MdbCmd1.ExecuteReader()
If MdbDr.HasRows Then
While MdbDr.Read
If Not Convert.IsDBNull(MdbDr.GetValue(0)) Then
Try
Album_IDS.Add(i, Convert.ToInt64(MdbDr.GetValue(0)))
i += 1
Catch e As Exception
'write exception
End Try
End If
End While
End If
MdbCmd.Connection.Close()
Mdbcon.Close()

catch ex as exception
end try
Oct 12 '07 #7
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...
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData1(ByVal vFile As String) As String
  2.         Dim Oleda As System.Data.OleDb.OleDbDataAdapter
  3.         Dim Olecn As System.Data.OleDb.OleDbConnection
  4.         Dim dt1 As DataTable
  5.  
  6.         Olecn = New System.Data.OleDb.OleDbConnection( _
  7.             "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.             "Data Source=" & vFile & ";" & _
  9.             "Extended Properties=Excel 8.0;HDR=Yes")
  10.         Olecn.Open()
  11.  
  12.         Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Connection=yes].[tblOutstanding] FROM [Report$];", Olecn)
  13.         ExcelCommand.ExecuteNonQuery()
  14.  
  15.         Olecn.Close()
  16.     End Function
  17.  
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
Expand|Select|Wrap|Line Numbers
  1. Function TestUploadData2(ByVal vFile As String) As String
  2.         Dim cn As SqlConnection
  3.         Dim cm As SqlCommand
  4.         Dim strSQL As String
  5.  
  6.         cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")
  7.         cn.Open()
  8.  
  9.         strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')"
  10.  
  11.         cm = New SqlCommand(strSQL, cn)
  12.         cm.ExecuteNonQuery()
  13.  
  14.         cn.Close()
  15. End Function
  16.  
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 disble 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

In the 1st code just put SELECT * INTO......
Jan 11 '08 #8
ANNA284

In the 1st code just put SELECT * INTO......
Jan 11 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Niklas | last post: by
2 posts views Thread by madeleine | last post: by
1 post views Thread by Eric | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.