Thursday, October 20, 2011

How to Read XML Data in Excel 2007


Import an XML data file into Excel 2007
1. Open the Excel 2007 application.
2. Click on the Microsoft icon on the top-left corner of the application's screen and click 'Open.'
3. Locate the XML data file on your hard drive.
4. Click on 'Open' to launch the Open XML or the Import XML dialog box. The Open XML dialog box will be displayed if the XML file does not have a style sheet reference. If the file does have a style sheet reference, the Import XML dialog box will be displayed.
5. Click on one of the three options if the Open XML dialog box is displayed on the screen. For example, the 'As an XML table' option imports the contents of the file to the workbook and applies an inferred schema to the data if the file does not refer to one. The 'As a read-only workbook' option causes the XML data file to be converted into a table containing rows and columns. The root node of the file is displayed as a heading in the first cell. The data is displayed in a read-only file with the tags as column headings. Any other tags are sorted alphabetically and shown on the second row. No inferred schema is applied to your data in this case. The 'Use the XML Source task pane' option displays the schema of the XML file in a task pane to the right of your application screen. Drag elements from the pane to the Excel worksheet in order to map them.
6. Click on one of the two options if the Import XML dialog box is displayed on the screen. For example, the 'Open the file with the following style sheet applied (select one)' option allows you to choose one of the style sheets shown. The selected style sheet will be applied to the data. The 'Open the file without applying a style sheet' option displays the data in the worksheet in columns and rows with the XML tags as column headings. The root node is displayed in the first cell. The rest of the elements are sorted and placed in the second cell.

Blogger news