Excel to XML to Dataset to SQL - As HTML

Main goal is to parse an Excel Worksheet and save it as a HTML table to SQL.

I have a web form (aspx) where a use is able to browse and upload an excel documnt. I then select the data from the worksheet and use a OleDBDataAdapter to build a Dataset from the Excel data. I fill the dataset with the data from the Excel document.

What I need to do from this point has me lost. I need to re-engineer the excel data into a HTML table to display within a orgianl asp code. I was planning to build the html table...save it as a large string to SQL database table. And then from asp page - read the table and re-display it.

Im not sure how to do this...anyone with experinece, please help.

Other things I have tried. After I have the excel data saved in the dataset...I can read it via Readxml and display it within the asp.net code. So, I dont knwo if it is possible to save this dataset information as HTML to a string...?

Anyone...please|||

Why not save the Excel as XML instead of HTML to SQL database? As I know you can display XML document in HTML form by using XSLT. So I prefer to save the XML documents as BLOB data into SQL database. You can push the XML data as binary array into SQL Server.

If you're using SQL2005, you can load XML files (actually all BLOB data, including images) into SQL database by using simply OPENROWSET with SINGLE_BLOB option. You can take a look at:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5xml.asp?frame=true

Excel to Sql Server

Hi

I am trying to upload a excel spreadsheet using a web application application into a sql server database.

Basically I'm tryign to code a a upload button, that takes the excel spreadsheet and inserts it into a table in the database.

I have been lookign for code examples but cannot find out, and I am really struggling...any help would be greatly appreciated. I am trying to do this using asp.net (vb).

ThanksI'm not an app developer but this may help (or might not :))

http://www.sqldev.net/dts/DotNETCookBook.htm

Excel To DataBase (Duplicate Recoards)

for example i exported student table with sno as (PK)

how to handle the upload twise (som of the records already in the data base )

1) How to ignore the existing records in the database

2) How to modify the records which is already exist in the database

1) EXCEL DATA SOURCE(STUDENT.XLS)

2) DATA CONVERSION

3) DESTINATION DATABASE (SQL SERVER 2005 STUDENT TABLE)

this is i have please tell me how to ignore/update the exisitng records

koti

It may be a good idea to load the excel data into a staging table first. This would give you a lot more flexibility than trying to run sql statements on the excel file.

To ignore duplicate records, you could then just do a look up from the staging table to the destination table and only load the rows that fail the lookup.

To update the records is a bit more tricky, as you have to use the OLE DB Command and write the SQL statement yourself.

Hope this helps,

Chris

|||

from the 1st page of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1

EXCEL -SL SERVER 2005

Hi friends

student table contain the 3 columns : SNO SNAME MARKS

by using these controles i can able to upload the records (which is not exist in the database)

Excel Source 1 --student.xls

Data Conversion 1 --for destination datatype convertion

Fuzzy Lookupmap with database student table with sno inner join

Conditional Split (if simularity =1 then ignored the record) else inserted the database

OLE DB Destination save the new records

+++++++++++++++++++++++++++++++++++NOW ++++++++++++++++++++++++++++++++++++

in student.xls containt 7 records (1-7)

in student table(server) containt 7 records (1-7)

but marks is diffrent from excel sheet NOW

i want to update the marks ,field only (may be tomorow more than 1 column i have to update)

== SIMULTANIOUSLY how to insert a new record and EXISTING RECORD update only marks ========

REGRADS

KOTI

Basically you want to update the row if it already exists in the target table. If that is the case, then this thread will help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1