Home

Convert XML to CSV

Submitted by Richard Morrison on 10 March, 2009 - 10:06

Ever tried opening an XML file using Excel? Chances are Excel tries to import it but gets it very wrong. You can use XSL stylesheets and processing instructions to make XML pages that look nice in browsers and intelligently open in Excel, while retaining the machine-readability and flexibility of XML.

Head your XML file with the following directives. These tell any application viewing the file that it can be transformed with different templates depending on the capability of the application.

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl"
  href="template_for_csv.xslt" 
  alternate="yes" 
  title="Comma Separated Values for spreadsheets" ?>
<?xml-stylesheet type="text/xsl" 
  href="template_for_html.xslt" 
  alternate="no" 
  title="Display in Browser" ?>

Here's an XSL file suitable for template_for_csv.xslt. It takes any XML file and turns it into a reasonable CSV file.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output encoding="iso-8859-1" method="text" indent="no"/>
    <xsl:strip-space elements="*" />
 
    <xsl:template match="/">
        <xsl:apply-templates/>
    </xsl:template>
 
    <xsl:template match="/*/*">
        <xsl:call-template name="element">
            <xsl:with-param name="e" select="."/>
        </xsl:call-template>
    </xsl:template>
 
    <xsl:template name="element">
        <xsl:param name="e"/>
        <xsl:param name="level">1</xsl:param>
 
        <xsl:call-template name="manyof">
            <xsl:with-param name="text">,</xsl:with-param>
            <xsl:with-param name="howmany" select="$level - 1"/>
        </xsl:call-template>
 
        <!-- e can be any element -->
        <xsl:value-of select="name($e)"/>
        <!-- element's value next-->
        <xsl:text>,</xsl:text>
        <xsl:call-template name="output_data">
            <xsl:with-param name="d" select="$e/text()"/>
        </xsl:call-template>
        <xsl:call-template name="crlf"/>
 
        <!-- it may have attributes -->
        <xsl:for-each select="$e/@*">
 
            <xsl:call-template name="manyof">
                <xsl:with-param name="text">,</xsl:with-param>
                <xsl:with-param name="howmany" select="$level"/>
            </xsl:call-template>
            <xsl:value-of select="local-name(.)"/>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="output_data">
                <xsl:with-param name="d" select="."/>
            </xsl:call-template>
            <xsl:call-template name="crlf"/>
        </xsl:for-each>
 
        <!-- and sub-elements -->
        <xsl:for-each select="*[name() != 'scan']">
            <xsl:call-template name="element">
                <xsl:with-param name="e" select="."/>
                <xsl:with-param name="level" select="$level + 1"/>
            </xsl:call-template>
        </xsl:for-each>
 
 
        <xsl:if test="$level = 1">
            <xsl:call-template name="crlf"/>
        </xsl:if>
 
    </xsl:template>
 
    <xsl:template name="crlf">
        <xsl:text>&#13;&#10;</xsl:text>
    </xsl:template>
 
    <xsl:template name="output_data">
        <xsl:param name="d"/>
        <xsl:choose>
            <xsl:when test="contains($d, ',')">
                <xsl:text>"</xsl:text>
                <xsl:value-of select="normalize-space($d)"/>
                <xsl:text>"</xsl:text>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="normalize-space($d)"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:template>
 
    <xsl:template name="manyof">
        <xsl:param name="text"/>
        <xsl:param name="howmany"/>
        <xsl:choose>
            <xsl:when test="$howmany &gt; 0">
                <xsl:value-of select="$text"/>
                <xsl:call-template name="manyof">
                    <xsl:with-param name="text" select="$text"/>
                    <xsl:with-param name="howmany" select="$howmany - 1"/>
                </xsl:call-template>
            </xsl:when>
        </xsl:choose>
    </xsl:template>
 
</xsl:stylesheet>