Import Data Into Excel – XML

I have discussed various methods to import data into Excel, e.g. SQL Server Import and Export Wizard, Data Connection and VBA.  They are all very powerful and flexible methods.  However, I cannot conclude this subject without discussing XML. 

XML is widely used for data migration so Microsoft Excel has provided us tools to import / export XML documents.  And I am going to demonstrate how simple it is.

Create XML Schema Definition File (XSD)

Copy and paste the following content into a XSD file using a text editor such as Notepad, e.g. expenses.xsd.

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="Meta">
          <complexType>
            <sequence>
              <element name="Name" type="string"/>
              <element name="Date" type="date"/>
              <element name="SAPCode">
                <simpleType>
                  <restriction base="positiveInteger">
                    <pattern value="[0-9]{4}" />
                  </restriction>
                </simpleType>
              </element>
            </sequence>
          </complexType>
        </element>
        <element name="ExpenseItem" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="Date" type="date"/>
              <element name="Description" type="string"/>
              <element name="Amount">
                <simpleType>
                  <restriction base="decimal">
                    <pattern value="[0-9]+(.[0-9]{2})?"/>
                  </restriction>
                </simpleType>
              </element>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Create XML Data File

Copy and paste the following content into a XML file using a text editor such as Notepad, e.g. Nancy expenses.xml.

<?xml version="1.0"?>
<Root 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Meta>
  <Name>Nancy Lee</Name>
  <Date>2010-01-01</Date>
  <SAPCode>0001</SAPCode>
</Meta>
<ExpenseItem>
  <Date>2010-01-01</Date>
  <Description>Airfare</Description>
  <Amount>500</Amount>
</ExpenseItem>
<ExpenseItem>
  <Date>2010-01-01</Date>
  <Description>Hotel</Description>
  <Amount>200</Amount>
</ExpenseItem>

</Root>

Enable XML Import / Import

XML buttons are under Developer Tab in Excel 2010 but Developer Tab is disabled by default.

So we have to enable Developer Tab first.  To get the Developer tab to display in Excel 2010, click the File > Options > Customize Ribbon:

• Once we enabled the Developer tab, we can click the Source button.

  

XML Source task pane is displayed.

• Click XML Maps… button and the XML Maps dialogue is displayed.

• Click Add… button.
• Select expenses.xsd and Click Open button.

  

Click OK button.

• A XML map is now added to the XML Source pane.

  

• Select Meta and drag it to cell a1.

  

• Select ExpenseItem and drag it to a3 cell.

  

Now the mapping is linked to our Excel worksheet.

• Click Import button.

• Select Nancy expenses.xml and click Import button.

• Data is added to the worksheet.

• The XML connection is established and we can refresh the XML data by clicking the Refresh Data button.

Conclusion

I only demonstrated how to import XML data into Excel but we can also export XML data from Excel.  XML document can contain very dynamic data and Excel allows us to export multiple worksheets to one single XML document.

 

• Import Data Into Excel – XML
• Import Data Into Excel – VBA
• Import Data Into Excel – Data Connection
• Import Data Into Excel – SQL Server Import and Export Wizard

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!