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.

June 28, 2006 | Filed Under Code, Data, XML 

Comments

15 Responses to “Excel to XML”

  1. Kaijin on October 4th, 2006 1: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 on January 24th, 2007 12: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 on January 31st, 2007 11: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 on January 31st, 2007 1: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 on January 31st, 2007 6: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 on February 1st, 2007 1:28 pm

    Thanks Jim.

  7. Deepa on April 4th, 2007 3: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 on April 4th, 2007 7: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 on April 23rd, 2007 7: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 on August 30th, 2007 2: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 on August 31st, 2007 7: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 on August 31st, 2007 2: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 on October 8th, 2007 1: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 on November 15th, 2007 5:58 am

    Thank you, this post was very helpful to me.

  15. Yasovardhan on November 29th, 2007 4: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.

Leave a Reply