Where do I start?

[ferretwoman]

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? I've looked for tools
that would help me out, but I haven't found anything that will allow
me to grab scattered information and plug it in so the program could
spit out xml. I really don't want to have to create a program to do
this if I can find one that meets my needs.

Any Ideas?

Thanks!

Linda

 

[Joe Kesselman]

Get the data out of the spreadsheets (for example, save as a CSV or TSV
file, if Excel can't save directly as XML -- I don't use Excel much and
haven't used a recent version at all, so I don't know whether it has an
XML export feature).

Then write an application that reads that simply-formatted text file and
builds your intended XML document based upon the information it
retrieves. If you *were* able to export as XSL (or if you want to start
by rewriting it into XML form), you can probably generate the final
document using an XSLT transformation; the other approach would be write
your own code, probably using the standard XML APIs (DOM or SAX,
depending on the nature of what you're doing) so you don't have to
reinvent too many wheels.

I'd suggest you start by reading some good getting-started-with-XML
tutorials, which will show you some of the tools available and give you
a grounding in the concepts you're going to need. Standard
recommendation: http://www.ibm.com/xml

 

[Andy Dingley]

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.

 

[ferretwoman]

I pulled the information I needed into an Access database, then wrote
a small program to loop through all the items inserting the correct
information between tags, and output to a file. Just waiting now to
find out if it's properly formatted for the program that will be
reading the xml file.

Thanks for the suggestions - this was actually easier than I
originally thought it would be.

Linda