Use Excel to Create an XML Data Document

 

Based on the video (4:11):

“Convert Excel Spreadsheet Data to XML”

http://www.youtube.com/watch?v=9bat12gH3Qs

 

Does not work in Excel 2008 for Mac!

 

Have your data in an Excel spreadsheet before you begin. Open it in Excel.

 

1.    Create an XML schema, using the column names in your spreadsheet.

    1. You can do this in Dreamweaver (or in a text editor).
    2. Open Dreamweaver. Create a new XML document.
    3. Set up two records exactly as they are in the spreadsheet, using the column headers as your data tags (to save time, see note below).
    4. File menu > Validate (check the schema in Dreamweaver).

2.    Import the XML schema into Excel.

    1. Go to a new, empty Excel worksheet in your Excel workbook.
    2. Data menu > XML > XML Source … (in Windows only!)
    3. Alternative for Office 2007 (Windows): Data tab > “From Other Sources” > “From XML Data Import” > select the XML file you made in Dreamweaver
      1. “The specified source is not a schema” > just click OK
      2. Import Data > click OK
      3. Select and delete the two rows of data, keeping only the column headers.

3.    Copy and paste your original data, as shown in the video.

4.    Save as a new Excel Workbook.

5.    Then save again—as “XML Data” (not spreadsheet!).

    1. In Office 2007 (Windows): You have to choose “Other Formats”; then you can choose “XML Data.” You must also change the file extension manually (to .xml).
    2. You get a warning; click Continue.
    3. You are asked to save the map. Click OK.

6.    The resulting XML file is a valid, clean XML file!

 

Note

To create your schema quickly and easily, use the free online converter at

http://www.creativyst.com/cgi-bin/Prod/15/eg/csv2xml.pl