1 2 Previous Next 16 Replies Latest reply on Jul 26, 2017 11:13 PM by beverly

    How to export to xsl properly with commas and carriage return?

    pleiades

      Hello,

       

      The description exported contains commas and carriage returns

       

      Whenever I try to export the records, I get a problems in the export

       

      There will be rows that is blank ( I think from carriage returns)

       

      and some additional columns from descriptions with commas

       

      The xls I use is below.

       

      Is it possible to include both commas and carraige return to the xls?

       

      Thank you!

       

      <?xml version='1.0' encoding='utf-8'?>
      <xsl:stylesheet xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
      exclude-result-prefixes="fmp" version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:output method="text"/>
      <xsl:template match="/">
      
      <!-- Header line, inserted only once; if not desired, remove -->
      <xsl:text>Name,Address,Birthday,Description,Status&#13;</xsl:text>
      
      <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">
      <xsl:variable name="posRow" select="position()"/>
      <xsl:variable name="lastRow" select="last()"/>
      <xsl:for-each select="fmp:COL/fmp:DATA">
      <xsl:value-of select="."/>
      <xsl:choose>
      <xsl:when test="position()!=last()">
      <xsl:text>,</xsl:text>
      </xsl:when>
      <xsl:when test="position()=last() and $posRow!=$lastRow">
      <xsl:text>&#13;</xsl:text>
      </xsl:when>
      <xsl:otherwise>
      </xsl:otherwise>
      </xsl:choose>
      </xsl:for-each>
      </xsl:for-each>
      
      </xsl:template>
      </xsl:stylesheet>
      

       

       

      Update1: I made a mistake, it should be xsl not xls

      Update2: The FMP is v14 and on Windows 7

        • 1. Re: How to export to xls properly with commas and carriage return?
          fmpdude

          Why don't you also attach a sample XML document which demonstrates the problem?

          • 2. Re: How to export to xls properly with commas and carriage return?
            beverly

            Hello, pleiades

            1. you say "xls" which is an extension for Excel. And you may be saving the result as a text file ('MyFile.xls').

             

            2. you show an "xslt" (or "xsl") which is an extension for Extensible Stylesheet Language Transformations. So the assumption is that you are exporting as XML and using this XSLT to transform (convert) the XML into text formatted at .csv

             

            3. the

            &#13;

            is the carriage return that is added to the end of each ROW (record) and the header ROW.

             

            4. the comma is already being added between the COL (fields)

             

            5. the number of columns is flexible, so that you can export 3 fields one time and 10 fields the next time. You would just need to change the header line.

             

            Q: are you asking to add more header row(s) with commas and return?

            Q: are you asking for some blank row(s) in the header? or in between the ROW (records)? or something else.

             

            Can you provide just a little more information, please?

            Beverly

            2 of 2 people found this helpful
            • 3. Re: How to export to xls properly with commas and carriage return?
              beverly

              ps. Please include the FMP version and your platform (and version) that you are using. Also a screenshot of your script to export may help us diagnose your problem.

              • 4. Re: How to export to xls properly with commas and carriage return?
                Jens Teich

                The additional blank lines are probably created here:

                 

                <xsl:for-each select="fmp:COL/fmp:DATA"> 

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

                <xsl:choose>

                 

                 

                FileMaker field values are included into the output and a linefeed before and after.  You can avoid this with

                 

                <xsl:for-each select="fmp:COL/fmp:DATA"><xsl:value-of select="."/><xsl:choose>

                 

                or

                 

                <xsl:for-each select="fmp:COL/fmp:DATA"> 

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

                <xsl:choose>

                 

                • 5. Re: How to export to xls properly with commas and carriage return?
                  beverly

                  Quite possibly, but the whitespace should be ignored in the XSLT. If OP uses the

                  <xsl:for-each select="fmp:COL/fmp:DATA"><xsl:value-of select="."/><xsl:choose>

                  and tests for us, that would be great!

                  Following that logic, all whitespace returns in the XSLT should be removed.

                   

                  Are you testing on Windows or MacOS or both?

                  Beverly

                  • 6. Re: How to export to xls properly with commas and carriage return?
                    user19752

                    pleiades , and which version?

                     

                    Latest FM16v2 is free from old bug on Windows that a line break become CRCRLF, but the bug remains on line break in field data...

                     

                    result of exported one record 2 fields, 1st field have 1CR2

                    changing the data to 1LF2 resulted same. I haven't found workaround.

                     

                    I guess "some additional columns from descriptions with commas" mean commas in field data become additional columns. If so, you need to quote data with something (usually double quote is used, and then you need to define how to "escape" double quote in data).

                    1 of 1 people found this helpful
                    • 7. Re: How to export to xsl properly with commas and carriage return?
                      pleiades

                      Hello, beverly!

                      Thank you for the help.

                       

                      1. you say "xls" which is an extension for Excel. And you may be saving the result as a text file ('MyFile.xls').

                       

                      I made a mistake, it should be xsl, the result is a csv

                       

                      2. you show an "xslt" (or "xsl") which is an extension for Extensible Stylesheet Language Transformations. So the assumption is that you are exporting as XML and using this XSLT to transform (convert) the XML into text formatted at .csv

                      you are correct

                      I wanted to include a header and from researching, an xsl can be used

                       

                      3. the

                      &#13;

                      is the carriage return that is added to the end of each ROW (record) and the header ROW.

                       

                      The is no problem in export if there is no carriage return in the field "Description"

                       

                      Example of carriage return in the Description, inside an edit box

                       

                      Big Paws

                      Brown Coat

                      Black Tails

                       

                      4. the comma is already being added between the COL (fields)

                       

                      There is no problem in export if there is no commas in the field "Desccription"

                       

                      Example of commas in the Description, inside an edit box

                       

                      Small paws, white fur, long brown tails

                       

                      5. the number of columns is flexible, so that you can export 3 fields one time and 10 fields the next time. You would just need to change the header line.

                       

                      The export is working properly except when the field "Description" contains commas or carriage return

                       

                      Q: are you asking to add more header row(s) with commas and return?

                      no, the headers are ok

                       

                      Q: are you asking for some blank row(s) in the header? or in between the ROW (records)? or something else.

                       

                      There are blank rows in between the ROW (records) and sometimes data are not in the correct columns

                       

                      Can you provide just a little more information, please?

                      The blank rows in between records are because of carriage return in the edit box, while the commas in the edit box cause the data to move to another column

                       

                      Would it be possible for the xsl to remove the carriage return in the Description and maybe change the commas into a space before exporting it so everything is in the right place?

                       

                      Is it possible to prevent a user from using some keyboard keys? when the user is on the edit box, he/she cannot use Enter key or the comma

                       

                      Thank you!

                      • 8. Re: How to export to xsl properly with commas and carriage return?
                        pleiades

                        Hi user19752,

                         

                        I'm on v14, removing the carriage return and commas in the edit box solve the export.

                         

                        are there any techniques you use to prevent a user from entering commas or carraige reture in edit box?

                         

                        Thank you!

                        • 9. Re: How to export to xsl properly with commas and carriage return?
                          user19752

                          One is, set enter/Return key as "go to next field" in layout mode. Commas can't be avoided by this.

                          Another is, use onObjectKeystroke trigger script. Returning 0 as script result avoid the keys.

                           

                          Auto-enter calculation can remove those characters after entered the field.

                           

                          I searched way on XSL, but it looks not simple to substitute characters on XSL.

                          1 of 1 people found this helpful
                          • 10. Re: How to export to xsl properly with commas and carriage return?
                            beverly

                            Thank you for the problem description!

                            Usually CSV also includes double-quotes around the text fields, so that carriage returns & commas are preserved as desired.

                             

                            So on line 15 (original XSLT) change to:

                            <xsl:value-of select="concat(&x34; , . , &x34;)" />

                            Yes this quotes every COL. it gets a little more complex if you only want certain fields quoted.

                            Try the fix above first.

                            Beverly

                            Sent from miPhone

                            1 of 1 people found this helpful
                            • 11. Re: How to export to xsl properly with commas and carriage return?
                              beverly

                              I searched way on XSL, but it looks not simple to substitute characters on XSL.

                              You are correct! Typically, it's a "lookup/replace" type template.

                              Re: [xsl] xslt replace special characters

                              The function

                              translate()

                              XPath, XQuery, and XSLT Function Reference

                              Does not help all times.

                              Beverly

                              • 12. Re: How to export to xls properly with commas and carriage return?
                                beverly

                                Sorry, I'm not in a position to test this. It might be:

                                <xsl:value-of select="concat('&x34;' , . ,' &x34;')" />

                                (literal double-quote to place around the field contents - COL/DATA)

                                BEVERLY

                                1 of 1 people found this helpful
                                • 13. Re: How to export to xls properly with commas and carriage return?
                                  user19752

                                  Constants can be outside of calclulation.

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

                                  and " in field data might need to be escaped with \ or doubling " on the cases.

                                  • 14. Re: How to export to xsl properly with commas and carriage return?
                                    pleiades

                                    Hi Beverly!

                                     

                                    Thank you very much, the &x34 should be change to &#34 to work.

                                     

                                    everything works wonderfully!  all records that are not properly exported before are now in their proper places.

                                     

                                    The working code i use is

                                    <xsl:value-of select="concat('&#34;' , . ,'&#34;')" />

                                    1 of 1 people found this helpful
                                    1 2 Previous Next