Need to convert XML to CSV in VB6 ?

Need to convert XML to CSV in VB6?

Don't want to spend the rest of your life learning how to do it?

Read the "Least You Need to Know" guide below

CSV is probably the mostly widely supported format for importing data to traditional applications. XML however has become increasingly fashionable even when it's perhaps not the best format for this type of import.

This quick guide demonstrates how to convert a basic XML file to CSV. It will convert all the XML nodes found so you don't need to worry about how many child nodes exist in the XML file.

All you need to specify is the root node name and the child node you want to convert.

The example shows the XML file we want to convert, the XSL file that controls the conversion, the CSV output produced and the VB6 code used to achieve these results.

Please remember that this is the very least you need to know but it should serve to get you started with a minimum of pain. To do anything more you will need to spend some time learning about all those things you really didn't want to know about.

Quick Intro

An XSL file is a template that tells the XML processor how to translate an XML file. If your needs are fairly basic it's a whole lot easier than writing code to process the XML file. If you have lots of nested elements you'll probably be better of writing code to handle the XML file. Our example simply specifies the XML to be converted, the XSL file to be used and writes out the CSV file.

There are some limitations of this example. It doesn't pick up the Stock Code from the Stock record so you want probably want to make sure that there was a separate StockCode element. It also won't cope with sub nodes of the stock record.

Before you start

You must add a reference to Microsoft XML vsn 3.0 to your project. There are lots of versions of XML but this version appears to be available on all Microsoft Windows versions. If you use a later version you will need to install the appropriate MSI file on your clients machines.

The XML File to be converted

<?xml version="1.0"?>
<Stock>
<StockRecord StockCode="1010">
<DESCRIPTION>House Coal - 10Kg</DESCRIPTION>
<QTY_IN_STOCK>0</QTY_IN_STOCK>
<QTY_ALLOCATED>0</QTY_ALLOCATED>
<SALES_PRICE>1.619</SALES_PRICE>
<TAX_CODE>2</TAX_CODE>
<LAST_UPDATED>03/10/2006 09:52:15</LAST_UPDATED>
</StockRecord>
<StockRecord StockCode="1025">
<DESCRIPTION>House Coal - 25Kg</DESCRIPTION>
<QTY_IN_STOCK>0</QTY_IN_STOCK>
<QTY_ALLOCATED>0</QTY_ALLOCATED>
<SALES_PRICE>4.0476</SALES_PRICE>
<TAX_CODE>2</TAX_CODE>
<LAST_UPDATED>03/10/2006 09:52:15</LAST_UPDATED>
</StockRecord>

</Stock>

The XSL File

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:template match="Stock">
<xsl:apply-templates select="StockRecord"/>
</xsl:template>

<xsl:template match="StockRecord">
<xsl:for-each select="*">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
<xsl:if test="position() != last()">
<xsl:value-of select="','"/>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text>
</xsl:template>

</xsl:stylesheet>

The VB6 Code

Public Function ConvertXMLToCSV()

Dim ErrText As String
Dim Silent As Boolean
Dim jobstep As String
Dim j As Integer
Dim Procname As String

Dim xmlSource
Dim xmlXForm
Dim strErr As String
Dim strResult As String
Dim iFilenumber As Integer

Dim strPath As String

Procname = "ConvertXMLToCSV"

On Error GoTo Errorhandler

'********************************
jobstep = "Creating XML Objects"
'********************************

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

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

'***********************************
jobstep = "Loading XML Source File"
'***********************************

' This loads the text that we want to transform.
' This doesn't raise an error if the file doesn't exist.

xmlSource.Load App.Path & "\" & "Stock.xml"

'****************************
jobstep = "Loading XSL File"
'****************************

' This loads the XSLT transform
' This doesn't raise an error if the file doesn't exist.

xmlXForm.Load App.Path & "\" & "Stock.xsl"

'*****************************
jobstep = "Transforming File"
'*****************************

' This transforms the data in xmlSource

strResult = xmlSource.transformNode(xmlXForm)

'*****************************
' These errors can be trapped.
'*****************************

On Error Resume Next

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"

Else

    ' Now write the string to a file.

     '****************************
     jobstep = "Getting FreeFile"
     '****************************

     On Error Resume Next

    Kill App.Path & "\" & "Stock.csv"

    On Error GoTo Errorhandler

     iFilenumber = FreeFile

     Open App.Path & "\" & "Stock.csv" For Binary As iFilenumber

     Put #iFilenumber, , strResult

     '************************
     jobstep = "Closing file"
     '************************

     Close iFilenumber

End If

ExitProc:

On Error Resume Next
Exit Function

Errorhandler:

ErrText = " " & Procname & " - Error " & Err & " at " & jobstep & ", " & Error(Err)

If Silent = False Then
     MsgBox ErrText, vbExclamation, Procname
End If

Resume ExitProc

End Function

The CSV output

"House Coal, test - 10Kg","0","0","1.619","2","03/10/2006 09:52:15"
"House Coal - 25Kg","0","0","4.0476","2","03/10/2006 09:52:15"

For more information please try the following links

[ Home ]

Need to convert XML to CSV in VB6?

Copyright © 2005 - 2009 Steve Blencowe Systems Limited

Last Updated 14/06/2010