XML is a software and hardware independent tool for storing, transporting, and sharing data between incompatible systems, or upgraded systems. XML stores data in plain text format.
I try to convert an Excel file to an XML data file, or vice versa to share with other software.
If you want to export and import XML files (or insert controls, create a macro, etc.) turn on the Developer tab. If Developer tab is not visible, execute the following steps:
1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
2. Under Customize the Ribbon, on the right side of the dialog box, select Main Tabs, if necessary.
3. Check the Developer check box.
4. Click OK.
5. You can find the Developer tab next to the View tab.
Now you can convert your Excel file to an XML data file, or vice versa. This enables you to exchange data between different applications. First, turn on the Developer tab. Here is the data we want to convert to an XML data file.
First, we will create a schema based on XML source data. The schema defines the structure of the XML file. We have to do this outside Excel:
1. For example, open Notepad and add the following lines.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set>
<record>
<LastName></LastName>
<Sales></Sales>
<Country></Country>
<Period></Period>
</record>
<record>
<LastName></LastName>
<Sales></Sales>
<Country> </Country>
<Period></Period>
</record>
</data-set>
Note: the tags refer to the column names, but you can give them any name you want! For example, instead of LastName use Name.
2. Save the file as schema.xml.
3. Open Sales per quarter.xls.
4. On the Developer tab, open the XML Source task panel, clicking on Source.
5. To add an XML map, click XML Maps button.
The XML Maps dialog box appears.
6. Click Add...
7. Select schema.xml and click OK twice.
8. Now simply drag (map) the 4 elements from the tree onto the worksheet (row 1).
9. On the Developer tab, under the XML group, click Export.
10. Save your file as data-set.xml and press Enter. Result in the figure below:
That saves quite a lot of time!
Note: to import an XML file in Excel, open a blank workbook, on the Developer tab click Import and select the XML file.
You can download here the files used in this article: 20151116_Sales_per_quarter.zip