"How to XSLT CSV file" revisited

| 10 Comments | No TrackBacks
Well, it's extremely well-chewed topic well-covered by many posters, but provided people keep asking it I feel I have to give a complete example of the most effective way (IMO) of solving this old recurring question - how to transform CSV or tab-delimited file using XSLT?

The idea is to represent non-XML formatted data as pure XML to be able to leverage many's favorite XML hammer - XSLT. I want to make it clear that approaching the problem this way doesn't abuse XSLT as XML transformation language. Non-XML data is being represented as XML and XSLT operates on it via XPath data model prism actually having no idea it was CSV file on the hard disk.

Let's say what's given is this tab-delimited file, containing some info such as customer ID, name, address about some customers. You need to produce HTML report with customers grouped by country. How? Here's how: all you need is XmlCSVReader (cudos to Chris Lovett), XSLT stylesheet and couple lines of code to glue the solution:


using System;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.IO;
using Microsoft.Xml;

public class Sample {
    public static void Main() {
        //XMLCSVReader setup
        XmlCsvReader reader = new XmlCsvReader();
        reader.Href = "sample.txt";
        reader.Delimiter = '\t';
        reader.FirstRowHasColumnNames = true;
        //Usual transform
        XPathDocument doc = new XPathDocument(reader);
        XslTransform xslt = new XslTransform();
        StreamWriter sw = new StreamWriter("report.html");
        xslt.Transform(doc, null, sw);

XSLT stylesheet

<xsl:stylesheet version="1.0" 
    <xsl:key name="countryKey" match="/*/*" use="country"/>
    <xsl:template match="root">
                <title>Our Customers Worldwide</title>
                <table style="border:thin solid orange;">
                    <xsl:for-each select="*[count(.|key('countryKey', 
                        <xsl:sort select="country"/>
                            <th colspan="2" 
                                <xsl:value-of select="country"/>
                            <th>Customer Name</th>
                            <th>Account Number</th>
                            select="key('countryKey', country)"/>
    <xsl:template match="row">
            <xsl:if test="position() mod 2 = 1">
                <xsl:attribute name="bgcolor">silver</xsl:attribute>
                select="concat(fname, ' ',mi, ' ', lname)"/>
                <xsl:value-of select="account_num"/>

Resulting HTML:

Customer Name Account Number
Derrick I. Whelply 87470586299
Michael J. Spence 87500482201
Brenda C. Blumberg 87544797658
Customer Name Account Number
Sheri A. Nowmer 87462024688
Rebecca Kanagaki 87521172800
Kim H. Brunner 87539744377
Customer Name Account Number
Jeanne Derry 87475757600
Maya Gutierrez 87514054179
Robert F. Damstra 87517782449
Darren M. Stanz 87568712234

Main virtue of this approach is that all transformation and presentation logic is concentrated in only one place - XSLT stylesheet (add CSS according to your taste), C# code is fully agnostic about data being processed. In the same fashion CSV file can be queried using XQuery or XPath. Once the data is represented as XML, all doors are open.

Related Blog Posts

No TrackBacks

TrackBack URL: http://www.tkachenko.com/cgi-bin/mt-tb.cgi/88


mehrnaz, you better open connection to thge ftp server yourself and once you get response stream, feed it into XMLCSVReader.

I am using XmlCsvreader to read csv files from the FTP server.
I have this to create xml from csv files:
XmlDocument doc = new XmlDocument();
XmlCsvReader reader = new XmlCsvReader(ftpName,Encoding.UTF8,doc.NameTable);
reader.FirstRowHasColumnNames = false;
reader.RootName = "root";
reader.RowName = "row";
but i get error that says: Default credentials are not supported on an FTP request.
I don't know how I can change the code to set username and password of the FTP.
Tnx in advance for your help

You can use my CSV parser to speed up the parsing of the actual CSV file instead of using the class inside XmlCsvReader, http://www.csvreader.com/ .

Do you have .NET installed?
XmlCsvReader is .NET application so it can't be run from VB (but can be from VB.NET).

I downloaded the XmlCsvReader. When I try to run it from ms-dos prompt window, my ms-dos prompt crashes, saying "tried to perform illegal opearation. Any idea as to how to fix this? Also is it possiible to run it from within MS Access visual basic?

I will really appreciate any help with this.

Best Regards,


I don't think that can be done with xmlcsvreader (unless you extend it).
xmlcsvreader just reads csv as XML. If you want to modify this XML, use some kind of XML processing. XSLT is usually the simplest solution, but if your XML is a big one, more advanced scenarios with customized XmlReader are available.

I have a question.....

my csv file is :

the Xml file I got using XmlCsvReader is :



I want to add namespaceUri & addributes to the element so that it allows nulls. So the final Xml output I want is like this:




1. How to add attributes to the elements? Any properties of xmlcsvreader to use? If so, what are they?
2. How to add namespaceUri & prefix(xsd,xsi...)
3. How to allow null values?

Thanks for your help,

Thanks Oleg, I got the dll and added the reference to my project.

Best Rgds,

Unfortunately XmlCSVReader doesn't provide dll you can refer to. So you need to do it yourself.
1. Download XmlCSVReader using link above.
2. Extract it to some directory.
3. Open XmlCsvReader.sln solution in Visual Studio .NET (you need C# to be installed).
4. Select XmlCsvReader project, right click and select Properties in the context menu.
5. Change "Output Type" to "Class Library".
6. Click Ok.
7. Rebuild the solution.

Then you can a reference to the XmlCsvReader.dll into your project (no matter VB.NET or C#) and use Microsoft.Xml.XmlCsvReader class.

I am not able to add 'Imports Microsoft.Xml'. For this to get, do I need to add any reference? If so, what reference should I add?

Thanks for all your help.

Best Rgds,

Leave a comment