1 2 Previous Next 25 Replies Latest reply on Apr 5, 2016 12:52 AM by ManjitSandhu

    Help Formatting Columns during XSLT Transformation

    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

        • 1. Re: Help Formatting Columns during XSLT Transformation
          Korry

          (...sort of continuing from Help with XSLT Transformation and Formatting)

          Rather than try to detect the field type, do you trust the metadata already supplied in the FMPXMLRESULT?

          ...
            <METADATA>
              <FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="d_SchemeName" TYPE="TEXT" />
              <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_NumberOfMembers" TYPE="NUMBER" />
          ...

           

          If so, we can use it in the ouput:

          <?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="*"/>  <!--required for position() to work-->
           
            <xsl:template match="/">
              <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>
              <Workbook>
                <Styles>
                  <Style ss:ID="Heading">
                    <Font ss:Size="10" ss:Bold="1" />
                  </Style>
                </Styles>
                <Worksheet ss:Name="RFP">
                  <Table>
                    <Row>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Client Name</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Stock Exchange</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Industry Sector</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">Scheme Name</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Number of Active Members</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Deferred Members</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Total Funds Managed</Data></Cell>
                      <Cell ss:StyleID="Heading"><Data ss:Type="String">Open/Closed</Data></Cell>
                    </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 for every "COL" node in a "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:value-of select="fmp:DATA"/>
                </Data>
              </Cell>
              
            </xsl:template>
           
          </xsl:stylesheet>

          • 2. Re: Help Formatting Columns during XSLT Transformation
            ManjitSandhu

            Hi

            I thought it might be better to star afresh with this question, apologies if I should have continued with the last one?

            Anyway, thanks for you reply. Apologies, but I'm not sure I understand your question re: trusting the metadata?

            Before introducing the new code, the data was exported with the default field names, and then copied/pasted into templates with the correct headings. Does the METADATA take the assumption of column type from the first item in the column? If so, then I'd say no to trusting it as several columns contain similar data, ie a mix of numeric/alpha-numeric and/or blanks. I tried your code on one report and it gave me Problems during load, as I assume it set the data types incorrectly, so i guess that confirms my answer of no to trusting the METADATA :)

            I guess, ideally, I'd like to define the column types so I know how the output will look when opened - that way any numbers that should be strings will be strings and vice-versa, though i suspect it will break the files if data, such as my example about the trailing spaces, is applied to a number field.

            Thanks again

            Martin

            • 3. Re: Help Formatting Columns during XSLT Transformation
              Korry

              It sounds like you might have to hard-code the column names and types into your XSLT since you don't want to use what's supplied in the original XML. The order of fields you arrange in the FileMaker export dialog is maintained in the XML, and so is the metadata. If you exported ten fields, you'll get ten lines of metadata that correspond to the ten COL nodes respectively (and in the order you exported them).

              Look up the function "normalize-space" to clean up trailing spaces.

               

              • 4. Re: Help Formatting Columns during XSLT Transformation
                ManjitSandhu

                Many thanks Korry - reading your reply made me think harder and realised that I didn't know how the XML would know what the datatypes were, and define its METADATA, so looking again at the Export, I noticed the checkbox 'Apply current layout's formatting to exported data' wasn't checked, so the XML was (I assume) defining the field type based on the data received.

                By checking this and making the changes above to the XSLT, I was able to attempt to open each report, and fix a few issues which stopped the files loading, mainly where fields were previously calculated as numbers but following changes to the specification, were switched to strings, but the calculation wasn't updated to reflect the change. There were other issues that still means i can't open any of the files, but I'm *hoping* these are easy to resolve :)

                So the remaining issues I have are where numbers are formatted as currency (there's a couple of fields used), and the date columns where there is no date present. I did have dates formatted as 1 January 2015 which also failed, but have modified those on their respective layouts to be in the dd/mm/yyyy format, and that fixes those. For the currency issue, I did find this example, but wasn't sure how to implement it into the XSLT file?

                Thanks again for your help, it's much appreciated

                Martin

                • 5. Re: Help Formatting Columns during XSLT Transformation
                  ManjitSandhu

                  I've managed to get round the date issue by commenting out the line:

                  <xsl:when test="$fmType='DATE'">Date</xsl:when>

                  Oddly, the same field is used on two reports, one now shows correctly as dates in the format dd/mm/yyyy, yet the same field used in a second report shows in the format d mmmm yyyy? it just means the date fields are exported as text, which isn't ideal, but it works.

                  So i'm now able to open reports where the dates were the only issue, but not where numbers are formatted as currency - these are formatted that way as part of the specification so can't be changed.

                  Cheers

                  Martin

                  • 6. Re: Help Formatting Columns during XSLT Transformation
                    Korry

                    The field type metadata in the exported XML comes from the database field type. If you selected Text for a Date field for example, the metadata will reflect that. That's what I meant by "trusting" the field types. As long as the database field types are good, you can use the XML metadata section to define the spreadsheet cell types.

                    By commenting out the date condition, you effectively make Date types into String types. You may want to consider *not* selecting "Apply current layout's data formatting to exported data" so that all your date fields are formatted the way the XML describes (<DATABASE DATEFORMAT="M/d/yyyy" ...). You could then use some XSLT tricks to reformat dates into something compatible with the spreadsheet, rather than change all your relevant FileMaker layouts.

                    To restrict number fields to just digits and the decimal point (using the clever example in your link), revise <Cell>...</Cell> to something like this:

                        <Cell>
                          <Data ss:Type="{$ssType}">
                            <xsl:variable name="d" select="fmp:DATA" />
                            <xsl:choose>
                              <!--clean up number fields-->
                              <xsl:when test="$fmType='NUMBER'">
                                <xsl:value-of select="translate($d,translate($d,'0123456789.',''),'')" />
                              </xsl:when>
                              <!--pass other types unchanged-->
                              <xsl:otherwise>
                                <xsl:value-of select="$d" />
                              </xsl:otherwise>
                            </xsl:choose>
                          </Data>
                        </Cell>

                    • 7. Re: Help Formatting Columns during XSLT Transformation
                      ManjitSandhu

                      Thanks (again) :) 

                      The field type metadata in the exported XML comes from the database field type. If you selected Text for a Date field for example, the metadata will reflect that. That's what I meant by "trusting" the field types. As long as the database field types are good, you can use the XML metadata section to define the spreadsheet cell types.

                       OK, that makes more sense.

                      By commenting out the date condition, you effectively make Date types into String types. You may want to consider *not* selecting "Apply current layout's data formatting to exported data" so that all your date fields are formatted the way the XML describes (<DATABASE DATEFORMAT="M/d/yyyy" ...). You could then use some XSLT tricks to reformat dates into something compatible with the spreadsheet, rather than change all your relevant FileMaker layouts.

                      Yes, I'd noticed that, and that's really not what I wanted. Now I understand your first comment better, I'm going to untick the 'Apply current format...' option for one report and see what I get out. I've set the layouts back to how they were, mostly dd mmm yyyy, and just have to figure out how to ignore blank dates, but you've got me in the right direction, so thanks.

                      I've try out your suggestion for numbers, and it works perfectly where there are formatted numbers - many thanks.

                      I'm trying to resolve how to deal with the dates, b first re-instating the line

                      <xsl:when test="$fmType='DATE'">Date</xsl:when>

                      I exported the data as tab delimited text (just to confirm the output) and my date fields came out as dd/mm/yyyy, but when running it through the XSLT, each date, whether present or blank, fails - the log file produced when the file failed to open contained 528 errors, my data contained 132 empty tags and 396 data tags with a date present.

                      I tried:

                                <!--ignore blank dates-->
                                <xsl:when test="$fmType='DATE'">
                                  <xsl:if test="$d!=''">
                                    <xsl:value-of select="$d" />
                                  </xsl:if>
                                </xsl:when>

                      But whilst I realised I can't compare a date against a string, am confused why all dates failed the parser!

                      I'll keep looking, but thanks again for your help

                      Martin

                      • 8. Re: Help Formatting Columns during XSLT Transformation
                        ManjitSandhu

                        Just for info, I output a file as pure XML (so no transformation), and to me, it looks OK

                        <?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>

                        Obviously I have only shown a couple of records, one with a date and one without.

                        • 9. Re: Help Formatting Columns during XSLT Transformation
                          ManjitSandhu

                          I can't figure it out! I did note that the date format from the XML header 

                          <DATABASE DATEFORMAT="D/m/yyyy"

                           whereas the dates in the XML were DD/mm/yyyy (or blank) but still, the only way I can get output for reports that have one or more date columns is to not apply the type DATE to columns that are tagged as DATE types in the XML, such as the example above:

                          <FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="d_DateLatestClientFirst" TYPE="DATE"/>

                          The one report that doesn't have any dates present works perfectly!

                          Thanks

                          Martin 

                          • 10. Re: Help Formatting Columns during XSLT Transformation
                            Korry

                            Looks like Excel wants date type fields as YYYY-MM-DD.

                            Call a template that does something like this to convert the dates.

                            • 11. Re: Help Formatting Columns during XSLT Transformation
                              ManjitSandhu

                              Brilliant! I thought that was the answer, but not quite!

                              I've updated the cell tag to include the date code, and re-added my if test to try and only deal with non-blank data, but still get the same number of errors, ie. one for each row of data. I had to change the function to reformat the dates as mine are DD/MM/YYYY, but think I've modified it from the original correctly?

                                  <Cell>
                                    <Data ss:Type="{$ssType}">
                                      <xsl:variable name="d" select="fmp:DATA" />
                                      <xsl:choose>
                                        <!--clean up number fields-->
                                        <xsl:when test="$fmType='NUMBER'">
                                          <xsl:value-of select="translate($d,translate($d,'0123456789.',''),'')" />
                                        </xsl:when>
                                        <!--reformat date fields-->
                                        <xsl:when test="$fmType='DATE'">
                                          <!--but only deal with non-blank items-->
                                          <xsl:if test="$d!=''">
                                            <xsl:copy>
                                              <xsl:value-of select="concat(
                                              substring-after(substring-after($d,'/'),'/') , '-',                
                                              format-number( substring-before(substring-after($d,'/'),'/'), '00') , '-',
                                              format-number( number( substring-before($d,'/')), '00'))"
                                              />
                                            </xsl:copy>
                                          </xsl:if>
                                        </xsl:when>
                                        <!--pass other types unchanged-->
                                        <xsl:otherwise>
                                          <xsl:value-of select="$d" />
                                        </xsl:otherwise>
                                      </xsl:choose>
                                    </Data>
                                  </Cell>

                              Thanks so much for your help, much appreciated.

                              Martin

                              • 12. Re: Help Formatting Columns during XSLT Transformation
                                ManjitSandhu

                                Actually, when running it through an online transformer, it 'apears' to look correct:

                                <?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 At Risk</Data>
                                            </Cell>
                                            <Cell ss:StyleID="Heading">
                                               <Data ss:Type="String">Client At 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">
                                                  <COL xmlns="http://www.filemaker.com/fmpxmlresult">2012-01-23</COL>
                                               </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>

                                 Although am not sure why the date is shown as:

                                <COL xmlns="http://www.filemaker.com/fmpxmlresult">2012-01-23</COL>

                                I'll keep trying!

                                Martin 

                                • 13. Re: Help Formatting Columns during XSLT Transformation
                                  Korry

                                  You don't want to make a copy of the node, you just want the data from it. Remove the <xsl:copy> element.

                                                <xsl:copy>
                                                  <xsl:value-of select="concat(
                                                  substring-after(substring-after($d,'/'),'/') , '-',                
                                                  format-number( substring-before(substring-after($d,'/'),'/'), '00') , '-',
                                                  format-number( number( substring-before($d,'/')), '00'))"
                                                  />
                                                </xsl:copy>

                                  • 14. Re: Help Formatting Columns during XSLT Transformation
                                    ManjitSandhu

                                    You don't want to make a copy of the node, you just want the data from it. Remove the <xsl:copy> element.

                                                  <xsl:copy>
                                                    <xsl:value-of select="concat(
                                                    substring-after(substring-after($d,'/'),'/') , '-',                
                                                    format-number( substring-before(substring-after($d,'/'),'/'), '00') , '-',
                                                    format-number( number( substring-before($d,'/')), '00'))"
                                                    />
                                                  </xsl:copy>

                                    Great, thanks for pointing that out.

                                    Am still getting 'Problem Loading' and subsequent log file entry for every row when opening the file in Excel! This is driving me nuts! 

                                    I did consider setting a default date when there was no date:

                                                <xsl:if test="$d=''">
                                                  <xsl:value-of select="1900-01-01" />
                                                </xsl:if>

                                    But after doing that, I've just renamed the XLS file to XML and opened with textpad, and strangely I am seeing some 1998 in records where the default has been applied.

                                    I have re-visited the code to convert dates to the correct format, and think I've re-coded it correctly - can you confirm my logic is correct please?

                                                <xsl:if test="$d!=''">
                                                  <xsl:value-of select="concat(
                                                    substring-after(substring-after($d, '/'), '/') , '-',                
                                                    format-number( number(substring-before( substring-after($d, '/'), '/')), '00') , '-',
                                                    format-number( number(substring-before($d, '/')), '00'))"
                                                    />
                                                </xsl:if>

                                     But what I have just spotted is the date format from FileMaker is in fact dd mmm yyyy, ie 01 Jan 1900. So some googling and a few small tweaks gets me to this:

                                                <xsl:if test="$d!=''">
                                                  <!--get the three letter month-->
                                                  <xsl:variable name="m" select="substring-before(substring-after($d, ' '), ' ')" />
                                                  <!--reformat date from dd mmm yyyy to yyyy-mm-dd-->
                                                  <xsl:value-of select="concat(
                                                    substring-after(substring-after($d, ' '), ' ') , '-',
                                                    format-number(
                                                            string-length(substring-before(
                                                            'JanFebMarAprMayJunJulAugSepOctNovDec', 
                                                            substring($m, 1, 3))) div 3 + 1, '00') , '-',
                                                    format-number( number(substring-before($d, ' ')), '00'))"
                                                    />
                                                </xsl:if>

                                    But still getting the same loading errors.

                                    So I really need to confirm that the format the data is output is the same as it is displayed in on the FM layout, (see attached image)? 

                                    If that's the case, then I should be looking to reformat it as per the second solution, but that's not working either!

                                    ** Having re-read my earlier replies and exporting the results as XML (without transformation) confirms the data is in the DD/MM/YYYY format, so the original solution *should* work! **

                                    1 2 Previous Next