Click here to Skip to main content
15,311,717 members
Articles / Programming Languages / XML
Posted 23 May 2006


50 bookmarked

Converting a List of Data to XML using Microsoft Excel 2003

Rate me:
Please Sign up or sign in to vote.
4.63/5 (25 votes)
23 May 20065 min read
A tutorial on using XML maps in Excel to convert spreadsheet data to XML.

Image 1


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.


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.

Image 2

  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.

    Image 3

    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.

    Image 4

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

    Image 5

  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:

    Image 6

  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.

    Image 7

    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.

    Image 8

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

    Image 9

  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.

    Image 10

  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.

    Image 11


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.


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


About the Author

Mike Ellison
University of Nevada, Las Vegas
United States United States
With a background in education, music, application development, institutional research, data governance, and business intelligence, I work for the University of Nevada, Las Vegas helping to derive useful information from institutional data. It's an old picture, but one of my favorites.

Comments and Discussions

Questionlist of lists type Pin
Andrew Joo10-Jul-13 9:22
MemberAndrew Joo10-Jul-13 9:22 
GeneralMy vote of 4 Pin
sleepingboy88821-Feb-13 22:51
Membersleepingboy88821-Feb-13 22:51 
QuestionConverting like above with excel 2008 on mac Pin
Member 924633612-Jul-12 7:07
MemberMember 924633612-Jul-12 7:07 
AnswerRe: Converting like above with excel 2008 on mac Pin
Mike Ellison16-Jul-12 10:42
MemberMike Ellison16-Jul-12 10:42 
QuestionNice Article but one doubt Pin
aashish3027-Feb-12 21:18
Memberaashish3027-Feb-12 21:18 
GeneralMy vote of 5 Pin
thewazz20-Jan-12 11:23
professionalthewazz20-Jan-12 11:23 
GeneralMy vote of 5 Pin
v3ks25-Apr-11 11:55
Memberv3ks25-Apr-11 11:55 
GeneralExcellent Pin
priyanka.pardeshi17-Nov-10 23:10
Memberpriyanka.pardeshi17-Nov-10 23:10 
GeneralRe: Excellent Pin
Mike Ellison18-Nov-10 5:49
MemberMike Ellison18-Nov-10 5:49 
GeneralExcellent one Pin
Jagan_nathan_V8-Jun-09 5:58
MemberJagan_nathan_V8-Jun-09 5:58 
GeneralRe: Excellent one Pin
Mike Ellison8-Jun-09 8:14
MemberMike Ellison8-Jun-09 8:14 
GeneralGenerating XML in Excel (and other environments) without limitations Pin
rkapl9-Jan-07 5:07
Memberrkapl9-Jan-07 5:07 
GeneralExcellent clarification Pin
leniel29-Dec-06 20:22
Memberleniel29-Dec-06 20:22 
GeneralRe: Excellent clarification Pin
Mike Ellison2-Jan-07 6:35
MemberMike Ellison2-Jan-07 6:35 
QuestionTime notation without frames Pin
edwoofer25-Oct-06 3:37
Memberedwoofer25-Oct-06 3:37 
AnswerRe: Time notation without frames Pin
Mike Ellison6-Nov-06 13:31
MemberMike Ellison6-Nov-06 13:31 
Generalclosure of tags Pin
edwoofer24-Oct-06 6:43
Memberedwoofer24-Oct-06 6:43 
GeneralRe: closure of tags Pin
Mike Ellison24-Oct-06 7:04
MemberMike Ellison24-Oct-06 7:04 
GeneralXML Attributes vs. Elements Pin
Cal Schrotenboer6-Jun-06 20:13
MemberCal Schrotenboer6-Jun-06 20:13 
GeneralRe: XML Attributes vs. Elements [modified] Pin
Mike Ellison7-Jun-06 5:44
MemberMike Ellison7-Jun-06 5:44 
Generalexcellent article for beginner-----thanks Pin
ff_lee730-May-06 13:44
Memberff_lee730-May-06 13:44 
GeneralRe: excellent article for beginner-----thanks Pin
NoSpamNsk31-May-06 9:17
MemberNoSpamNsk31-May-06 9:17 
GeneralRe: excellent article for beginner-----thanks Pin
Mike Ellison2-Jun-06 5:25
MemberMike Ellison2-Jun-06 5:25 

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

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