473,322 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
8 8426
kenobewan
4,871 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

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

Similar topics

1
by: Richard Holliingsworth | last post by:
Hello: Thanks for your quick response. I'm trying to import a new Excel file into an A2K table and it's truncating the data. One of the Excel columns is a text field that can be up to 2000...
1
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
2
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access...
4
by: Steve Jorgensen | last post by:
I'm restarting this thread with a different focus. The project I'm working on now id coming along and will be made to work, and it's too late to start over with a new strategy. Still, I'm not...
3
by: deko | last post by:
I've been trying to use the Access Import Wizard to expedite importing data into my mdb. The nice thing about the wizard is that I can import from different file formats - txt, xls, even Outlook -...
10
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in...
2
by: madeleine | last post by:
I'm hoping the answer to this is that I'm just doing something silly, but I'm really scratching my head over this one. I'm importing data from multiple workbooks, each workbook has a sheet called...
8
by: FireGeek | last post by:
I have a database that is split appropriately. Annually, we need to add data from .xls files. I have added code so with a click of a button, it will import this data as a new table into the...
1
by: Eric | last post by:
In MS Excel, the ability exists to run a "web query." This function is accessed via the data menu's import external data option. The web query wizard accepts a URL address, and then is able to...
7
by: TG | last post by:
hi! I am trying to create a sql server table from an excel sheet. Here is the code I have: 'This procedure the xlsx file and dumps it to a table in SQL Server
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.