Export Excel to XML Tutorial

We have made this simple table with a list of people identified by Name, Job Title, Address, Phone, and Mail fields to create business cards.

Excel offers an automatic export of the stylesheet into the XML format; when you click on File > Save As, there is an XML Data (*.xml) option in the Save as type dropdown menu.

As you can see, if we do that, Excel returns an error because it needs an XML map of the current table.

Let’s start with XML Map.

With our preferred text editor, let’s create a blank file and add XML header information.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

Each XML file must contain a root element. In our tutorial, we want to create an XML containing business cards so we will add a business cards root element.

<businesscards> … </businesscards>

Writing an XML element is quite simple; we only have to keep in mind some simple rules so our XML will be valid:

• Each element consists of an opening tag (<businesscards>) and a closing tag (</businesscards>).
• Each tag in an XML file must be enclosed by an opening angle bracket “<” and a closing angle bracket “>”; the tag name is inside these two characters. To define a closing tag, we have to add a slash “/” character after the opening angle bracket.

Tag names must be formatted respecting some simple rules:

• They cannot start with a number.
• They cannot start with a dot (.), comma (,) character, or any other punctuation characters. Names cannot start with “xml” word in any format such as uppercase, lowercase, etc.
• They cannot contain spaces

Above we have listed only some of the many XML specifications and for this tutorial, we do not need anything else.

Let’s continue this tutorial by adding a card element inside our root element.

<businesscards>
   <card> … </card>
</businesscards>

Now inside our card let’s add all the card elements in the Excel table: name, jobtitle, address, phone, mail, and inside each element we add a sample text data.

<card>
   <name>Employee name</name>
   <address>Employee address</address>
   <phone>999-999999</phone>
   <mail>employee@mail.com</mail>
</card>

To have a correct map, we must have twice the card elements, so we duplicate this card element and after that XML file looks like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<businesscards>
   <card>
      <name>Employee name</name>
      <jobtitle>Job Title</jobtitle>
      <address>Employee address</address>
      <phone>999-999999</phone>
      <mail>employee@mail.com</mail>
   </card>
   <card>
      <name>Employee name</name>
      <jobtitle>Job Title</jobtitle>
      <address>Employee address</address>
      <phone>999-999999</phone>
      <mail>employee@mail.com</mail>
   </card>
</businesscards>

Save and close it and open the Excel file with the business cards table.

If not it is not enabled, click on the Developer menu tab from menu File > Options, then click on the tab Customize Ribbon, and check Developer in the right column.

Now on the Developer menu tab click on the Source command.

A new panel appears in the right side of Excel.

Click on the XML Maps… In the new dialog box that appears, click on Add… and choose the XML file saved before.

A warning dialog box appears (because it needs an office schema but it doesn’t matter because it will import elements as they are), click OK and the XML Source panel will be filled with our XML elements.

The last step is the real field mapping. We need to select each field and drag and drop it from its column in the Excel table. Pay attention to dragging them to the first row.

It’s time to convert the Excel to XML. When you have finished with the field mapping, click on the Export command on the Developer tab.

Choose where to save the XML file and all the rows will be exported.