Convert XML to CSV, with XSL

• Download source - 1.44 Kb

Introduction

This VBScript will use the MSXML DLL from Microsoft to transform an XML file with XSL to a semi colon separated file, which can be easily imported in a host of other applications that are not completely XML aware yet.

Background

This week, I faced a situation where a user had to be allowed to import an XML file into a MS Access database. My first thought was using Excel for the purpose, and she created a schema based on the file. Then she ran into a message that said the file being too large for the sheet to import at once. There are a few ways to work around this limitation but since the objective was to import the XML into an MS Access database, converting the file to a more readable format was our next stop.

Using the Code

For this sample, I used an XML file from the Internet, and downloaded the PlantCatolog.xml from the the W3schools site. And as a starting point for the XSL file, I used the sample written by Peter Burden. With these two files, we only have to write the VBScript to transform the XML to our resulting file.

First, we save the XML file on the C disk as testme.xml and the XSL file as testme.xsl. Note that the difference is in the extension, so we can use the same filename to keep things simple. Our resulting file will also be on C and will be called testme.txt.

Now, fire up Notepad and add this code, creating the needed variables first:

  Dim xmlSource
  Dim xmlXForm
  Dim strErr
  Dim strResult 

  Dim fso, file
  Dim strPath
  Const ForWriting = 2

  Set xmlSource = CreateObject("MSXML.DOMDocument")
  Set xmlXForm = CreateObject("MSXML.DOMDocument")

  xmlSource.validateOnParse = True
  xmlXForm.validateOnParse = True
  xmlSource.async = False
  xmlXForm.async = False

As you can see, this is just a bit of general initializing, and creating the objects to load our XML and XSL files needed in the next step:

  ' This loads the text that I want to transform

  xmlSource.Load "c:\testme.xml"

  If Err.Number <> 0 Then
      strErr = Err.Description & vbCrLf
      strErr = strErr & xmlSource.parseError.reason & " line: " & _      
               xmlSource.parseError.Line & " col: " & _
               xmlSource.parseError.linepos & _
               " text: " & xmlSource.parseError.srcText
      MsgBox strErr, vbCritical, "Error loading the XML"

  End If

  ' This loads the XSLT transform

  xmlXForm.Load "c:\testme.xsl"

  If Err.Number <> 0 Then

      strErr = Err.Description & vbCrLf
      strErr = strErr & xmlSource.parseError.reason & " line: " & _      
               xmlSource.parseError.Line & " col: " & _
               xmlSource.parseError.linepos & _
               " text: " & xmlSource.parseError.srcText
      MsgBox strErr, vbCritical, "Error loading the Transform"
  End If

This tells the XML objects to load the files into memory for the transform which we perform next:

  ' This transforms the data in xmlSource

  strResult = xmlSource.transformNode(xmlXForm)

  If Err.Number <> 0 Then

      strErr = Err.Description & vbCrLf

      strErr = strErr & xmlSource.parseError.reason & _
               " line: " & xmlSource.parseError.Line & _
               " col: " & xmlSource.parseError.linepos & _
               " text: " & xmlSource.parseError.srcText

      MsgBox strErr, vbCritical, "Error executing the Transform"

  End If

At this point, the transformed XML is placed into a string variable called strResult that we need to write as file to disk:

  Set fso = CreateObject("Scripting.FileSystemObject")
  strPath = "c:\testme.txt"

  ' open the file

  Set file = fso.opentextfile(strPath, ForWriting, True)

  ' write the info to the file

  file.write strResult

  ' close and clean up

  file.Close

For this last step, we used the FileSystemObject which makes writing files in cases like this very easy. The download adds some cleanup for the objects, and I�ll leave that out here.

Points of Interest

This little project taught me how easy and convenient things can be when you use widely available tools. For this solution, I used MSXML, FSO, and Notepad, and these are generally available tools to anyone on the Windows platform. This is just a simple sample of the power simple tools harbor for good solutions.

Mapping XML to schemas, or using generic import/export wizards, is, at times, much more expensive in terms of time, which is often a realization afterwards. This solution took me one hour to build and debug, which is good investment when you see the possible applications for future projects. The time spend is in the mapping of the XSL to the XML, which I think can be automated too, but that�s where I lack some insight, so all suggestions are welcome if you think you know of a way to automate the XML to XSL mapping in this solution. Or I will do it myself in a future article again, using VBScript.

Any comments or additions related to this article are welcome. In this industry, we can think of several different methods and tools for solving everyday problems, and this is just one of them.

Happy programming!

History

• 2006.04.29 - Version 0.1.