O'Reilly logo

Excel Hacks by Raina Hawley, David Hawley

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #95. Load an XML Document into Excel

If someone sends you an XML file containing data that fits into tables, you don't need to read the text and all its angle brackets. You can load the document into Excel directly, tell Excel how you want to present it, and work with the data through maps.

Extensible Markup Language (XML) has become a common interchange format over the past few years, and it's not unusual for people and organizations to send each other XML files. XML's simple core structures make it easy to exchange information with much less concern that all parties are using the same software. Until recently, however, although generic XML tools were widely available, bridging the gap between XML documents and the user interface was difficult. Excel 2003 makes it much easier, at least for data that fits on a grid.

Tip

This hack uses Excel features that are available only in Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel.

We'll start with a sample XML document, shown in Example 8-1.

Example 8-1. A simple XML document for analysis in Excel

<?xml version="1.0" encoding="UTF-8"?> <sales> <sale> <date>2003-10-05</date> <ISBN>0596005385</ISBN> <Title>Office 2003 XML Essentials</Title> <PriceUS>34.95</PriceUS> <quantity>200</quantity> <customer ID="1025">Zork's Books</customer> </sale> <sale> <date>2003-10-05</date> <ISBN>0596002920</ISBN> <Title>XML in a Nutshell, 2nd Edition</Title> <PriceUS>39.95</PriceUS> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required