My Place For SQL

Analyze XML data in Excel

Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Merging and analyzing financial data from a variety of sources can be a thorny task. What software applications were used to generate the data? What operating systems? Sometimes you can use a converter to make data compatible with your software. Other times, such as when the data comes from proprietary software, converting data is expensive and time-consuming.

Enter XML
But now there’s Extensible Markup Language (XML). XML is a method for putting structured data into a text file that follows standard guidelines maintained by the World Wide Web Consortium (W3C) (World Wide Web Consortium (W3C): A consortium of commercial and educational institutions that oversees research and promotes standards in all areas related to the World Wide Web.). XML data can be read by a variety of applications, including Microsoft Office Excel 2003. In fact, for structural data, Excel is a great choice for viewing and analyzing data in worksheets.

For example, the following file contains expense report data. In a format incompatible with Excel, this data could be difficult to work with. As XML, however, you can open and analyze it in a familiar format.

XML file with expense report data


Opening an XML file
You can open an XML file in Excel in one of three ways:

• As an XML list An XML list is created in a new workbook. An XML list is an Excel list that has been mapped to one or more XML elements. Each column in the XML list represents an XML element.
  
• As a read-only workbook The XML data file is opened as a read-only workbook. The mapping of elements is not saved.
  
• As an XML list, using the XML Source task pane You can choose how an XML list is displayed by mapping elements of the schema (schema: A description of a database that defines the attributes of the database, such as tables, fields (columns), and properties.) to the worksheet. When you open an XML file as an XML list in the XML Source task pane, the schema of the XML data file is displayed, and you can drag elements of the schema onto the worksheet.
  
  Note When you open an XML file that does not have XML mapping, the Open XML dialog box does not appear, and the file opens as an XML workbook.
  
  Open a file as an XML list
  
  1. On the File menu in Excel, click Open.
  2. In the Open dialog box, locate the XML file that you want to open, click it, and then click Open.
  3. In the Open XML dialog box, click As an XML list, and then click OK.
     
  
  
  XML file opened as an XML list
  
  
  Open a file as a read-only workbook
  
  1. On the File menu, click Open.
  2. In the Open dialog box, locate the XML file that you want to open, click it, and then click Open.
  3. In the Open XML dialog box, click As a read-only workbook, and then click OK.
  
  The XML file opens as a workbook with no XML maps. The file can be read or copied, but cannot be changed or saved. If you change a read-only file, you can save your changes only if you give the document a new name.
  
  XML file opened as a read-only workbook
  
  
  Open a file using the XML Source task pane
  
  1. On the File menu, click Open.
  2. In the Open dialog box, locate the XML file that you want to open, click it, and then click Open.
  3. In the Open XML dialog box, click Use the XML Source task pane, and then click OK.
     The XML Source task pane opens.
     
  4. To map one or more elements to your worksheet, select the elements in the XML Source task pane and drag them to the worksheet location where you want them to appear.
     
     XML source elements mapped to XML list
     
     
     
  5. Select a cell in the mapped range to which you want to import XML data.
  6. On the List toolbar, click Import.
     
     
  7. In the Import XML dialog box, locate the XML data file you want to import. This can be the same file that contained the schema or a different XML file.
  8. Click the file, and then click Import.
     The data appears in the mapped XML list.
     
     
  XML list with imported data
  
  
  Importing XML data into an existing XML list
  
  Caution By default, existing data in a mapped range is overwritten when you import data. If you want to append new data to the existing data, follow these steps before you import data:
  
  
  1. Select a cell in the mapped range.
  2. On the List toolbar, click XML Map Properties.
     
     
  3. Click Append new data to existing XML lists, and then click OK.
  
  Now you can import new data without overwriting the existing data.
  
  Import data into an existing XML list
  
  
  1. Select a cell in the mapped range to which you want to import XML data.
  2. On the List toolbar, click Import.
     
     
  3. In the Import XML dialog box, locate the file that contains the XML data file you want to import, click it, and then click Import.
  
  Applying automatic formatting
  
  1. Select a cell in the XML list that you want to format.
  2. On the Format menu, click AutoFormat.
  3. Click the format you want, and then click OK.