Converting a List of Data to XML using Microsoft Excel 2003

• Download sample files - 2.58 Kb

Introduction

Microsoft Excel 2003 supports XML in a variety of ways. One application of this XML functionality is the ability to export a list of data to an XML format. This is particularly useful when working with data originally in comma-separated value files (.csv files) or fixed-width data files. Both are plain text file formats, and are common import/export options for database and spreadsheet applications.

Normally, an XSL stylesheet would be considered when transforming a data source to an XML output. An XSL stylesheet will not help us transform .csv or fixed-width data into XML however, as XSL requires an XML-based source. A .csv or fixed-width file is easily imported into Excel though, and with a few additional steps, may be exported to an XML format of choice.

An Excel list becomes exportable to XML when assigned an XML map. An XML map is an XML schema or other file showing an example of the desired XML output, in which case Excel can infer the schema. Either is associated with the spreadsheet containing the list data. The user then may perform drag-and-drop operations to connect individual data fields from the map to the list source. Finally, the data is exported with a menu command.

Step-by-step

The starting point for this task is straightforward: Excel is open, and you have either entered or imported a list of data (e.g., a .csv file). Include a heading row, identifying field names at the top of each column. For practice, you may use the file Sample.xls which may be downloaded with the link at the top of this article.

1. We will begin by creating the map file, which can be either an XML schema or a file showing example XML output. Open Notepad or your desired text editor.
2. Enter and save the map file. For practice, you may use the file sectionsMap.xml that comes with the article download. In this illustration, we are creating an example XML document that demonstrates the output we wish to achieve rather than use a schema. Excel will read the example output and infer a schema appropriate for the map.

   For the example output, create a root element. Nested within the root, create an element that is associated with a single row of data in the list. The individual fields of data in the list row may be identified as attributes of that element, or as child elements of the parent.

   Once the element representing a full list row is complete, copy and paste it as a second element within the root. It is important that the example output imply multiple elements to associate with multiple list rows. The picture below shows the <section> tag repeated twice deliberately; this allows Excel to infer that it will repeat throughout the list of data.

   

   If the <section> tag were not repeated in the example output, Excel would treat its association as a single cell of data rather than a column of cells. Repeating the <section> element in the example allows Excel to infer an association to the column across multiple rows.

3. In Excel, with the sheet containing your list of data active, choose the menu Data -> XML -> XML Source. The XML Source task pane opens.

   

4. In the XML Source task pane, click the XML Maps… button.

   

5. In the XML Maps dialog, click Add..., and select the schema or XML example output file you created in step #2. If you are using an XML example rather than a schema, like the sectionsMap.xml file from the article downloads, you will see the following dialog, which you may dismiss by clicking OK:

   

6. In the XML Maps dialog, the map file will be added. Click OK to dismiss the dialog.
7. The XML Source task pane will now display the map elements and attributes.

   

   This is a good time to verify Excel’s interpretation of the example output. Each of the icons in the XML map describes the interpretation of the given element or attribute.

   

   (source: Excel Help)

   The picture above shows that the <section> element in our example output file has been interpreted as a “repeating parent element”. The “repeating” aspect of this interpretation is necessary to correlate with repeating rows of list data. The element’s “children” are the individual fields of data across a given row.

8. Now, it’s time to associate an individual child element or attribute in the map with its list column in the source. Do this by clicking and dragging a field from the map to the list column header to which it should be associated. When dropped on the column header, the list column will highlight and is automatically converted to an Excel List object.

   

9. Repeat the drag-and-drop operation for each additional field, associating each field in the map with its respective column of list data by dropping it on top of the column header.

   

10. With the XML associations made, it is time to export the data. Choose the menu Data -> XML -> Export… and specify the location/filename to create the new XML file.
11. Open the newly created file in Notepad or another text editor to verify that the export has worked as desired.

    

Summary

A list of data in Excel, such as that which may be obtained from a .csv or fixed-width data file, may be exported to an XML format, provided an XML map has been assigned to the spreadsheet. An XML map may either be an XML schema describing the source data, or an XML document showing an example of the desired output format. In an example output file, a parent element is defined to correlate with a given list row. The parent element is repeated at least once in the example output, allowing Excel to infer its association to repeated rows in the list. Individual child attributes and elements may then be associated with respective list columns through a drag-and-drop operation, connecting the XML child to its related list field header. With the XML map in place and field items associated to columns of data, the data may be exported with the menu command Data -> XML -> Export. When converted to XML, the list data is then suitable for use as a source in an XSL transformation or any other XML application.