473,372 Members | 1,003 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,372 software developers and data experts.

Doubt in " HOW TO: Query and Display Excel Data by Using ASP.NET, ADO.NET, and Visual C# .NET"

RK
Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/default...en-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK.
Jul 20 '05 #1
4 11288

"RK" <ra*******@yahoo.com> wrote in message
news:22*************************@posting.google.co m...
Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/default...en-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK.


The article doesn't mention SQL Server anywhere, and it sounds like your
problem is how to automate something in Excel, so I guess you'll get a
better answer in an Excel or C# newsgroup.

Simon
Jul 20 '05 #2
RK (ra*******@yahoo.com) writes:
Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?


Of course there is.

I have not tried to get data from Excel with SELECT statements, but
would be surprised if "SELECT * FROM Sheet1" would work. I've mainly
use the OLE interface to get data from Excel in Perl. While I understand
that you don't want users to have to highlight things, I fail to see
but that you need to have some pre-defined structure that the Excel
file must have, or else you will have no idea of what you are importing.

But while you said SQL Server twice in your posting (look again Simon! :-),
your question seems more to be related to Excel, so you should find a
group about Excel.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
You might look at the capabilities of SQL Server DTS Packages. They can
read from Excel spreadsheets. However, your datasheet would have to be
massaged enough that the data was a fairly straightforward import.

I suppose if I were you, I'd use a combination of VBA code to hack up the
spreadsheet, save it to a convenient spreadsheet, then use SQL-DMO to
trigger a DTS package for the import.

Otherwise, you could just do a VBA module that would pass in the interesting
data directly over an ADO, OLEDB or ODBC connection. We've done something
like that, it wasn't terribly difficult.

"RK" <ra*******@yahoo.com> wrote in message
news:22*************************@posting.google.co m...
Hi,

In my application, I need to copy data from an Excel file into a SQL
table. The article related to this can be found at

http://support.microsoft.com/default...en-us%3B306572

Using this,I am first extracting data from given excel file into a
temporary DataTable. After making some operations on that DataTable
(like splitting one column into two), I am saving the data into actual
table in SQL Server.

My doubt is that, in the above given link, there are few steps needed
to do on the excel file... eg.
--> Highlight the rows and columns where the data resides.
--> On the Insert menu, point to Name, and then click Define.
--> In the Names in workbook text box, type myRange1, and then click
OK.

I don't want my client to do thsese operations everytime he changes
contents in that excel file, as this excel file changes almost daily.
The extracting of data from excel file is done after clicking UPDATE
button on webpage. The person saving data into Excel file is differnet
from the one who updates it into SQL.

Is there any other way where we can directly copy data from excel file
into DataTable without doing the above three steps?

I am using Visual Studio.NET 2003,SQL Server 2000 and C# as developing
language for this web-based project.

Any advice would be greatly appreciated. Thanks in advance.

Regards,
RK.

Jul 20 '05 #4
Hi,

There are a few alternatives. I recommend you to use SQL*XL an addin I
wrote for Excel to do these things. There are some other alternatives
which I will mention.

1. SQL*XL - addin for Excel to access your databases.
SQL*XL is an addin for Excel that allows you to access your database
directly from Excel. You can query data into Excel or you can pump
data from Excel into the database. SQL*XL focusses on making it easy
for the end user but it can also be used to make scripts as it is
fully compatible with Excel macro recording and VBA.

Have a look at SQL*XL at: www.oraxcel.com

2. Define your Excel workbook as a data source in ODBC
If you do this you can issue select statements against it. You can use
it in an openquery statement in SQL server or even make it a linked
server I suppose. Named ranges will appear as tables. Reserve row 1
for the column headers.
Problems with this technique: it is readonly, you may have problems
with accessing the workbook (in use)
The nice thing is that you can use it directly in SQL or DTS

3. You can write a little macro in Excel that dumps the file out in a
nominated format (CSV e.g) and a nominated directory. You could have
DTS to pickup the file and import it in your database table.

Hope this helps,

Gerrit-Jan Linker
Linker IT Consulting Limited
www.oraxcel.com
Author of SQL*XL
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: jrasmussen | last post by:
I have created an Excel spreadsheet for the web. When it opens in IE 6.0 I get the message that the format is not supported. This is because it is in tab delimited format. Ane when I save the file,...
3
by: John | last post by:
Is there a way to code the button that's available in the query window--microsoft excel icon that exports to excel. I know transferspreadsheet will do this---but I want the query, which is in a...
8
by: Jerry | last post by:
I have an off-the-shelf app that uses an Access database as its backend. One of the tables contains a field with an "OLE Object" datatype. I'm writing some reports against this database, and I...
1
by: Benny Ng | last post by:
Hi,All, Export Method: ------------------------------------------------------------------------- strFileNameExport = "Results" Response.Clear() Response.Buffer = True...
2
by: CVerma | last post by:
I'm using an html input control (System.web.UI.HTMLControls.HTMLInputFile) to upload files such as msword, excel, jpg, and pdf. I have the encType property set in the form:...
3
by: Michael Tkachev | last post by:
Hello, I need to create an "Excel" file in the ASP.net. But a security system doesn't allow to do it. What can I do? There is an exception heppens when I try to execute the following row: ...
0
by: Microsoft NewsGroups | last post by:
I am creating a TAB delimited file with .XLS extention using the Response.ContentType = "application/ms-excel" method. Normally I would place this on it's own form and delete all the HTML in the...
2
by: steve02a | last post by:
I'm using the Response.ContentType = "application/vnd.ms-excel" in my web application to export data into a spreadsheet. That works fine and all. My question is: When I export the data and go...
4
by: ShadowLocke | last post by:
Hi, Im not very experienced with XSL, this is my first run in with it. I found an XSL file that converts a dataset to an excel readable format. The problem I have is when the dataset has a...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.