Tip: Converting a CSV File into XML by Excel

I came across a problem to convert a historical data in comma-separated format (CSV) into a modern & more useful XML format. There are many ways to do such conversion. The most obvious one is to do it programmatically by writing a program to read CSV data and exporting it to an XML file by means of the Simple API for XML (SAX) or the Document Object Model (DOM) libraries.

But I was too lazy to do it.

I knew that Microsoft Excel 2003 supports exporting worksheets into XML data. I tried it out but found that it is not that simple -- we have to define an XML schema and bind each element to each column of the worksheet.

So I went on researching.

Finally, I found a very useful tool on MSDN:

Excel 2003 XML Tools Add-in Version 1.1 (download).

The Excel Add-in's "XML Tools -> Convert a Range to an XML List" menu command allows us to simply select a range in a worksheet (with or without first row as column names) -- with the advanced options to specify the XML root element name and row element name.

The add-in automatically prepare our worksheet for exporting of XML data by the "Data -> XML -> Export" menu command.

Here is a summary of how I used Excel to convert a CSV file into XML:

1. Install Excel 2003 XML Tools Add-in


2. Open the CSV file, e.g. LegacyData.csv, in Excel


3. Save as an Excel workbook


4. Open the Excel workbook


5. Use Menu -> XML Tools -> Convert a Range to an XML List


6. Select the whole data rage


7. Choose "Yes, first row contains column names"


8. Select "Use Advanced Options"


9. Set XML root element name, e.g. "Root"


10. Set XML row element name, e.g. "Record"


11. Click "OK"


12. An error message "The data that you are attempting to map contains formatting that is incompatible with the format specified in the worksheet" will pop up if we try to convert an automatically formatted date column. This is because of the formatting in the Date column. As a workaround, I chose "Match element data type." Dates in the date column will be converted to the raw format, e.g. 38013 for 1/27/2004.


13. Export the data by using Menu -> Data -> XML -> Export


14. The dates in the Date elements of the exported XML file will be in the raw format.


15. Open the exported XML file in an XML editor. In my case, I used XMLSpy.


16. Go to the Grid view. Select all entries in the Date column (but DO NOT delete the "Date" column title) and delete the entries.


17. Copy the corresponding date entries from the Excel worksheet and simply paste them into the Date column.


18. Save the XML file. The XML data file is now properly converted.

I hope this posting will be useful to those who face the same problem as mine.


Keywords: tips, conversion, csv, xml, excel