473,837 Members | 1,468 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

2 New Member
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\Syst em32\msexcl40.d ll 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.ExecuteNonQu ery() 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 8457
kenobewan
4,871 Recognized Expert Specialist
I'd stick with ado.net but have you tried using sqloledb?
Jun 15 '07 #2
KeithChang
2 New Member
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
hariviswa
1 New Member
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\Syst em32\msexcl40.d ll 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.ExecuteNonQu ery() 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
KnightHack
15 New Member
Dim Str_MdbConn As String = "Provider=Micro soft.Jet.OLEDB. 4.0;" _
& "data source= " & ExlFilename & ";" _
& "Extended Properties=Exce l 8.0;"
Dim conn As New OleDbConnection (Str_MdbConn)
Try
If conn.State <> ConnectionState .Open Then
conn.Open()
Oct 12 '07 #5
KnightHack
15 New Member
Dim xlApp As New Excel.Applicati on
Dim xlWrkBk As Excel.Workbook
xlWrkBk = xlApp.Workbooks .Open(XLSFileNa me)
Dim xlWrkSht As Excel.Worksheet
xlWrkSht = xlWrkBk.Workshe ets(1)
To_find_Last_ro w = xlWrkSht.Cells. Find(What:="*", SearchDirection :=Excel.XlSearc hDirection.xlPr evious, SearchOrder:=Ex cel.XlSearchOrd er.xlByRows).Ro w
Album_Name = xlWrkSht.Cells( 4, 2).value
UPC = xlWrkSht.Cells( 4, 3).value
Volume = xlWrkSht.Cells( 4, 6).value
Pic_Path = Path.GetDirecto ryName(XLSFileN ame) & "\" & xlWrkSht.Cells( 4, 14).value
Album_Type = xlWrkSht.Cells( 4, 17).value
Oct 12 '07 #6
KnightHack
15 New Member
[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=Micro soft.Jet.OLEDB. 4.0; "data source= " & ExlFilename & ";" _
& "Extended Properties=Exce l 8.0;"
Dim conn As New OleDbConnection (Str_MdbConn)
If conn.State <> ConnectionState .Open Then conn.Open()

MdbCmd = New OleDbCommand(St r_MdbCmd, Mdbcon)
MdbDr = MdbCmd1.Execute Reader()
If MdbDr.HasRows Then
While MdbDr.Read
If Not Convert.IsDBNul l(MdbDr.GetValu e(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.Connecti on.Close()
Mdbcon.Close()

catch ex as exception
end try
Oct 12 '07 #7
ANNA284
2 New Member
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\Syst em32\msexcl40.d ll 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.ExecuteNonQu ery() 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
2 New Member
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
3220
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 characters. When I import it into A2K, that field is truncated. If I try to inport the file into an EXISTING table that I define myself where that field is a MEMO field, the import crashes.
1
6490
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 wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table
2
15524
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 wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table
4
3031
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 coming to a firm conclusion over whether it was the better approach, and wonder if I should do it differently the next time I'm faced with a similar issue. I needed an app to automatically import from spreadsheets with a semi-dynamic structure,...
3
3726
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 - and dump everything into a table. The problem is once I have the data imported into a new table, I can't do much with it. If I try to run an Append query and insert data from the new table into an existing table, the query fails - "Error...
10
11836
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 .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify...
2
3509
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 SubSAT and I need to get specific data from that into the database. I'm finding that if I have the workbook open on my desktop then the SubSAT % complete and the High level plan % come in fine, but as soon as the workbook is closed they come...
8
8614
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 database. The problem is that the code is on the front end and thus it imports the data/new table into the front ent. How can I accomplish an import (with the click of a button) into the back end and link it to the front end? THANKS
1
5800
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 import the data from that address into an excel worksheet. What I would like to do is use SSIS to import data from the same web site. In other words, I now use Excel's web query functionality to import data from a website with a url of xyz.asp. ...
7
12084
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
10875
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10623
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10272
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7806
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7001
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5669
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5848
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4474
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3124
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.