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:
Code:
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();
xslt.Load("style.xsl");
StreamWriter sw = new StreamWriter("report.html");
xslt.Transform(doc, null, sw);
sw.Close();
}
}
XSLT stylesheet
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="countryKey" match="/*/*" use="country"/>
<xsl:template match="root">
<html>
<head>
<title>Our Customers Worldwide</title>
</head>
<body>
<table style="border:thin solid orange;">
<xsl:for-each select="*[count(.|key('countryKey',
country)[1])=1]">
<xsl:sort select="country"/>
<tr>
<th colspan="2"
style="text-align:center;color:blue;">
<xsl:value-of select="country"/>
</th>
</tr>
<tr>
<th>Customer Name</th>
<th>Account Number</th>
</tr>
<xsl:apply-templates
select="key('countryKey', country)"/>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
<xsl:template match="row">
<tr>
<xsl:if test="position() mod 2 = 1">
<xsl:attribute name="bgcolor">silver</xsl:attribute>
</xsl:if>
<td>
<xsl:value-of
select="concat(fname, ' ',mi, ' ', lname)"/>
</td>
<td>
<xsl:value-of select="account_num"/>
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
Resulting HTML:
| Canada | |
|---|---|
| Customer Name | Account Number |
| Derrick I. Whelply | 87470586299 |
| Michael J. Spence | 87500482201 |
| Brenda C. Blumberg | 87544797658 |
| Mexico | |
| Customer Name | Account Number |
| Sheri A. Nowmer | 87462024688 |
| Rebecca Kanagaki | 87521172800 |
| Kim H. Brunner | 87539744377 |
| USA | |
| 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.
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";
doc.Load(reader);
Console.WriteLine(doc.OuterXml);
doc.Save("output.xml");
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,
Ruslana
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.
Oleg,
I have a question.....
-----------
my csv file is :
"customer_id","account_num","lname","fname","mi"
1,87462024688,"Nowmer","Sheri","A."
2,87470586299,"Whelply","Derrick","I."
the Xml file I got using XmlCsvReader is :
1
87462024688
Nowmer
Sheri
A.
2
87470586299
Whelply
Derrick
I.
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
87462024688
Nowmer
Sheri
A.
2
87470586299
Whelply
Derrick
I.
----------
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,
Pmuk
Thanks Oleg, I got the dll and added the reference to my project.
Best Rgds,
pmuk
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,
pmuk