Click here to Skip to main content
12,747,078 members (30,782 online)
Click here to Skip to main content
Add your own
alternative version


38 bookmarked
Posted 1 May 2006

Convert XML to CSV, with XSL

, 1 May 2006
Rate this:
Please Sign up or sign in to vote.
An article about using XSL to transform XML to CSV


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.


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

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!


  • 2006.04.29 - Version 0.1.


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


About the Author

Brian Mulder
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 | ;-)

You may also be interested in...

Comments and Discussions

GeneralMy vote of 1 Pin
Mehdi_Dirbaz9-Jul-11 22:46
memberMehdi_Dirbaz9-Jul-11 22:46 
QuestionHow do I create the XSL File ? Pin
Alex Slack22-Feb-11 21:43
memberAlex Slack22-Feb-11 21:43 
GeneralMy code works without error, but my output file is empty Pin
milledj30-May-10 9:00
membermilledj30-May-10 9:00 
QuestionLarge xml's crash [modified] Pin
strNeedHelp25-May-10 19:42
memberstrNeedHelp25-May-10 19:42 
This worked great in testing, however, when I tried it on actual xml with it's xslt, the size was too much for it to handle. Can you point me in the right direction for best solution for xml's sized 1,000,000 KB to 5,100,000 KB. Am new to Visual Studio but if the solution resides there, I need to learn, or if elsewhere, please direct me, thanks.

modified on Wednesday, May 26, 2010 1:10 AM

AnswerRe: Large xml's crash Pin
milledj31-May-10 15:48
membermilledj31-May-10 15:48 
GeneralRe: Large xml's crash Pin
strNeedHelp1-Jun-10 18:44
memberstrNeedHelp1-Jun-10 18:44 
GeneralRe: Large xml's crash Pin
milledj2-Jun-10 2:58
membermilledj2-Jun-10 2:58 
GeneralRe: Large xml's crash Pin
milledj2-Jun-10 9:45
membermilledj2-Jun-10 9:45 
GeneralRe: Large xml's crash Pin
strNeedHelp2-Jun-10 17:14
memberstrNeedHelp2-Jun-10 17:14 
GeneralVery good and clear article Pin
Michele_6618-May-10 1:34
memberMichele_6618-May-10 1:34 
GeneralXML to HTML with XSL in 2003 Server x64. Pin
Alex M. Alvarez A.11-Nov-09 2:04
memberAlex M. Alvarez A.11-Nov-09 2:04 
Generalconvert XML to CSV Pin
kishore kumar v12-Sep-09 0:41
memberkishore kumar v12-Sep-09 0:41 
Questionxml transformation Pin
ah wen5-Jun-07 17:38
memberah wen5-Jun-07 17:38 
QuestionXML to CSV Pin
anadzeal8-Feb-07 14:23
memberanadzeal8-Feb-07 14:23 
GeneralRe: XML to CSV Pin
Hemang2330-Jul-08 1:30
memberHemang2330-Jul-08 1:30 
GeneralAutomate XML to XSL mapping Pin
daluu23-Jan-07 12:54
memberdaluu23-Jan-07 12:54 
GeneralNice way to do XML transformation in VBScript / WSH Pin
daluu23-Jan-07 12:53
memberdaluu23-Jan-07 12:53 
Generalgives error message 80004005 Pin
Prat_rn21-Sep-06 12:58
memberPrat_rn21-Sep-06 12:58 
GeneralRe: gives error message 80004005 Pin
hotcigar12-Dec-08 5:08
memberhotcigar12-Dec-08 5:08 
GeneralNot So Bad Pin
Mavarok4-Jul-06 4:16
memberMavarok4-Jul-06 4:16 
GeneralBonerificly Bad Pin
Mavarok4-Jul-06 3:50
memberMavarok4-Jul-06 3:50 
GeneralRe: Bonerificly Bad Pin
Rodney D4-Apr-07 10:27
memberRodney D4-Apr-07 10:27 
QuestionWhy not invoke the transformation from the command line? Pin
IainWildman9-May-06 7:52
memberIainWildman9-May-06 7:52 
AnswerRe: Why not invoke the transformation from the command line? Pin
Brian Mulder9-May-06 8:48
memberBrian Mulder9-May-06 8:48 
AnswerRe: Why not invoke the transformation from the command line? Pin
jkarretero25-Mar-09 23:17
memberjkarretero25-Mar-09 23:17 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170215.1 | Last Updated 1 May 2006
Article Copyright 2006 by Brian Mulder
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid