Show All

Hide All

Merging and analyzing financial data can be a thorny task. The inventory data might be maintained in one software program, the tax data in another, and purchase orders in yet another. Sometimes, you can use a converter to make data compatible with your software. At 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 programs, including Excel 2003. In fact, for structural data, Excel 2003 is a great choice for viewing and analyzing data in worksheets.

For example, the following file contains purchase order data. If this data is in a format incompatible with Excel, you might have difficulty working with it. If this data is in XML, however, you can open the file and analyze the data in a format you're familiar with.

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, click the XML file that you want to open, and then click Open.
3. In the Open XML dialog box, click As an XML list, and then click OK.

   

4. If the XML Source task pane does not appear on the right side of the screen, click Task Pane on the View menu.

Each column in the XML list corresponds to an XML element in the task pane. In this example, the XML element ns1:AuthBy is mapped to column A (ns1:AuthBy), and Annette Hill is displayed in the row data.

Open a file as a read-only workbook

1. On the File menu, click Open.
2. In the Open dialog box, click the XML file that you want to open, 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 mapping. You can read or copy the file, but you cannot change or save it. If you change a read-only file, you can save your changes only if you give the document a new name.

Open a file by using the XML Source task pane

1. On the File menu, click Open.
2. In the Open dialog box, click the XML file that you want to open, 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. In this example, you drag the report elements, such as AuthBy and PurchaseOrder, to cells near the top of the worksheet. You drag the item elements, such as Class and Description, to cells in a row on the worksheet.

   Tip

   To select nonadjacent elements, click one element, hold down CTRL, and then click each element you want to select. To select and drag all the elements in a folder, drag the folder to the left cell of the worksheet location where you want the elements to appear.

   

5. Select a cell in the mapped range to which you want to import XML data.
6. On the List toolbar, click Import XML data.

   

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.

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, click the file that contains the XML data file you want to import, and then click Import.

Apply 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.

Data incompatibility is solved

With all the programs you use now speaking to each other, you can exchange your financial data smoothly and efficiently. And you can view and analyze data in familiar Excel worksheets, by itself or with related data.