Excel and XML - mapping data?

[drizzt81]

Hello,

I have a program that generates a lot of data (statistics from my simulation) and since it is multi-dimensional data, I felt that an XML file would be a smart way to store it (and somewhat portable). I particular a single XML file stores multiple Statistics for each run. the value is stored for each step inside of a run, giving this type of XML source:

<STATISTIC Name="Token moves">
	<RUN INDEX="0">
		<DATA INDEX="0" TYPE="INT">20</DATA>
		<DATA INDEX="1" TYPE="INT">25</DATA>
		<DATA INDEX="2" TYPE="INT">25</DATA>
		<DATA INDEX="3" TYPE="INT">25</DATA>
	</RUN>
	<RUN INDEX="1">
		<DATA INDEX="0" TYPE="INT">20</DATA>
		<DATA INDEX="1" TYPE="INT">20</DATA>
		<DATA INDEX="2" TYPE="INT">20</DATA>
	</RUN>
</STATISTIC>

I would like to import this data into excel with each Statistic getting a single spreadsheet, runs across and steps down.

Is this possible, if so, how?

[CoffeeFiend]

XML's a good format for many things. You're faced with a few options:

-excel does support XML (well, 2003 does at least, under file>open, the combobox underneath has "XML file" in it)

-perhaps whatever program that saves to XML can also export a excel-friendly format like CSV too

-you can use some type of XML editing app to transform it...

-you can write a program to parse the XML and write a csv file using the data

-you can use VBA inside excel or VSTO to parse the XML and fill cells

Excel's XML support is better than nothing, but it won't do overly complex things. Thankfully it's not very hard to program or script a little parser (providing your know the basics about programming and XML), which is likely what you'll have to do.

[drizzt81]

XML's a good format for many things. You're faced with a few options:

-excel does support XML (well, 2003 does at least, under file>open, the combobox underneath has "XML file" in it)

I have already tried that. While i have yet to write an XML schema to go along with my data, I don't think that excel's XML support allows a single XML file to be translated into multiple worksheets, which is what I would like to do.

-perhaps whatever program that saves to XML can also export a excel-friendly format like CSV too

Since I wrote that program, I could do that. However, I think that the same problem applies that the XML file is causing: Excel interprets a single CSV file as a single worksheet. Since my data has more than 2 dimensions (Statistic Name, Run, Step) and I have tens of runs with hundreds of steps, dropping it all in a single worksheet would make working with data pretty hard.

-you can use VBA inside excel or VSTO to parse the XML and fill cells

I had not thought of that idea. I will look into it, unfortunately my VBA skills are close to zero.

Excel's XML support is better than nothing, but it won't do overly complex things. Thankfully it's not very hard to program or script a little parser (providing your know the basics about programming and XML), which is likely what you'll have to do.

thanks for your ideas, I appreciate your input.

Greg

Edited September 12, 2006 by drizzt81

[CoffeeFiend]

Yeah, I know it won't fill data on different worksheets by itself. That's basically what I meant by "Excel's XML support is better than nothing, but it won't do overly complex things." It'll import some simple XML, but that's basically it.

As for VBA/VSTO, it seems like the only 2 options if you really need stuff on multiple worksheets. VBA sucks (poor, old and crusty VB-ish scripting language that sucks badly), but it's a bit simpler than VSTO (and VSTO needs Visual Studio - not sure if you have/use it, and they're not exactly free either...)