Import XML Excel into dataset

[postings]

Hi - can you help please?

Please review the following straightforward code which works fine with
ordinary XML files.

----------------------------------------------------------------
Dim originalxmlsource As New DataSet
Dim xmlfilename As String = Request.PhysicalApplicationPath _ &
"myfilename.xml"
MyDataGrid.DataSource = originalxmlsource
MyDataGrid.DataBind()
----------------------------------------------------------------

In actuality what I really need to do is import an Excel XML file,
which was originally generated by Office Web Components (Excel 2002).
OWC would only allow me to Export in Excel XML, rather than native
Excel format.

If I try to use the above code using Excel XML I get a "Specified cast
is not valid" error.

So my question is how can I neatly import Excel XML into a dataset?

Many thanks!

Alex

 

[Guest]

Have you looked at the DataSet.ReadXml() method?

 

[postings]

Opps sorry I didn't post the code properly (doh!), yes it includes the
readxml method, but it won't read Excel XML.
Revised code here - thanks Alex

----------------------------------------------------------------
Dim originalxmlsource As New DataSet
Dim xmlfilename As String = Request.PhysicalApplicationPath _ &
"myfilename.xml"
originalxmlsource.ReadXml(xmlfilename)
MyDataGrid.DataSource = originalxmlsource
MyDataGrid.DataBind()
----------------------------------------------------------------

 

[Guest]

which line, specifically, is getting the cast error?
Is they XML file a valid XML file?

 

[postings]

Hi Curt

This line gets "Specified cast is not valid":
originalxmlsource.ReadXml(xmlfilename)

But if only happens if I use an Excel 2002 XML spreadsheet, that's what
I want to import into a dataset.
Plain XML works fine.

Thanks

Alex

 

[Guest]

You may have to do some manual cleanup on the XML then.. sounds like it's not
validating correctly as proper XML.

 

[vMike]

Hi - can you help please?

Please review the following straightforward code which works fine with
ordinary XML files.

----------------------------------------------------------------
Dim originalxmlsource As New DataSet
Dim xmlfilename As String = Request.PhysicalApplicationPath _ &
"myfilename.xml"
MyDataGrid.DataSource = originalxmlsource
MyDataGrid.DataBind()

I think you may need 2003 version for exporting xml in a readable format as
2002 seems to export all of the excel spreadsheet formats etc. There may be
someone out there that has a customize file output writer to write the
desire xml format. A workaround may be to link you excel spreadsheet to
MSAcess and use the access export for xml (DataOnly). Hope this helps

 

[postings]

Thank vMike

I think you may need 2003 version for exporting xml in a readable format as
2002 seems to export all of the excel spreadsheet formats etc.

I was wondering if there was a way of transforming the Excel 2002 XML
file itself (i.e. strip out the garbage) before it got read in....

Hmmm....

Cheers

Alex