Click here to Skip to main content
15,867,756 members
Articles / Programming Languages / XSLT
Article

Convert XML to CSV, with XSL

Rate me:
Please Sign up or sign in to vote.
3.15/5 (17 votes)
1 May 20063 min read 272K   3.4K   38   30
An article about using XSL to transform XML to CSV

Image 1

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:

VBScript
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:

VBScript
' 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:

VBScript
' 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:

VBScript
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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Other
Netherlands Netherlands
Brian is on a never ending mission to share and gain knowledge. And hopefully inspiring the ones who have been helped being able to help others in the future.

In the industry since 1997 mostly in financial related environments working with SQL Server from version 7, VB6, VBA and .NET (C#). Build numerous MS Office snippets from 1998 till today on the internet look for bruintje Wink | ;-)

Comments and Discussions

 
GeneralMy code works without error, but my output file is empty Pin
milledj30-May-10 8:00
milledj30-May-10 8:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.