3 Replies Latest reply on Dec 2, 2015 11:13 AM by jendahl53

    Tab delimited export including header row

    jwilson

      Title

      Tab delimited export including header row

      Post

           Judging by some old discussions on the internet, this is evidently a long running problem. 

           I want to automate the export of data from FMP12 to a GIS programme in Tab delimited format including a header row, and the solution needs to work on both Mac and Win OS.  Manually I can go from FMP to excel to Tab-delimited to GIS, but its extremely laborious, especially as one of the fields contains commas which makes excel insert inverted commas in the text file, which then have to edited out in TextWrangler.

           Although I can get FMP to produce an appropriately structured tabbed text file, it doesnt include a header row.  Has anyone come up with a work around. I have seen several references to the use of xml plus a xslt file, but have not managed to get anything to work.

            

            

        • 1. Re: Tab delimited export including header row
          davidanders

               One export option is Merge, it includes field names.

               There are plugins that add features that should exist in Filemaker.  Some are free.

          http://filemaker-plugins.com/features/file-manipulation/

                

          • 2. Re: Tab delimited export including header row
            jwilson

                 OK, thanks for this.  Closer look at merge and it seems like a comma delimited structure. 

                 However the GIS program didn't like merge file but I found out that it was expecting CRLF at the end of each record which the mac version of FMP does not include.  Scraping the internet produced this handy script which can be used to convert any file with Mac CR to Windows style CRLF:

                 tell application "Finder"
                     
                     set aFile to (choose file)
                     
                     set txt to paragraphs of (read aFile)
                     
                 end tell



                 set {TID, text item delimiters} to {text item delimiters, (return & (ASCII character 10))} -- CRLF

                 set txt to txt as text

                 set text item delimiters to TID



                 tell application "Finder"
                     
                     try
                         
                         set ff to open for access aFile with write permission
                         
                         set eof of ff to 0
                         
                         write txt to ff
                         
                         close access ff
                         
                         return true
                         
                     on error
                         
                         try
                             
                             close access file aFile
                             
                         end try
                         
                         return false
                         
                     end try
                     
                 end tell

            • 3. Re: Tab delimited export including header row
              jendahl53

              I've been trying to do a tab-delimited export with headers for the longest time, and finally figured out how to do it, with the help of some other folks on this forum. Here is my solution (works on Win 10, FMP 12, but should be cross-platform). This method uses the Export as XML, and a .xsl file.

               

              Using a text editor (i.e. TextWrangler on the Mac, TextPad on Windows), create this file, save with an .xsl suffix (tabexport.xsl)

               

              <?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="/">

                      <xsl:comment>Insert static one time

                          data for import</xsl:comment>

                      <xsl:comment>This is the header row for records</xsl:comment>

                      <xsl:for-each select="fmp:FMPXMLRESULT/fmp:METADATA">

                          <xsl:for-each select="fmp:FIELD">

                                  <xsl:value-of select="@NAME"/>

                                  <xsl:if test="position()!=last()"><xsl:text>&#09;</xsl:text></xsl:if>

                          </xsl:for-each>

                      <xsl:text>&#13;</xsl:text>

                      </xsl:for-each>

                      <xsl:comment>This is the record set</xsl:comment>

                      <xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

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

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

                                  <xsl:if test="position()!=last()"><xsl:text>&#09;</xsl:text></xsl:if>

                          </xsl:for-each>

                          <xsl:if test="position()!=last()"><xsl:text>&#13;</xsl:text></xsl:if>

                      </xsl:for-each>

                  </xsl:template>

              </xsl:stylesheet>

               

              Note: &#09 is the encoded value for the tab character. &#13 is a return character.

               

              Now, when you want to export, use Filemaker's File > Export Records command, export as XML. When the dialog appears to specify XML and XSL options, use Grammar: FMPXMLRESULT, Check "Use XSL style sheet", as a file, and specify your .xsl file that you just created. Hit Continue..., specify your export fields, and this should produce a tab-delimited file with headers!

               

              --> John