On 18 May, 19:07, ferretwoman <ferretwoma...@yahoo.comwrote:
I've been given an .xsd schema file and a few spreadsheets, and have
been asked to create an xml file for thousands of rows of data from
those spreadsheets. The xsd schema cannot use Excel to create
the file due to some complex looping involved.
Where do I start in creating this xml file?
A lot depends on the "shape" of your data. Excel is a fairly simple 3-
dimensional rectilinear grid of cells, with each cell having a lot of
potential complexity. XML is an n-level hierarchical tree, usually of
simple string values (although you can data type things a little).
Mapping the simplest examples of each onto each other is easy enough,
the problems usually arise because a "table of cells" in Excel is
expected to generate a particular number of tree levels in XML, in a
manner that's application-dependent, not mechanically defined in Excel
and not defined by the XML Schema either. This requires either:
* hand-written code to loop around in Excel and generate appropriate
hierarchies in the XML,
or
* rather generic behaviour in the code that sniffs the data for
repeated row values and assumes the hierarchy from this
* Some extra markup in the XML Schema (additional to XML Schema
itself) that describes the column mappings.
As a pragmatic solution to your problems today, I'd write some Excel
VBA code that uses the M$oft MSXML XML DOM (easy enough to do from
within Excel and you should find plenty of example code out there).
I _wouldn't_ follow Joe's recommendation to dump out "Excel shaped"
data into another format, then process it further from there. That's a
recommendation that makes sense if you have an alternative format
available to you where you already have better tools you're more
familiar with. If however you're starting completely from scratch,
Excel isn't a bad place to be working with it anyway.