A Stitch in Time: Excel to XML in the Year 2014

This blog post is a reminder to me in the likely event that I’ll be in this situation again (hence “a stitch in time”), and I hope it may also prove a useful resource for others who discover themselves in my position. 

Behind the scenes of our XML-based Digital Mitford project, we’re organizing lots of information in Excel spreadsheets–info on archive holdings, bibliography data–it’s simply convenient to enter lots of data that way rather than in angle-bracket mode. We decided to use Excel for certain applications last summer only after we figured out how to “map” a spreadsheet file to XML. Having figured it out once, we’ve not needed to redo it until…today. I suddenly found a pressing need to set this up anew on my laptop, and I cursed myself today for not recording somewhere what I did months and months ago…because it was all so much more complicated than it needed to be and I didn’t want to go through all THAT again! Standard software applications shouldn’t be making their code-minded users work harder than they did 10 years ago to do the same things, and they only aggravate the problem when they don’t update their official Help documentation.

First of all, current versions of Excel (or versions 2007 and later) SEEM to offer sufficient tools to map spreadsheet data to XML, but one sooner or later figures out that the old 2003 version offered a better way to generate a schema (.xsd file) directly from the data.  

If you don’t have the Developer tab showing, you need to unveil it (or “customize the ribbon”). That’s the easy part.

Here’s what’s going to be obnoxious. If you want to generate your own schema from your data, without resorting to an external file, you’re going to wind up here, reading about how to “Create an XML Data File and XML Schema File from Worksheet Data” and sadly, it’s going to lead you to a buggy and outdated “XML Tools Add-in” patch. When you attempt to work with this old chestnut from the year 2003, you’ll generate errors (and headaches), not the schema you need. Keep the instructions open, but get yourself the revised XML Tools Add-in from Pixcels.nl: You could either follow their instructions to debug the 2003 add-in, or do yourself a big favor and just download and install their excellent debugged version of the year 2013.

Why this isn’t available to us directly from Excel’s documentation is…beyond comprehension!   


Published by

Elisa Beshero-Bondar

Associate Professor of English http://about.me/ebbondar

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s