{"id":845,"date":"2009-10-30T12:27:24","date_gmt":"2009-10-30T12:27:24","guid":{"rendered":"http:\/\/www.mjtnet.com\/blog\/?p=845"},"modified":"2009-10-30T12:27:24","modified_gmt":"2009-10-30T12:27:24","slug":"convert-xml-to-csv","status":"publish","type":"post","link":"https:\/\/www.mjtnet.com\/blog\/2009\/10\/30\/convert-xml-to-csv\/","title":{"rendered":"Convert XML to CSV"},"content":{"rendered":"

Earlier this month I wrote about how you can read and write data from\/to CSV files<\/a> as if they were database tables using DBQuery. Andrew asked<\/a> 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. <\/p>\n

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<\/a>. <\/p>\n

The more technically aware will probably ask why we can’t just use the MSXML.DomDocument object in VBScript<\/a> 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. <\/p>\n

So, without further ado, here’s my ConvertXMLtoCSV function:<\/p>\n

VBSTART\r\nSub ConvertXMLtoCSV(xmlFile,csvFile)\r\n    Set xlApp = CreateObject(\"Excel.Application\")\r\n    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)\r\n    xlBook.SaveAs csvFile, 6\r\n    xlBook.close false\r\n    xlApp.quit\r\nEnd Sub\r\nVBEND<\/pre>\n
You will need Excel installed for this to work.  Use the function like this:<\/p>\n
VBRun>ConvertXMLtoCSV,c:\\docs\\my.xml,c:\\docs\\converted.csv<\/pre>\n
And now you can quickly read the data into an array using DBQuery.<\/p>\n
The function works quietly in the background.  You won’t see Excel open – just a short delay while the CSV file is created.<\/p>\n
To convert to an Excel file rather than a CSV file change the code to:<\/p>\n
VBSTART\r\nSub ConvertXMLtoXLS(xmlFile,xlsFile)\r\n    Set xlApp = CreateObject(\"Excel.Application\")\r\n    Set xlBook = xlApp.Workbooks.OpenXML(xmlFile,2)\r\n    xlBook.SaveAs xlsFile, 56\r\n    xlBook.close false\r\n    xlApp.quit\r\nEnd Sub\r\nVBEND<\/pre>\n
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.<\/p>\n
See:
\nReading From CSV Files<\/a>
\nMore on Reading and Writing CSV Files<\/a>
\nRetrieve Entire Excel Sheet Using DBQuery<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/845"}],"collection":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/comments?post=845"}],"version-history":[{"count":3,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/845\/revisions"}],"predecessor-version":[{"id":848,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/posts\/845\/revisions\/848"}],"wp:attachment":[{"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/media?parent=845"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/categories?post=845"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mjtnet.com\/blog\/wp-json\/wp\/v2\/tags?post=845"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}