— Forest and the Trees

Excel to XML

I had to convert an Excel file to XML so that I could load the data into Flash. I’m always excited when I get to use Microsoft Office – so this was a real treat. Actually, it’s not too bad – but, there was one major headache.

You need Excel 2003 for this to work. (I’m using Windows)

Excel 2003 has a couple ways to export XML. You can use “Save As -> XML Spreadsheet”. But, that gives you a ridiculous file with lots more info that you want (formatting info, etc.). The better way to do it is to write an xsd (xml schema) and load that into the spreadsheet. Here’s a sample xsd defining a repeating node with a bunch of attributes.

So, save that as sample.xsd. Then open your Excel spreadsheet. Choose “Data ->XML ->XML Source”. That opens the XML Source window. Click the button “XML Maps”. Then “Add”. Then load your schema (for some reason Excel calls xsds XML Maps).

In the XML Source window, your attribute names will show up. You can drag them onto the column names of your spreadsheet to map them. After mapping, choose “Save As” choose type “XML Data”. And voila, an XML file formatted to the xsd.

Here’s the rub – and the reason you need the “dontNeed” attributes (in the xsd above). For Excel files where the columns are not of equal length or have some empty cells, the XML file will not export if you do not map the columns in sequence from left to right. (I hope there is another way around this as well) So, start at the left and map every one of the columns, until you reach the last column you need to export. Then, “right click->remove element” the attributes in the XML Source window that you don’t need. Yes this is a kluge. But it does seem to work.

The error you get if you don’t do this is “A mapped element’s relationship with other elements cannot be preserved.” That’s a really helpful message.

20 comments
  1. Kaijin says: October 4, 20061:40 pm

    Thanks very much for this tip! I was working on this for a couple hours and kept getting that “relationship preservation” error and couldn’t figure it out. Be nice if excel actually worked the expected way the first time. Thanks!

  2. Jerry Golley says: January 24, 200712:48 pm

    Yours is the only link I found regarding this error and while it was very good information, it has not solved my problem. I am unable to remove the elements that are not required and unable to get rid of the “relationship” error. I’ve trimmed my files down to the bare minimum and cannot get it to work. Any suggestions?

  3. Jim Doria says: January 31, 200711:53 am

    I’ve been getting this same error, although I’m using a simple XML file rather than an XSD as my map in Excel, following the advice in this tutorial: http://www.codeproject.com/soap/ExcelListToXml.asp.

    Even though I mapped my columns (all three of them!) from left to right, I still get the error.

    I’m using child elements instead of attributes, because I thought they were cleaner and easier to work with. Why did you choose to use attributes for your data?

  4. Doug says: January 31, 20071:04 pm

    Jim,
    I use attributes for variable values (strings, numbers, dates) and nodes for data structures that have children (like objects and arrays). Just makes more sense to me that way. Additionally, I use XML in Flash and nodes parse slower than attributes in Flash.
    –d

  5. Jim Doria says: January 31, 20076:40 pm

    Thanks Doug.

    It turned out I was working too hard on this and had psyched myself out. It’s really pretty easy, but if you do it the “non-easy” way you get hung out to dry.

    First, I’m not sure you actually need a schema. I simply created a simple XML file with the structure I wanted and Excel could use it. You do need to indicate duplicating rows, as mentioned in the tutorial link. Schema gives you more control of course, but for those unfamiliar with creating them it’s good to know that you don’t strictly need it.

    Once I created the xml “template” (for lack of a better term) and imported into Excel as an XML map, I had to assign the XML elements to the columns in my spreadsheet. This is where I went wrong. I dragged individual sub-elements onto individual columns, as described in the tutorial, which produced the “preserve relationships” error. Wnat Excel expected me to do was to drag the high-level node containing the sub-elements that corresponded to my columns, and drop it on the first column in the sheet. Excel then automatically assigned the names of the sub-elements to all the consecutive columns following the first one, and the data exported with no problem.

    Didn’t matter whether I used attributes or child elements in my template file. Both worked great!

  6. Doug says: February 1, 20071:28 pm

    Thanks Jim.

  7. Deepa says: April 4, 20073:08 am

    Although this post is helpful, my problem is a bit different. I’m unable to delete an existing Map. I’ve changed some fields on the XML and now require the new structure to be reflected, but its not allowing to delete the earlier XML Map. I’ve tried going to XML Maps and then selecting the Map Name and Delete. It does not even give any error.

  8. Doug says: April 4, 20077:00 am

    Sorry Deepa. I have no experience deleting maps. Maybe doing a saveAs or exporting the spreadsheet (then re-importing) will clear the map. Then make a new map? Just guessing w/o doing any testing.

  9. Becca says: April 23, 20077:40 am

    Is there any way to include xml comments and customised Doctype tags in the outputted exported xml document by defining them in the xml ‘template’/schema (the xml file that defines the structure you want for output). As, at the moment I can create xml files from the spreadsheet using my xml template map, however I want to include some xml comments and want these to be added to the output xml file automatically.

  10. Sam says: August 30, 20072:54 pm

    hei, i need to convert xml to excel. Do u know how can I do that. my xml file is on a server. i need to pull out the xml by name of the file and save it as excel in the desktop.

  11. Doug says: August 31, 20077:07 am

    I just did a quick test and Excel does a pretty good job of importing XML files. Just save the file as an xml file locally, and open in Excel. I’m using Excel 2003

  12. todd says: August 31, 20072:09 pm

    I too am getting error “the mapped element’s relationship with other elements cannot be preserved.” The message is vague and I don’t know what the problem is. I dragged the high level node to the first column, and Excel mapped all subsequent columns. When I went to export as XML I got the dreaded message “the mapped element’s relationship with other elements cannot be preserved.” I’ve been working on this all day and am going insane.

    The Excel workbook I’m trying to convert to XML has over 30 columns, with many of those columns containing empty cells. I’m using an XML “template” so Excel infers the schema. I don’t have experience XSD and am wondering if I may have to make my own schema.

  13. Bill Gilliss says: October 8, 20071:43 pm

    Thank you SO!!! much for clearing this up. A permanent bookmark here in case I forget.

    It should be a lot easier to create an XSD from an existing spreadsheet with column names, but yours works until that day comes.

    Thanks again.

  14. Terry says: November 15, 20075:58 am

    Thank you, this post was very helpful to me.

  15. Yasovardhan says: November 29, 20074:21 am

    I am also facing the same problem. My excel has some values as empty. I tried the solution, but it is not working. Can you please suggest me some solution.

  16. Erhan says: December 8, 200910:09 am

    Hi everyone,

    I am having the same error as well :’A mapped element’s relationship with other elements cannot be preserved.’ Can anyone help me on this urgent case? I will be really appreciated.

  17. Chidamabram says: April 23, 20102:40 pm

    Download an addin OfficeExcel2003XMLToolsAddin.exe; generate the schema again; then save as xml

  18. Cris says: September 12, 20106:52 pm

    If you get any messages such as “Denormalized records” or “the xml map corresponding to the active cell is not exportable” or “the active cell cannot be exported” – try saving your file to CSV first.

    This was a major problem I had when exporting – even in 2007. It’s things like this that make me realise that MS are pretty poor when it comes to making robust software. The money is what gets things done well enough to retail.

  19. Derrick says: November 20, 201010:06 pm
  20. Greg says: November 18, 20114:20 pm

    I swear there is another hobgoblin in here somewhere but definitely drag each XML element/attribute individually to each column — The order does NOT matter but trying to drag the top level element onto the sheet just plain doesn’t work.

Submit comment