xml - XSLT Transformation of Dates -
i'm struggling xslt deal columns of dates data mix of dates , blanks. data exported filemaker, know column types, , whilst (with elsewhere) have been able deal currency formatting , applying formatted headings, cannot work data 1 or more date columns present. i've output data xml it's apparent i'm starting with:
<?xml version="1.0" encoding="utf-8" ?> <fmpxmlresult xmlns="http://www.filemaker.com/fmpxmlresult"> <errorcode>0</errorcode> <product build="01-09-2015" name="filemaker" version="server 13.0v9"/> <database dateformat="d/m/yyyy" layout="" name="my_app.fmp12" records="528" timeformat="k:mm:ss "/> <metadata> <field emptyok="no" maxrepeat="1" name="d_clientname" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_oraclecode" type="number"/> <field emptyok="yes" maxrepeat="1" name="d_company_trustee" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_lead_consultant" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_targetmarketorganisation" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_stock_exchange" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_status" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_lostto" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_reasonlost" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_datelatestclientfirst" type="date"/> <field emptyok="yes" maxrepeat="1" name="d_clientatrisk" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_clientatriskcomment" type="text"/> <field emptyok="yes" maxrepeat="1" name="d_actions_last_12_months" type="text"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__current::d_revenue_half_year" type="number"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__current::d_revenue_full_year" type="number"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__plus_1::d_revenue_half_year" type="number"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__plus_1::d_revenue_full_year" type="number"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__plus_2::d_revenue_half_year" type="number"/> <field emptyok="yes" maxrepeat="1" name="client_revenue__plus_2::d_revenue_full_year" type="number"/> </metadata> <resultset found="528"> <row modid="3" recordid="1"> <col><data>record 1</data></col> <col><data>616375</data></col> <col><data>company</data></col> <col><data>other, an</data></col> <col><data>yes</data></col> <col><data></data></col> <col><data>active</data></col> <col><data></data></col> <col><data></data></col> <col><data></data></col> <col><data></data></col> <col><data></data></col> <col><data>n</data></col> <col><data></data></col> <col><data>64911.25</data></col> <col><data></data></col> <col><data>28987.5</data></col> <col><data></data></col> <col><data>29143.75</data></col> </row> <row modid="3" recordid="2"> <col><data>record 2</data></col> <col><data>603888</data></col> <col><data>trustee</data></col> <col><data>other, an</data></col> <col><data>yes</data></col> <col><data></data></col> <col><data>active</data></col> <col><data></data></col> <col><data></data></col> <col><data>01/01/1900</data></col> <col><data>low risk</data></col> <col><data></data></col> <col><data>n</data></col> <col><data></data></col> <col><data>2363.23</data></col> <col><data></data></col> <col><data>4325</data></col> <col><data></data></col> <col><data>14660</data></col> </row> </resultset> </fmpxmlresult> because of numerical data formatted currency, have had strip out non-numeric characters during transformation, wanted include dates dates in output, not strings. here xslt transformation:
<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/xsl/transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/tr/rec-html40"> <xsl:output indent="yes"/> <xsl:strip-space elements="*"/> <xsl:template match="/"> <xsl:processing-instruction name="mso-application">progid="excel.sheet"</xsl:processing-instruction> <workbook> <!-- define style heading row --> <styles> <style ss:id="heading"> <font ss:size="10" ss:bold="1" /> </style> </styles> <!-- set worksheet name --> <worksheet ss:name="dc_mg"> <table> <!-- populate , format headings --> <row> <cell ss:styleid="heading"> <data ss:type="string">client name</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">project code</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">company/trustee</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">lead consultant</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">target market organisation</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">stock exchange</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">status</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">lost to</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">reason lost</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">date latest client first</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">client @ risk</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">client @ risk comment</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">actions last 12 months</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2012</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2012</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2013</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2013</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2014</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2014</data> </cell> </row> <!-- add data each subsequent row --> <xsl:for-each select="fmp:fmpxmlresult/fmp:resultset/fmp:row"> <row> <xsl:apply-templates /> </row> </xsl:for-each> </table> </worksheet> </workbook> </xsl:template> <!--called every "col" node in "row"--> <xsl:template match="fmp:col"> <xsl:variable name="i" select="position()" /> <xsl:variable name="fmtype" select="/fmp:fmpxmlresult/fmp:metadata/fmp:field[$i]/@type" /> <xsl:variable name="sstype"> <xsl:choose> <xsl:when test="$fmtype='number'">number</xsl:when> <xsl:when test="$fmtype='date'">date</xsl:when> <xsl:otherwise>string</xsl:otherwise> </xsl:choose> </xsl:variable> <cell> <data ss:type="{$sstype}"> <xsl:variable name="d" select="fmp:data" /> <xsl:choose> <!--clean number fields--> <xsl:when test="$fmtype='number'"> <xsl:value-of select="translate($d,translate($d,'0123456789.',''),'')" /> </xsl:when> <!--ignore blank dates--> <xsl:when test="$fmtype='date'"> <!--am stuck goes here--> </xsl:when> <!--pass other types unchanged--> <xsl:otherwise><xsl:value-of select="$d" /></xsl:otherwise> </xsl:choose> </data> </cell> </xsl:template> </xsl:stylesheet> what i'm seeing when attempt open resulting file (post xslt transformation), problem loading excel , link log file displays. log file contains same 7 lines of code each record in data (528 records):
xml error in table reason: bad value file: c:\users\abc123def\appdata\local\microsoft\windows\temporary internet files\content.mso\84e8567e.xls group: cell tag: data attrib: type value: date so doesn't seem dates irrespective of whether valid or not. strip out line:
<xsl:when test="$fmtype='date'">date</xsl:when> but dates strings.
i've run through online transformation tool (using xslt) , gives me following:
<?xml version="1.0" encoding="utf-8"?> <?mso-application progid="excel.sheet"?> <workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" xmlns:html="http://www.w3.org/tr/rec-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel"> <styles> <style ss:id="heading"> <font ss:bold="1" ss:size="10" /> </style> </styles> <worksheet ss:name="dc_mg"> <table> <row> <cell ss:styleid="heading"> <data ss:type="string">client name</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">project code</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">company/trustee</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">lead consultant</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">target market organisation</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">stock exchange</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">status</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">lost to</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">reason lost</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">date latest client first</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">client @ risk</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">client @ risk comment</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">actions last 12 months</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2012</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2012</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2013</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2013</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue half year 2014</data> </cell> <cell ss:styleid="heading"> <data ss:type="string">revenue full year 2014</data> </cell> </row> <row> <cell> <data ss:type="string">record 1</data> </cell> <cell> <data ss:type="number">616375</data> </cell> <cell> <data ss:type="string">company</data> </cell> <cell> <data ss:type="string">other, an</data> </cell> <cell> <data ss:type="string">yes</data> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string">active</data> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="date" /> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string">n</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">64911.25</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">28987.5</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">29143.75</data> </cell> </row> <row> <cell> <data ss:type="string">record 2</data> </cell> <cell> <data ss:type="number">603888</data> </cell> <cell> <data ss:type="string">trustee</data> </cell> <cell> <data ss:type="string">other, an</data> </cell> <cell> <data ss:type="string">yes</data> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string">active</data> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="date">01/01/1900</data> </cell> <cell> <data ss:type="string">low risk</data> </cell> <cell> <data ss:type="string" /> </cell> <cell> <data ss:type="string">n</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">2363.23</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">4325</data> </cell> <cell> <data ss:type="number" /> </cell> <cell> <data ss:type="number">14660</data> </cell> </row> </table> </worksheet> </workbook> but again, still problems loading, , same errors in log file. help/advice gratefully received.
thanks
martin
Comments
Post a Comment