Friday, June 02, 2006

XML -> CSV XSL Transformation

CSV - comma separated values format (supported by Excel).
Handling quotes in XML->>CSV XSLT
First trick is to save the " and "" inside a variable, second trick is to use a recursive template.


Handling quotes, commas and line-breaks in XML->>CSV XSLT

In a CSV file, if a field value contains a comma or a linefeed or a quote, then
it must be enclosed in quotes.
Also, if a field value contains quotes, then an extra quote must be inserted in front of each quote.



My implementation:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8" />
<xsl:template match="/">
<xsl:for-each select="Table">
<xsl:call-template name="DisplayCSV">
<xsl:with-param name="field" select="Column1"/>
</xsl:call-template>
<xsl:text>,</xsl:text>
<xsl:call-template name="DisplayCSV">
<xsl:with-param name="field" select="Column2"/>
</xsl:call-template>
<xsl:text>,</xsl:text>
<xsl:call-template name="DisplayCSV">
<xsl:with-param name="field" select="Column3"/>
</xsl:call-template>
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:template>


<xsl:template name="DisplayCSV">
<xsl:param name="field"/>

<xsl:variable name="linefeed">
<xsl:text>&#10;</xsl:text>
</xsl:variable>

<xsl:choose>
<xsl:when test="contains( $field, '&quot;' )">
<!-- Field contains a quote. We must enclose this field in quotes,
and we must escape each of the quotes in the field value.
-->
<xsl:text>"</xsl:text>

<xsl:variable name="apos">"</xsl:variable>
<xsl:variable name="aposapos">""</xsl:variable>
<xsl:call-template name="SubstringReplace">
<xsl:with-param name="stringIn" select="normalize-space($field)"/>
<xsl:with-param name="substringIn" select="$apos"/>
<xsl:with-param name="substringOut" select="$aposapos"/>
</xsl:call-template>

<xsl:text>"</xsl:text>
</xsl:when>

<xsl:when test="contains( $field, ',' ) or
contains( $field, $linefeed )" >
<!-- Field contains a comma and/or a linefeed.
We must enclose this field in quotes.
-->
<xsl:text>"</xsl:text>
<xsl:value-of select="normalize-space($field)" />
<xsl:text>"</xsl:text>
</xsl:when>

<xsl:otherwise>
<!-- No need to enclose this field in quotes.
-->
<xsl:value-of select="normalize-space($field)" />
</xsl:otherwise>

</xsl:choose>
</xsl:template>

<xsl:template name="SubstringReplace">
<xsl:param name="stringIn"/>
<xsl:param name="substringIn"/>
<xsl:param name="substringOut"/>
<xsl:choose>
<xsl:when test="contains($stringIn,$substringIn)">
<xsl:value-of select="concat(substring-before($stringIn,$substringIn),$substringOut)"/>
<xsl:call-template name="SubstringReplace">
<xsl:with-param name="stringIn" select="substring-after($stringIn,$substringIn)"/>
<xsl:with-param name="substringIn" select="$substringIn"/>
<xsl:with-param name="substringOut" select="$substringOut"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$stringIn"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>

</xsl:stylesheet>

No comments:

Followers

About Me

My photo
Email me: blog@postjobfree.com