Excel – Quick way to convert Excel sheet to XML

microsoft excelmicrosoft-office-2007xml

How do you easily convert an excel file into a XML file?

When trying to save as an XML File, it complains that the file does not have an XML mapping. Clicking help brings up pretty complicated stuff about XML Mapping file, XLD and some other acronyms.
Why is it so complicated?

Lately I've realized that tab delimited, CSV and others are prone to formating issues (comas in a field, new lines, quotes, …). So I think that XML is a better way to process excel data.

Please advise. Maybe a freeway tool?

Best Answer

Here is an oldish reference to the 'XML::Excel' Perl module.
There is another question on Stackoverflow that shows samples for converting to CSV that may give you ideas on using such scripts.

There is also a short IBM developerWorks article on tips to Convert Excel data to XML which points to some resources.

Related Solutions

Excel Convert XML cell to columns

Have a look at the newest stock formulas for Excel 2013: =WEBSERVICE and =FILTERXML

Here is an example: In cell A1 place the following webservice formula. It will retrieve the latest weather for John F Kennedy airport.

=WEBSERVICE("http://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=metars&requestType=retrieve&format=xml&hoursBeforeNow=3&mostRecent=true&stationString=KJFK")

In cell A2 enter the following formula. It will parse the XML data using XPATH to get the station identifier KJFK. Using XPATH you should be able to extract any portion of the XML you want.

=FILTERXML(A1,"/response/data/METAR/station_id")

Excel – Edit XML files in Excel

To edit your xml data manually, you can do the following

On the Developer Ribbon, XML Tab, Import
You may see this window:

Click OK and import to A1
On that same tab, click Source which will open the XML Source pane with the mappings
As you select cells on the worksheet, the different nodes will be highlighted in the XML Source pane, so you can see what is being referred to.
You can then alter the different values and save the document back as an XML document. The changes will be maintained.
With regard to the ns1:, I refer you to Excel to XML Namespace Prefix Customization. But there are other articles in SO that have can guide you in removing the ns1:, should you choose to do that.

Best Answer

Related Solutions

Excel Convert XML cell to columns

Excel – Edit XML files in Excel

Related Question