Export DataSet to XML for MS Excel?

[=?Utf-8?B?anAybXNmdA==?=]

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! :)

[Martin Honnen]

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/

[Ignacio Machin ( .NET/ C# MVP )]

On Aug 25, 12:52*pm, jp2msft <[e-mail]>
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)

[sloan]

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! :)

[=?Utf-8?B?anAybXNmdA==?=]

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 <[e-mail]>
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)