September 26, 2023

Binary Blogger

Are you a 1 or a 0? News, Thoughts and Reviews

How To: Excel 2010 XML Exports

3 min read
In all the years working day in and day out with tools, technology and processes sometimes you miss the simplest and stupidest things. I have come across one of those bumps in the road that I have spent far more time on than I should but no more. Things like this I need to lay it out for all of you because I know there are others that have ran across this before.

I will lay out how to create a simple XML file from an Excel spreadsheet. If you are in anyway working with an application that brings in data then you have worked with either CSV (Comma Delimited Files) or XML files. CSV are easy to deal with however XML files are trick if you don’t know 100% about them especially if you have to create them from scratch. I know about them, I have created them progamatically but have recently required to create XML data source files from Excel Spreadsheets. Even though it should be a simple process I was surprised of the effort to so a simple export from Excel to an XML file that wasnt’ horrifically over bloated with XML tags.

So, here’s how you do it.

1) Open Excel

2) Make sure the XML tools are in your Menu/Ribbon. File > Options > Customize Ribbon. Then add the Developer to the menu. See the picture.

3) Create an XML Mapping file. This file is what you will use to map your Excel spreadsheet data. There is a catch that took my a while to figure out and is not documented very well in the official Microsoft help sites or other sites I looked around in.

My sample is only for a single node XML file, you can extend this process to the complexity of your data but once you understand the process it’s simple. The XML mapping file is only the nodes, is my main root and is for each Job Title. Now, if you only have one line for your data element Excel won’t populate it with your whole sheet, only the first one. You need to have two here in order to Excel to traverse the whole column. This is what took me a while to figure out. Bug? By Design? Either way I think it’s dumb and redundant.
 
4) Once you get the mapping file created, in any Notepad program will be just fine, you need to open the mapping file in your data sheet. In the Developer Ribbon, in the XML tools, click Source. A side window will open, click XML Maps… and Add your mapping file.

 

5) To format your XML drag your data element to the column of data. It will get colors if it’s done correctly.

6) After that, Export it using the Export in the Developer tools, you don’t save it as an XML.

 
 
7) Enjoy your XML file.


For a simple process, using Excel, it’s more complicated than it should be. I don’t understand why this isn’t a built in Wizard. There are tons of tools out there for XML creation but they can be overwhelming when you want a very simple XML file.

End of Line.

~~~~~~~~~~~~~~~~~
Binary Blogger
@BinaryBlogger

Please follow and like us:
Pin Share
Copyright © All rights reserved. | Newsphere by AF themes.

Enjoy this blog? Please spread the word :)

  • RSS
  • Follow by Email
  • Twitter
    Visit Us
    Follow Me
  • YOUTUBE
  • INSTAGRAM
RSS
Follow by Email
Twitter
Visit Us
Follow Me
YOUTUBE
INSTAGRAM