Phil, our intrepid editor/Office System handyman was tasked with moving a large amount of data in a flat spreadsheet into an XML document. The catch was, he had to do it quickly, he doesn’t really know XML Schema well enough to write one and there was way too much data to simply retype, even with copious cutting and pasting.
Phil, ingeniously, took advantage of both InfoPath and Excel’s XML functionality to get this done with enough time to get to the new Irish pub down the street (J.J. Mahoney’s) for our gaming session!
Here’s what he did.
a) Look at all the data in the raw spreadsheet and groan.
b) Realize that Excel will allow you to import an XML schema and map it over existing data in the document.
c) Load InfoPath, design a very simple form with a bunch of textboxes that mirrors the structure of the data in the spreadsheet.
d) Within InfoPath, click File, Extract Form Files
e) Go to disk and find the InfoPath form’s schema
f) Load Excel and import the schema
g) Map the schema over the data in Excel
h) Click Data, XML, Export!