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.

image

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

image

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

    image

    XML Source task pane is displayed.

image

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

image

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

    image

    Click OK button.

image

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

    image

  • Select Meta and drag it to cell a1.

    image

  • Select ExpenseItem and drag it to a3 cell.

    image

    Now the mapping is linked to our Excel worksheet.

image

  • Click Import button.

image

  • Select Nancy expenses.xml and click Import button.

image

  • Data is added to the worksheet.

image

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

image

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.

 

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

3 Responses to Import Data Into Excel – XML

  1. Pingback: Import Data Into Excel – SQL Server Import and Export Wizard « Technologies and your business

  2. Pingback: Import Data Into Excel – Data Connection « Technologies and your business

  3. Pingback: Import Data Into Excel – VBA « Technologies and your business

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: