471,354 Members | 1,549 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,354 software developers and data experts.

Export DataSet to XML for MS Excel?

I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info.

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;
}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities! :)
Aug 25 '08 #1
4 9445
jp2msft wrote:
I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info.

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;
}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities! :)
I am not sure why you iterate over the tables to write out the DataSet.
DataSet has its own WriteXml method and also a WriteXmlSchema method.
And if you use
ds.WriteXml("file.xml");
then you should be able to read that in with
ds.ReadXml("file.xml");
too.
If the schema does make problems with Excel then you could write it out
as a separate document with
ds.WriteXmlSchema("schema.xsd");
ds.WriteXml("file.xml");
and read it back in with e.g.
ds.ReadXmlSchema("schema.xsd");
ds.ReadXml("file.xml");

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Aug 25 '08 #2
On Aug 25, 12:52*pm, jp2msft <jp2m...@discussions.microsoft.com>
wrote:
I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info..

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)){
* foreach (DataTable table in ds.Tables) {
* * table.WriteXml(xw, XmlWriteMode.WriteSchema);
* * //table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
* }

}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
* dgv.DataSource = table.DefaultView;

}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities! :)
Hi,

Have you tried to export it to CSV instead?
CSV is readable by Excel (most of the cases Excel is teh default
program for .CSV files)
Aug 25 '08 #3

Here is a food for thought post:
http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!148.entry

The xml generated by the WriteXml method...can be transformed to anything
you like.

.......

"jp2msft" <jp*****@discussions.microsoft.comwrote in message
news:CC**********************************@microsof t.com...
I've got a DataSet that I save as XML using the DataSet DataTable's
WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data:
id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad,
my
data is there, bunched between a lot of this difficult to read other info.

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;
}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities! :)

Aug 25 '08 #4
A CSV option is included in the SaveAs Dialog Box for the Clients to select
from.

I'm just trying to figure out a little of this XML stuff. :)

"Ignacio Machin ( .NET/ C# MVP )" wrote:
On Aug 25, 12:52 pm, jp2msft <jp2m...@discussions.microsoft.com>
wrote:
I've got a DataSet that I save as XML using the DataSet DataTable's WriteXml
method.

If I say XmlWriteMode.IgnoreSchema, it shows up great in Excel, but I can
not reopen the file in my application because there is no schema for it.

On the other hand, I can choose XmlWriteMode.WriteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet, ns1:IsDataSet=TRUE, etc. If I open this file in Notepad, my
data is there, bunched between a lot of this difficult to read other info..

Is there some common ground that I am missing?

I write the XML file using this:

using (XmlTextWriter xw = new XmlTextWriter(m_filename, Encoding.UTF8)) {
foreach (DataTable table in ds.Tables) {
table.WriteXml(xw, XmlWriteMode.WriteSchema);
//table.WriteXml(xw, XmlWriteMode.IgnoreSchema);
}

}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(openFileDlg1.FileName, XmlReadMode.ReadSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultView;

}

There is typically only one (1) table in the view, but my use of foreach
prevents errors while reminding me of the possibilities! :)

Hi,

Have you tried to export it to CSV instead?
CSV is readable by Excel (most of the cases Excel is teh default
program for .CSV files)
Aug 25 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Hans [DiaGraphIT] | last post: by
1 post views Thread by Kevin Blakeley | last post: by
4 posts views Thread by John Z. | last post: by
3 posts views Thread by Sachin Salgarkar | last post: by
reply views Thread by XIAOLAOHU | last post: by

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.