Click here to Skip to main content
Click here to Skip to main content

Convert XML to CSV, with XSL

By , 1 May 2006
Rate this:
Please Sign up or sign in to vote.

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.

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

About the Author

Brian Mulder
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 vote of 1 PinmemberMehdi_Dirbaz9-Jul-11 21:46 
QuestionHow do I create the XSL File ? PinmemberAlex Slack22-Feb-11 20:43 
GeneralMy code works without error, but my output file is empty Pinmembermilledj30-May-10 8:00 
QuestionLarge xml's crash [modified] PinmemberstrNeedHelp25-May-10 18:42 
AnswerRe: Large xml's crash Pinmembermilledj31-May-10 14:48 
GeneralRe: Large xml's crash PinmemberstrNeedHelp1-Jun-10 17:44 
GeneralRe: Large xml's crash Pinmembermilledj2-Jun-10 1:58 
GeneralRe: Large xml's crash Pinmembermilledj2-Jun-10 8:45 
GeneralRe: Large xml's crash PinmemberstrNeedHelp2-Jun-10 16:14 
GeneralVery good and clear article PinmemberMichele_6618-May-10 0:34 
GeneralXML to HTML with XSL in 2003 Server x64. PinmemberAlex M. Alvarez A.11-Nov-09 1:04 
Generalconvert XML to CSV Pinmemberkishore kumar v11-Sep-09 23:41 
Questionxml transformation Pinmemberah wen5-Jun-07 16:38 
QuestionXML to CSV Pinmemberanadzeal8-Feb-07 13:23 
GeneralRe: XML to CSV PinmemberHemang2330-Jul-08 0:30 
GeneralAutomate XML to XSL mapping Pinmemberdaluu23-Jan-07 11:54 
GeneralNice way to do XML transformation in VBScript / WSH Pinmemberdaluu23-Jan-07 11:53 
Generalgives error message 80004005 PinmemberPrat_rn21-Sep-06 11:58 
GeneralRe: gives error message 80004005 Pinmemberhotcigar12-Dec-08 4:08 
GeneralNot So Bad PinmemberMavarok4-Jul-06 3:16 
GeneralBonerificly Bad PinmemberMavarok4-Jul-06 2:50 
GeneralRe: Bonerificly Bad PinmemberRodney D4-Apr-07 9:27 
QuestionWhy not invoke the transformation from the command line? PinmemberIainWildman9-May-06 6:52 
AnswerRe: Why not invoke the transformation from the command line? PinmemberBrian Mulder9-May-06 7:48 
AnswerRe: Why not invoke the transformation from the command line? Pinmemberjkarretero25-Mar-09 22:17 

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

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

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