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

I have an Excel worksheet with 4 columns:
F1 F2 F3 AutoNo
A Y C 1
G C D 2
S W A 3

I have a table in SQL Server 2000 which corresponds to the above worksheet.
What's the best way to update columns F1, F2, F3 in the table using the
AutoNo from both the table and worksheet?

Thanks for any replies using ADO/VB/SQL and not DTS.Hi

I would expect either the autonumber in the spreadsheet or the autonumber in
the database to be the master, otherwise you will probably end up with
miss-matching records.

If you use a liked server you can update/query both. If the SQL Server table
has an identity that you want to force, then SET IDENTITY_INSERT ON.

John

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3f1132e5$0$31925$afc38c87@.news.optusnet.com.a u...
> I have an Excel worksheet with 4 columns:
> F1 F2 F3 AutoNo
> A Y C 1
> G C D 2
> S W A 3
>
> I have a table in SQL Server 2000 which corresponds to the above
worksheet.
> What's the best way to update columns F1, F2, F3 in the table using the
> AutoNo from both the table and worksheet?
> Thanks for any replies using ADO/VB/SQL and not DTS.|||John

Real issue I have is how do I get data from Excel into SQL the fasted
possible way?

Thanks for your previous reply!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f12643a$0$15033$ed9e5944@.reading.news.pipex. net...
> Hi
> I would expect either the autonumber in the spreadsheet or the autonumber
in
> the database to be the master, otherwise you will probably end up with
> miss-matching records.
> If you use a liked server you can update/query both. If the SQL Server
table
> has an identity that you want to force, then SET IDENTITY_INSERT ON.
> John
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3f1132e5$0$31925$afc38c87@.news.optusnet.com.a u...
> > I have an Excel worksheet with 4 columns:
> > F1 F2 F3 AutoNo
> > A Y C 1
> > G C D 2
> > S W A 3
> > I have a table in SQL Server 2000 which corresponds to the above
> worksheet.
> > What's the best way to update columns F1, F2, F3 in the table using the
> > AutoNo from both the table and worksheet?
> > Thanks for any replies using ADO/VB/SQL and not DTS.|||I concur, a linked server gets your data in SQL fastest. From there you
just use SQL commands to join and update the tables.
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\myexcelfile.xls',
NULL,
'Excel 5.0'
GO
SELECT * FROM ExcelSource...MyNamedRange
GO

If you are working with small tables that get dumped to Excel for an
employee to update (say prices) here is one way to make the update happen
immediately. It essentially binds the spreadsheet to the server table and
uses optomistic locking.

Matthew Martin

Dim con As ADODB.Connection

Private Sub Worksheet_Activate()
Set con = New ADODB.Connection
con.Provider = "sqloledb"
con.Properties("Data Source").Value = "MARIA" ' Your server name here
con.Properties("Initial Catalog").Value = "MyDB" ' your DB name here
con.Properties("Integrated Security").Value = "SSPI"
con.Open
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' Ensure we have a row ID & column name
' AutoNum is in column 1, where primary keys should be.
If Target.Row > 1 _
And Worksheets(1).Cells(1, Target.Row).Text <> "" _
And Worksheets(1).Cells(Target.Column, 1).Text <> "" Then
If con Is Nothing Then
Worksheet_Activate
End If

con.Execute "UPDATE tblExportSQL " & _
"SET " & Worksheets(1).Cells(1, Target.Column).Text & " = '" & _
Target.Text & "' WHERE AutoNo = " & Worksheets(1).Cells(Target.Row,
1).Text

End If
End Sub

Private Sub Worksheet_Deactivate()
con.Close
Set con = Nothing
End Sub

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3f1132e5$0$31925$afc38c87@.news.optusnet.com.a u...
> I have an Excel worksheet with 4 columns:
> F1 F2 F3 AutoNo
> A Y C 1
> G C D 2
> S W A 3
>
> I have a table in SQL Server 2000 which corresponds to the above
worksheet.
> What's the best way to update columns F1, F2, F3 in the table using the
> AutoNo from both the table and worksheet?
> Thanks for any replies using ADO/VB/SQL and not DTS.
>

Excel Rendering Question

When a report is exported to Excel, each page in the report becomes a
worksheet. The worksheets are named as 'sheet 1', 'sheet 2' etc. Is there
any way to name the sheets as page headings?Hi Sanjay,
My grouping is exactly the same. However, I also have pagebreakatend set:
<Grouping Name="list1_Country_Name">
<GroupExpressions>
<GroupExpression>=Fields!CountryName.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
A.
"Sanjay" wrote:
> I have the following grouping element in my report:
> <Grouping Name="list2_Details_Group">
> <GroupExpressions>
> <GroupExpression>=Fields!customer_name.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> Excel is rendered as each customer in a different sheet but I also need the
> sheet name to be =Fields!customer_name.Value.
> Andrew,
> Can you post the rdl extract where your groupings are named?
> Thanks
> Sanjay
> "Andrew Byrne" wrote:
> > I have seen this whenever I export to Excel and have my report grouped. For
> > example, if I group by country then excel will render with each sheet=a
> > country. In my case the country names were written to the Sheet Name, so
> > maybe you just need to name your groupings in the report?
> >
> > "Sanjay" wrote:
> >
> > > When a report is exported to Excel, each page in the report becomes a
> > > worksheet. The worksheets are named as 'sheet 1', 'sheet 2' etc. Is there
> > > any way to name the sheets as page headings?

Excel problem

I am trying to import data from sql server to excel.

it creates a new worksheet with name 'mytable' and excel file also has 3 sheets (by default as well).

when package is executed, Data gets transferred first time.

When I try to execute package again it gives me an error - that Table 'mytable' already exists.

To solve this I added another task before it creates the table ('mytable' sheet in excel), where I drop this table with the statement " DROP TABLE 'mytable' " (Connectiontype is EXCEL)

it works now, but I need to have this table 'mytable' in the excel, when ever I need to execute the package.

Is there any statement like in sql where I can check whether the table exists or not - like " IF EXISTS( select * from sysobjects where name = 'mytable')
I need to check this in Excel.

also, is there a way to drop other 3 sheets in excel, which comes by default.

Thanks in advance.

Management Studio export wizard after selecting source and destination.In specify table copy or query pane select query write query click next and click mapping button at select dource tables and views pane.Configure destination excel sheet.

bye.

|||

The Excel driver respects the saved Excel setting for the default number of sheets in a new workbook. One way to avoid this is to create an empty "template" workbook configured as you want, and use a File System Task to make a copy of that template each time that you want to perform your export.

Or, you could use Execute SQL Tasks to repeat both the DROP and the CREATE each time, and configure error settings such that, if the DROP fails (because the table does not exist), the package continues.

-Doug

|||I was able to do it by the first method, by creating an template workbook.

I tried to work by second method by checking whether table (worksheet) exists in excel or not, but couldn't get it through due to syntax error. for excel database connection, I didn't get it through the corrent syntax.|||

IF EXISTS certainly won't work with Excel.

If you need to check for existing tables, the System.Data.OleDb namespace has some methods to return schema information that Jet/Excel may support. (I haven't yet tried it myself.) You could connect in a Script Task, check schema information, and set a package variable value to indicate to downstream tasks whether the intended destination table exists or not.

-Doug

Excel Import Identity Error

I am trying to import from an Excel Worksheet in the Enterprise Manager. I run through the wizard, and in transformations I have the Enable Identity Insert box checked, but when I run the import I get the following error:
Error at Destination for Row number 13880. Errors so far in this task: 1.
The statement has been terminated.
Cannot insert the value NULL into column 'ID', table 'CCReport.ITPSG.CCSPEND'; column does not allow nulls.
INSERT fails.

There are only 13881 rows, and I don't see anything in 13880 that is any different than the ones before it. Any ideas?try importing into the database first with out enabling the identity column, once it's imported then you can debug the data as well as re-populate the original table by doing any data manupulation.
" insert into originaltable
select columns from importedRawDatatable"

Excel headers - using SimplePageHeaders deviceinfo setting

I want to try out placing the report header in the Excel page header instead of the worksheet. I have seen forum references to updating the config files to switch on SimplePageHeaders deviceinfo setting, but I can find no reference to this in the documentation and it appears to be ignored when I try it out. Is this in RS 2005 only? (I'm using 2000 SP2).

Would this get round my current problem of having lots of additional columns/merged cells to accommodate all the header layout when exported to Excel? Ideally I want the data structure in Excel to be a straight forward matrix of the data values in the report table which isn't feasible when exported to Excel with our default headers which contains a lot of labels containing context information.

I am pretty sure this is new in 2005. and yes, it would help you get past your merged cell problem.

Excel headers - using SimplePageHeaders deviceinfo setting

I want to try out placing the report header in the Excel page header instead of the worksheet. I have seen forum references to updating the config files to switch on SimplePageHeaders deviceinfo setting, but I can find no reference to this in the documentation and it appears to be ignored when I try it out. Is this in RS 2005 only? (I'm using 2000 SP2).

Would this get round my current problem of having lots of additional columns/merged cells to accommodate all the header layout when exported to Excel? Ideally I want the data structure in Excel to be a straight forward matrix of the data values in the report table which isn't feasible when exported to Excel with our default headers which contains a lot of labels containing context information.

I am pretty sure this is new in 2005. and yes, it would help you get past your merged cell problem.