Hack 41 Convert XML to CSV

figs/beginner.gif figs/hack41.gif

Turn your XML into CSV for use by applications that don't support XML.

In [Hack #21], you saw how to convert a comma-separated values (CSV) file to XML. This hack does the reverse, turning XML into CSV. This could be useful to you if you want to read XML data into an application that does not directly support XML but does support CSV, such as Excel 2000.

Let's go back to the output of CSVToXML, the file inventory.xml, after it has been cleaned up by [Hack #38] . Here is a portion of it (Example 3-19).

Example 3-19. A portion of inventory.xml
<?xml version="1.0" encoding="ISO-8859-1"?>

<!-- Generated using Dave Pawson's CSVToXML -->

<Inventory>

 <Line>

  <ItemNumber>line</ItemNumber>

  <Description>desc</Description>

  <Quantity>quan</Quantity>

  <Date>date</Date>

 </Line>

 <Line>

  <ItemNumber>1</ItemNumber>

  <Description>Oak chairs</Description>

  <Quantity>6</Quantity>

  <Date>31 Dec 2004</Date>

 </Line>

 <Line>

  <ItemNumber>2</ItemNumber>

  <Description>Dining tables</Description>

  <Quantity>1</Quantity>

  <Date>31 Dec 2004</Date>

 </Line>

 <Line>

  <ItemNumber>3</ItemNumber>

  <Description>Folding chairs</Description>

  <Quantity>4</Quantity>

  <Date>29 Dec 2004</Date>

 </Line>

 <Line>

  <ItemNumber>4</ItemNumber>

  <Description>Couch</Description>

  <Quantity>1</Quantity>

  <Date>31 Dec 2004</Date>

 </Line>

 ...

You can transform this file with the stylesheet csv.xsl, shown in Example 3-20.

Example 3-20. csv.xsl
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">



<xsl:output method="text"/>



<xsl:template match="Inventory">

 <xsl:apply-templates select="Line"/>

</xsl:template>

   

<xsl:template match="Line">

 <xsl:for-each select="*">

  <xsl:value-of select="."/>

  <xsl:if test="position( ) != last( )">

   <xsl:value-of select="','"/>

  </xsl:if>

 </xsl:for-each>

 <xsl:text>&#10;</xsl:text>

</xsl:template>

   

</xsl:stylesheet>

The output of this stylesheet is text (line 2). The template starting on line 8 matches all the Line elements in inventory.xml and processes its element children. The for-each element (line 9) works like a template within a template, instantiating its template for all the element nodes (*) it processes. value-of retrieves the string value of these nodes (line 10).

The if instruction (line 11) tests to see if the node is the last node by using the Boolean position( ) != last( ). The position() function returns an integer representing the position of the current node, and last() returns an integer representing the position of the last node. If the node is not (!=) the last node, value-of places a comma in the result. If it is the last node, this step is skipped.

After all the element node children of a given Line element are processed, the text element (line 15) outputs a newline. When the template matching Line exhausts its search, the transform is complete.

Apply csv.xsl to inventory.xml with this command:

xalan -o newinventory.csv inventory.xml csv.xsl

You have now round-tripped the data back to the original, as the output file newinventory.csv (Example 3-21) is identical to inventory.csv.

Example 3-21. newinventory.csv
line,desc,quan,date

1,Oak chairs,6,31 Dec 2004

2,Dining tables,1,31 Dec 2004

3,Folding chairs,4,29 Dec 2004

4,Couch,1,31 Dec 2004

5,Overstuffed chair,1,30 Dec 2004

6,Ottoman,1,31 Dec 2004

7,Floor lamp,1,20 Dec 2004

8,Oak bookshelves,1,31 Dec 2004

9,Computer desk,1,31 Dec 2004

10,Folding tables,3,31 Dec 2004

11,Oak writing desk,1,28 Dec 2004

12,Table lamps,5,26 Dec 2004

13,Pine night tables,3,26 Dec 2004

14,Oak dresser,1,30 Dec 2004

15,Pine dressers,1,31 Dec 2004

16,Pine armoire,1,31 Dec 2004

You can now display inventory.csv in Excel 2000 (Figure 3-19) or another application that can handle CSV files.

Figure 3-19. newinventory.csv in Excel 2000
figs/xmlh_0319.gif


3.12.1 See Also

  • XSLT Cookbook, by Sal Mangano (O'Reilly), pages 155-170.

  • Danny Ayers' Java code XMLToCSV: http://www.dannyayers.com/old/code/CSVtoXML.htm