Retrieving Data from XML Files

Problem

You need to analyze some data contained in an XML file and would like to do so in Excel.

Solution

Import the XML file into Excel using File images/U2192.jpg border=0> Open ... and then manipulate the data as you normally would in Excel.

Discussion

Extensible Markup Language (XML) files are text files that use the XML protocol for structuring data. XML was developed as a standard, cross-platform data exchange file format that would make transferring data from platform to platform and application to application easy. In this regard, XML is very similar to HTML used for web pages.

Programmers using a variety of languages can create XML datafiles of program output. Database applications can put their data in XML files . In fact, Microsoft Access allows you to import and export XML files. If you need to get at the data contained in an XML file, you can do so using any of a number of programs that read XML files, like Access. However, if you're also interested in manipulating or analyzing the data in Excel, then the more direct route is to open the XML file directly in Excel. Select File Open ... from the main menu bar as you normally would to open a file, and select "XML Files (*.xml)" from the "Files of Type drop-down list instead of the default XLS file type.

This feature is currently available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Example 3-2 shows the partial contents of a sample XML file as if opened in a text editor.

Example 3-2. Sample XML file

- 
 1
 5083 - H111
 269
 145
 83
 165.62
 Aluminum
 Metric
 
- 
 2
 5083 - H116
 276
 165
 96
 199.92
 Aluminum
 Metric
 
- 
 3
 5083 - H321
 276
 165
 96
 215.6
 Aluminum
 Metric
 

Clearly the data contained in this file is difficult to decipher in this form. Once imported into Excel, the data appears as shown in Figure 3-29.

Now the data is in a useful tabular form in Excel. At this point you can manipulate it as you would any other spreadsheet.

When Excel imports an XML file, it attempts to infer the structure of the data unless an XML schema is available. A schema is like a template for an XML file. It provides further guidance on how to interpret the data in an XML file (e.g., how to interpret text versus numerical values and so on). Schemas can be embedded within an XML file or they can reside as separate XSD (XML Schema Definition) files. Excel calls these XML Map files, and they can be loaded in as well. See the help topic on XML Map files for your version of Excel for more information on loading map files.

Figure 3-29. XML data in Excel

 

See Also

The O'Reilly book Excel Hacks, by David and Raina Hawley, provides a few useful hacks on dealing with XML data in Excel. For more in-depth coverage, you should check out the O'Reilly book Office 2003 XML, by Evan Lenz, Mary McRae, and Simon St.Laurent. Chapter 6 is particularly interesting and even covers loading and saving XML files using VBA.