Click here to Skip to main content
Click here to Skip to main content
Go to top

Converting a List of Data to XML using Microsoft Excel 2003

, 23 May 2006
Rate this:
Please Sign up or sign in to vote.
A tutorial on using XML maps in Excel to convert spreadsheet data to XML.

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Mike Ellison

United States United States
I work for the University of Nevada, Las Vegas in the Office of Institutional Analysis and Planning. Among other things, our office is charged with the mission of deriving useful information in support of administrative decision-making from institutional data. Within the context of that mission, my office mates and I apply technology in the form of custom data processing applications, data extraction and analysis tools, reporting tools, relational databases, OLAP solutions, data warehousing, and data mining.
 
Visit my blog at MishaInTheCloud.com


Comments and Discussions

 
Questionlist of lists type PinmemberAndrew Joo10-Jul-13 9:22 
GeneralMy vote of 4 Pinmembersleepingboy88821-Feb-13 22:51 
QuestionConverting like above with excel 2008 on mac PinmemberMember 924633612-Jul-12 7:07 
AnswerRe: Converting like above with excel 2008 on mac PinmemberMike Ellison16-Jul-12 10:42 
QuestionNice Article but one doubt Pinmemberaashish3027-Feb-12 21:18 
GeneralMy vote of 5 Pinmemberthewazz20-Jan-12 11:23 
GeneralMy vote of 5 Pinmemberv3ks25-Apr-11 11:55 
GeneralExcellent Pinmemberpriyanka.pardeshi17-Nov-10 23:10 
GeneralRe: Excellent PinmemberMike Ellison18-Nov-10 5:49 
GeneralExcellent one PinmemberJagan_nathan_V8-Jun-09 5:58 
GeneralRe: Excellent one PinmemberMike Ellison8-Jun-09 8:14 
GeneralGenerating XML in Excel (and other environments) without limitations Pinmemberrkapl9-Jan-07 5:07 
GeneralExcellent clarification PinmemberLeniel29-Dec-06 20:22 
GeneralRe: Excellent clarification PinmemberMike Ellison2-Jan-07 6:35 
QuestionTime notation without frames Pinmemberedwoofer25-Oct-06 3:37 
AnswerRe: Time notation without frames PinmemberMike Ellison6-Nov-06 13:31 
Generalclosure of tags Pinmemberedwoofer24-Oct-06 6:43 
GeneralRe: closure of tags PinmemberMike Ellison24-Oct-06 7:04 
GeneralXML Attributes vs. Elements PinmemberCal Schrotenboer6-Jun-06 20:13 
GeneralRe: XML Attributes vs. Elements [modified] PinmemberMike Ellison7-Jun-06 5:44 
Generalexcellent article for beginner-----thanks Pinmemberff_lee730-May-06 13:44 
GeneralRe: excellent article for beginner-----thanks PinmemberNoSpamNsk31-May-06 9:17 
GeneralRe: excellent article for beginner-----thanks PinmemberMike Ellison2-Jun-06 5:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 23 May 2006
Article Copyright 2006 by Mike Ellison
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid