register
other register

Friday, July 04, 2008

Convert CSV to XML Using XSLT

Refer to: http://blogs.msdn.com/kaevans/archive/2003/04/17/5780.aspx

In your CSV file,

1. Surround the whole data with tag.
2. Add tag to the first row which contains the heading titles
3. Add tag to contain the whole data.

<root>
<head>firstname, lastname</content>
<content>bla, bla
bla, bla</conent>
</root>

<xsl:stylesheet version ="2.0" xmlns:xsl ="http://www.w3.org/1999/XSL/Transform" > <xsl:output method ="xml"/> <!-- template that matches the root node--> <xsl:template match ="/" > <root> <xsl:call-template name ="texttorows" > <xsl:with-param name ="StringToTransform" select ="/root/content" /> <xsl:with-param name="heading" select="/root/heading" /> </xsl:call-template> </root> </xsl:template> <!-- template that actually does the conversion--> <xsl:template name ="texttorows" > <!-- import $StringToTransform--> <xsl:param name ="StringToTransform" select ="''" /> <xsl:param name="heading" select="''" /> <!-- <xsl:variable name="CR" select="'&#013;'"/> --> <xsl:variable name="CR" select="'&#xA;'"/> <xsl:choose> <!-- string contains linefeed--> <xsl:when test ="contains($StringToTransform,$CR)" > <!-- Get everything up to the first carriage return--> <row> <xsl:call-template name ="csvtoxml" > <xsl:with-param name ="StringToTransform" select ="substring-before($StringToTransform,$CR)" /> <xsl:with-param name="heading" select="$heading" /> </xsl:call-template> </row> <!-- repeat for the remainder of the original string--> <xsl:call-template name ="texttorows" > <xsl:with-param name ="StringToTransform" > <xsl:value-of select ="substring-after($StringToTransform,$CR)" /> </xsl:with-param> <xsl:with-param name="heading"> <xsl:value-of select="$heading" /> </xsl:with-param> </xsl:call-template> </xsl:when> <!-- string does not contain newline, so just output it--> <xsl:otherwise> <row> <xsl:call-template name ="csvtoxml" > <xsl:with-param name ="StringToTransform" select ="$StringToTransform" /> <xsl:with-param name="heading" select="$heading" /> </xsl:call-template> </row> </xsl:otherwise> </xsl:choose> </xsl:template> <xsl:template name ="csvtoxml" > <!-- import $StringToTransform--> <xsl:param name ="StringToTransform" select ="''" /> <xsl:param name ="heading" select ="''" /> <xsl:choose> <!-- string contains linefeed--> <xsl:when test ="contains($StringToTransform,',')" > <!-- Get everything up to the first carriage return--> <elem> <xsl:value-of select ="substring-before($heading,',')" /><xsl:text> </xsl:text> <xsl:choose> <xsl:when test="starts-with($StringToTransform, '&quot;')"> <xsl:value-of select ="substring-before($StringToTransform,'&quot;,')" /> </xsl:when> <xsl:otherwise> <xsl:value-of select ="substring-before($StringToTransform,',')" /> </xsl:otherwise> </xsl:choose> </elem> <!-- repeat for the remainder of the original string--> <xsl:call-template name ="csvtoxml" > <xsl:with-param name ="heading" > <xsl:value-of select ="substring-after($heading,',')" /> </xsl:with-param> <xsl:with-param name ="StringToTransform" > <xsl:choose> <xsl:when test="starts-with($StringToTransform, '&quot;')"> <xsl:value-of select ="substring-after($StringToTransform,'&quot;,')" /> </xsl:when> <xsl:otherwise> <xsl:value-of select ="substring-after($StringToTransform,',')" /> </xsl:otherwise> </xsl:choose> </xsl:with-param> </xsl:call-template> </xsl:when> <!-- string does not contain newline, so just output it--> <xsl:otherwise> <elem> <xsl:value-of select ="$heading" /><xsl:text> </xsl:text> <xsl:value-of select ="$StringToTransform" /> </elem> </xsl:otherwise> </xsl:choose> </xsl:template> </xsl:stylesheet>

No comments: