Monday, August 23, 2010

Exporting ClearQuest records to Excel

If you need to export clearquest records to excel, the easy option is to use CSV format.  You can do that by using the "Save result set to file" option and select a delimiter of your choice.  Hoqwever, if one of your exported column is a multi line text, this may not provide the results you expect when you open the file in excel.

In that case you may want to export the data as xml file.  Use the "Rational ClearQuest Client"  (not the windows client) and click on the "export query results" icon above the query results pane.  This will open a window where you can select the export format.   In this window select the xml format and you can get the results as an xml file. (You can find detailed instruction on how to import here) However, the xml file is not very friendly to read.   You can create a simple xsl file and add a single line to the xml file to use the xsl file you just created to make the file more reader friendly.

Here is a sample xsl file you can use: (You can download a copy from here)

<?xml version="1.0" encoding="ISO-8859-1"?
> <xsl:stylesheet version="1.0" xmlns:xsl="">
<xsl:template match="/">
<html> <body> <h2>My ClearQuest Results </h2> <table> <xsl:for-each select="exportedResults/columnNames"> <tr bgcolor="gray"> <xsl:for-each select="columnName"> <th><xsl:value-of select="."/> </th> </xsl:for-each> </tr> </xsl:for-each> <xsl:for-each select="exportedResults/records/record"> <tr> <xsl:for-each select="field"> <td><xsl:value-of select="."/></td> </xsl:for-each> </tr> </xsl:for-each> </table> </body> </html> </xsl:template> </xsl:stylesheet>

You can add the following line to the generated xml so you tell the browser to use the xsl.  Make sure that the xsl and xml fiels are in the same directory.

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="cqTransform.xsl"?>
<!--Generated by ClearQuest Eclipse client  Mon Aug 23 14:43:15 EDT 2010-->

No comments: