Lesson 98: How to convert Excel files to XML

You can also transfer files Excel to XML or vice versa. This allows you to exchange data between different applications. First, enable the Developer tab.

This is the data I want to convert to an XML file.

How to convert Excel files to XML

First, we will create an XML-based schema. The schema defines the structure of the XML file.

1. Open Notepad and add the following lines.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <record>
       <LastName>Smith</LastName>
       <Sales>16753</Sales>
       <Country>UK</Country>
       <Quarter>Qtr 3</Quarter>
    </record>
    <record>
       <LastName>Johnson</LastName>
       <Sales>14808</Sales>
       <Country>USA</Country>
       <Quarter>Qtr 4</Quarter>
    </record>
</data-set>

Note: the tags refer to 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 data-set.xlsx.

4. On the Developer tab, in the XML group, click Source to open the XML Source.

5. To add XML Maps, click XML Maps.

6. Click Add.

7. Select schema.xml and click OK twice.

8. Now just drag the data onto the sheet.

9. On the Developer tab, in the XML group, click Export.

10. Save your file as data-set.xml and press Enter.

Result:

Note: to import an XML file, open a blank workbook, on the Developer tab, in the XML group, click Import and select the XML file.

In addition, you can also view many other excel articles here.