How to Import XML to XL

Microsoft Excel 2003 Professional edition and all subsequent releases provide full support for XML.

Microsoft implemented support for extensible markup language, or XML, with the Professional editions of Excel 2003. XML consists of a set of rules that determine how data is structured. Excel works with two types of XML files. The schema, or .xsd, file, provides the rules that specify how to handle and validate data. The data, or .xml, file stores the actual data. Excel can use the schema to create a set of mapped elements in a workbook to which data files can be imported. Excel can also work directly with XML data in the form of an external data set.

Things You'll Need

• Microsoft Excel 2003 Professional Edition or higher
• XML data file
• XML schema file

Instructions

Import XML Data as an External File

1. Open Microsoft Excel. Click on the "Data" tab and locate the "Get External Data" group. Click on the icon for "From Other Sources" and select "From XML Data" from the drop-down menu.

2. Locate the XML data file. Select the file name and then click on "Open" in the lower right hand corner of the current dialog box. The "Import Data" dialog box will appear.

3. In the "Import Data" dialog box, specify whether to import the data as an XML table in a new worksheet, XML table in an existing worksheet, or whether to flatten the data into a two-dimensional table with the XML tags serving as column headings in the worksheet.

4. Save the spreadsheet when the import has completed.

Import XML Data Into Mapped Worksheet Cells

1. Highlight the "Developer" tab in Excel. If the "Developer" tab does not appear on the menu, click on the Microsoft Office button in the upper left corner of the application window and then click on "Excel Options" in the lower right hand corner of the dialog box.

2. Highlight "Popular" in the menu on the left and click on the check box next to "Show Developer tab in the Ribbon" under "Top Options for Working with Excel." Then click on "OK" to exit the dialog box.

3. Create a workbook that links to an existing XML schema. If there are no mapped elements, use the "XML Source Task Pane" under "XML Source Task Pane" to develop a mapping in the current workbook.

4. Import individual data items by selecting one of the mapped cells in the active worksheet and then clicking on the "Import" icon in the "XML group" under the "Developer" tab, which will open the "Import XML" dialog window. Select the location of the XML data file and click on "Import."

5. Import the entire XML data table by clicking on the "Import" icon in the "XML group" under the "Developer" tab, which will open the "Import XML" dialog window. Select the location of the XML data file and click on "Import." Specify whether to import the file into a new or existing worksheet.

Tips & Warnings

• Import errors may occur when XML data that is imported into mapped cells that cannot be validated against an existing schema. Either remove the schema or verify that the mappings contain the correct XML data element.

• XML support is provided in Excel 2003 (Professional editions) or higher. Previous versions, including the Basic, Small Business, Student and Standard editions of Excel 2003, will not support XML.

References

• Microsoft Office: Overview of XML in Excel
• Microsoft Office: Import XML Data
• Mr. Excel: Using XML in Excel 2003