AnsweredAssumed Answered

Help Formatting Columns during XSLT Transformation

Question asked by ManjitSandhu on Apr 27, 2015
Latest reply on Apr 5, 2016 by ManjitSandhu

Title

Help Formatting Columns during XSLT Transformation

Post

I recently asked a question about difficulties I was having with transformation of data from XML via FileMaker, and with some really useful replies, have almost got to where I want to be. I've improved my solution to reformat columns that can be cast as a number as numbers, but I have a specific column that some values can be cast, others not, but I really want the column left as a string.

Here's the code that handles the reformatting:

<xsl:template match="fmp:COL">
  <xsl:variable name="type">
    <xsl:choose>
      <xsl:when test="number(.) = .">Number</xsl:when>
      <xsl:otherwise>String</xsl:otherwise>
    </xsl:choose>
  </xsl:variable>
  <Cell><Data ss:Type="{$type}"><xsl:value-of select="fmp:DATA"/></Data></Cell>
</xsl:template>

I was wondering if this could be repeated for date columns? Something like:

<xsl:when test="date(.) = .">Date</xsl:when>

And my main question - I have one column in a couple of the reports that is an identifier. For some records (probably 75%) they are alphanumeric, so adhere to the string format, but there are some that are all numeric - I want this specific column to remain as a string, so be excluded from the reformat.

As an example, for one record, the identifier is XYX000000000116, but for another it is 188954598240166. Once refomatted to a number the latter identifier displays as 1.88955E+14, but obviously it needs to remain as it's actual value as a string.

One last question...I have found there to be some issues with one column of data where all values are numeric, but in a few cases the user has appended a space to the value, i.e. 62296 has been entered as '62296 ', so whilst it parses as numeric, it fails to allow the file to open as the xml is incorrect:

<Data ss:Type="Number">622996 </Data>

Can the numeric test exclude items like this or do I have to validate the input to not allow the blank?

Any help gratefully received.

Regards

Martin

Outcomes