Convert comma delimited data to XML using XSLT

The attached VB.NET sample shows how to convert a comma delimited (.CSV) file to XML using VB.NET and Extensible Stylesheet Language Transformations (XSLT). Converting the comma delimited data using XSL has pros and cons. On the plus side, you don't need to loop through the input CSV file and walk the XML DOM adding elements and/or attributes. On the minus side, attempting to transform large CSV files may produce Out of Memory errors.

The sample takes comma delimited data of the form:

    John Doe,123.00,AAA,10/1/1958
    Mary Jane,34.50,BBB,8/25/1960

and converts it to XML looking like:

<ROOT>
  <ROW RowNum="1">
    <NAME>John Doe</NAME>
    <SOMEVALUE>123.00</SOMEVALUE>
    <SOMECODE>AAA</SOMECODE>
  </ROW>
  <ROW RowNum="2">
    <NAME>Mary Jane</NAME>
    <SOMEVALUE>34.50</SOMEVALUE>
    <SOMECODE>BBB</SOMECODE>
  <ROW>
</ROOT>

The first step is to wrap the comma delimited data with <ROOT></ROOT> XML tags so it can be treated as an XML document. Then an XslTransform object from the System.Xml.Xsl namespace is created, the XSL stylesheet is loaded and the XSL transform is applied. The VB.NET code to do this is similar to:

    Imports System.Xml
    Imports System.Xml.Xsl
    '
    ' Create a resolver with default credentials.
    ' Create the XSL Transform object.
    '
    Dim XSLT As XslTransform = New XslTransform
    Dim resolver As XmlUrlResolver = New XmlUrlResolver
    resolver.Credentials = System.Net.CredentialCache.DefaultCredentials
    '
    ' Load the stylesheet. Transform the file. 
    '
    XSLT.Load(strInputXSLTFile, resolver)
    XSLT.Transform(strInputCSVFile, strOutputXMLFile, resolver)
    XSLT = Nothing

Only a few lines of VB code are needed to apply the transform. The bulk of the code exists in the XSLT file. The XSL contains three templates (XSL jargon for procedures). The first one matches the root XML node (<ROOT></ROOT>) and calls the second linestorows template which recursively convert each line in the CSV file to a ROW node in the XML.

The linestorows template calls the third fieldstoxml template which recursively parses the line, splits it at the commas, and creates the corresponding XML elements.

The XSLT file is annotated and should be easy to follow if you are familiar with XSL. If not, their are a lot of good books and online resources available.


Download Source Code

Instructions

Download the source code and run it. Use the Browse buttons to select the input comma delimited CSV file, the XSLT file to transform the data, and the XML file to hold the results.

The sample code includes two XSL transform files. XSLT file "transform.xslt" hard codes the names of the fields in the comma delimited file. Thus, it is specific to the included test file. XSLT file "transform2.xslt" is generic and will work with any .CSV file. Try the two XSLT files and view the resultant XML.

Notes

This code will not run under version 1.0 of the .Net framework.








About TheScarms
About TheScarms


Sample code
version info

If you use this code, please mention "www.TheScarms.com"

Email this page



The World's Number 1 Web Host

© Copyright 2014 TheScarms
Goto top of page