How To: Excel 2010 XML Exports

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.

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.

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:

Binary Blogger

Binary Blogger has spent 20 years in the Information Security space currently providing security solutions and evangelism to clients. From early web application programming, system administration, senior management to enterprise consulting I provide practical security analysis and solutions to help companies and individuals figure out HOW to be secure every day.

Subscribe
Facebook Page
Follow Me On Twitter
contactme@binaryblogger.com