How to open XML files in Excel 2003

Article translations Article translations
Article ID: 827294 - View products that this article applies to.
For a Microsoft Excel 2002 version of this article, see 282161.

Expand all | Collapse all

On This Page

SUMMARY

In Microsoft Excel, you can open and save Extensible Markup Language (XML) files. This article describes how XML files are displayed when you open them in Excel.

MORE INFORMATION

Structured Data

XML is a meta-markup language that provides a format for describing structured data. XML is a subset of the Standard Generalized Markup Language (SGML) that is optimized for delivery over the Web.

XML is a flexible text format that is derived from SGML (ISO 8879) as defined by the World Wide Web Consortium (W3C). The W3C oversees the implementation of XML so that it remains independent of applications and vendors. For more information about the W3C and XML, visit the following W3C Web site:
http://www.w3.org
In XML, you can define an unlimited set of tags. Tags are used to describe the type of data that is enclosed. The following XML example is a weather report:
<?xml version="1.0" ?>
<weather-report>
   <date>March 25, 1998</date>
   <time>08:00</time>
   <area>
      <city>Seattle</city>
      <state>WA</state>
      <region>West Coast</region>
      <country>USA</country>
   </area>
   <measurements>
      <skies>partly cloudy</skies>
      <temperature>46</temperature>
      <wind>
         <direction>SW</direction>
         <windspeed>6</windspeed>
      </wind>
      <h-index>51</h-index>
      <humidity>87</humidity>
      <visibility>10</visibility>
      <uv-index>1</uv-index>
   </measurements>
</weather-report>

How to Open Files in Excel

When you open an XML file in Excel, you are prompted to select one of the following methods to import the XML data:
  • As an XML list
  • As a read-only workbook
  • Use the XML Source task pane

Open the File As an XML List

When you open an XML source, Excel looks for a tag for an XML style sheet (XSL). The XSL describes how the data is to be presented. If the tag is present, Excel prompts you with a choice to apply the style sheet or not. If you select to apply an XSL, the XSL dictates how to present the data; in this case, the remainder of the information in this section of this article does not apply.

If there is no XSL tag, you receive the following message:
The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data.
Excel then imports the data in the XML source. If the XML data contains duplicate fields inside an element, Excel spreads the data over multiple rows. Each row contains one unique set of data. This can cause a small set of data in XML to be displayed as a larger number of rows in Excel.

Open the File As a Read-Only Workbook

When you select to open an XML source document as a read-only workbook, the first tag of the element is used like a title and is placed in cell A1. The rest of the tags are sorted alphabetically and placed across the second row. The weather report example earlier in this article will be sorted into the following list of column headings when you open it as a read-only workbook:
  • /area/city
  • /area/country
  • /area/region
  • /area/state
  • /date
  • /measurements/h-index
  • /measurements/humidity
  • /measurements/skies
  • /measurements/temperature
  • /measurements/uv-index
  • /measurements/visibility
  • /measurements/wind/direction
  • /measurements/wind/windspeed
  • /time
If there is one set of data, the data for each of these columns is put on the third line of the spreadsheet. If the XML data contains duplicate fields inside an element, Excel spreads the data over multiple rows. Each row contains one unique set of data. This can cause a small set of data in XML to be displayed as a larger number of rows in Excel.

Use the XML Source Task Pane

The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.

If you are opening an XML data file that does not refer to a schema, Excel infers the schema of the XML data file.

REFERENCES

287739 XL2002: Error Message: The Following Features from Your Workbook Will Not Be Saved in the XML Spreadsheet

287734 XL2002: Excel Saves XML Files in the XML Spreadsheet Format


For more information about XML, visit the Microsoft Developer Network XML Home or the XML Software Developer's Kit Web site:
http://msdn.microsoft.com/xml/default.aspx
http://msdn2.microsoft.com/en-us/library/aa203727(office.11).aspx

Properties

Article ID: 827294 - Last Review: January 12, 2007 - Revision: 5.3
APPLIES TO
  • Microsoft Office Excel 2003
Keywords: 
kbxml kbimport kbopenfile kbconversion kbconvert kbinfo KB827294

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com