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.Ig noreSchema, 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.Wr iteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet , ns1:IsDataSet=T RUE, 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.Wr iteSchema);
//table.WriteXml( xw, XmlWriteMode.Ig noreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(open FileDlg1.FileNa me, XmlReadMode.Rea dSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultVi ew;
}

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.Ig noreSchema, 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.Wr iteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet , ns1:IsDataSet=T RUE, 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.Wr iteSchema);
//table.WriteXml( xw, XmlWriteMode.Ig noreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(open FileDlg1.FileNa me, XmlReadMode.Rea dSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultVi ew;
}

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("fi le.xml");
then you should be able to read that in with
ds.ReadXml("fil e.xml");
too.
If the schema does make problems with Excel then you could write it out
as a separate document with
ds.WriteXmlSche ma("schema.xsd" );
ds.WriteXml("fi le.xml");
and read it back in with e.g.
ds.ReadXmlSchem a("schema.xsd") ;
ds.ReadXml("fil e.xml");

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

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

On Aug 25, 12:52*pm, jp2msft <jp2m...@discus sions.microsoft .com>
wrote:

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

If I say XmlWriteMode.Ig noreSchema, 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.Wr iteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet , ns1:IsDataSet=T RUE, 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.Wr iteSchema);
* * //table.WriteXml( xw, XmlWriteMode.Ig noreSchema);
* }

}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(open FileDlg1.FileNa me, XmlReadMode.Rea dSchema);
foreach (DataTable table in ds.Tables) {
* dgv.DataSource = table.DefaultVi ew;

}

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.space s.live.com/blog/cns!A68482B9628 A842A!148.entry

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

.......

"jp2msft" <jp*****@discus sions.microsoft .comwrote in message
news:CC******** *************** ***********@mic rosoft.com...

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

If I say XmlWriteMode.Ig noreSchema, 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.Wr iteSchema, and the data in
Excel looks more like some kind of code instead of the data:
id=NewDataSet,
name=NewDataSet , ns1:IsDataSet=T RUE, 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.Wr iteSchema);
//table.WriteXml( xw, XmlWriteMode.Ig noreSchema);
}
}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(open FileDlg1.FileNa me, XmlReadMode.Rea dSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultVi ew;
}

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 <jp2m...@discus sions.microsoft .com>
wrote:

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

If I say XmlWriteMode.Ig noreSchema, 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.Wr iteSchema, and the data in
Excel looks more like some kind of code instead of the data: id=NewDataSet,
name=NewDataSet , ns1:IsDataSet=T RUE, 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.Wr iteSchema);
//table.WriteXml( xw, XmlWriteMode.Ig noreSchema);
}

}

I read it back using this:

DataSet ds = new DataSet();
ds.ReadXml(open FileDlg1.FileNa me, XmlReadMode.Rea dSchema);
foreach (DataTable table in ds.Tables) {
dgv.DataSource = table.DefaultVi ew;

}

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)