Convert XML to CSV

Earlier this month I wrote about how you can read and write data from/to CSV files as if they were database tables using DBQuery. Andrew asked if I knew of a way that XML files could be treated as database tables in the same way. I did find a couple of references to some ODBC drivers online but one was not yet available and the other was part of a larger commercial package so I was unable to try them.

Since then it has occurred to me that there is a way we can convert XML files to CSV (or XLS) files using Excel. Once the data is in CSV file format we can then retrieve it easily using DBQuery.

The more technically aware will probably ask why we can’t just use the MSXML.DomDocument object in VBScript to read in the XML and parse it. Well, while that is of course possible, many will find the ability to access the XML data in a tabular database-like format easier and more familiar.

So, without further ado, here’s my ConvertXMLtoCSV function:

VBSTART
Sub ConvertXMLtoCSV(xmlFile,csvFile)
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)
    xlBook.SaveAs csvFile, 6
    xlBook.close false
    xlApp.quit
End Sub
VBEND

You will need Excel installed for this to work. Use the function like this:

VBRun>ConvertXMLtoCSV,c:\docs\my.xml,c:\docs\converted.csv

And now you can quickly read the data into an array using DBQuery.

The function works quietly in the background. You won’t see Excel open – just a short delay while the CSV file is created.

To convert to an Excel file rather than a CSV file change the code to:

VBSTART
Sub ConvertXMLtoXLS(xmlFile,xlsFile)
    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)
    xlBook.SaveAs xlsFile, 56
    xlBook.close false
    xlApp.quit
End Sub
VBEND

Please note I’m using Excel 2007 and haven’t tried this with earlier versions though I believe Excel 2003 can also read from XML files. Success may also depend on the format of your XML.

See:
Reading From CSV Files
More on Reading and Writing CSV Files
Retrieve Entire Excel Sheet Using DBQuery