1 2 Previous Next 15 Replies Latest reply on May 29, 2014 9:55 AM by deninger

    Export Field or Export Records to .csv w/ windows line feeds

    deninger

      I am trying to create a file for a company (for Electronic Data Interchange) from Filemaker Pro 13 on a Mac. I have run into a problem with the files when I open them in Windows (the platform the other company works with the file in)

       

      When I look at the exported data on my Mac, it appears that each record is on its own line (like I expect).

       

      When I look at the exported data on Win 7 (notepad), the data is all run together in one very long line.

       

      I have searched for solutions both on the net and here, and have not managed to find any workaround. So far, I have tried.

       

      Export Records (Windows ANSI) specifiying the fields to be exported within the script line

      Export Records (ASCII DOS) specifiying the fields to be exported within the script line

      Export Records (Windows ANSI) exporting a single field containing the entire record in csv format

      Export Records (ASCII DOS) exporting a single field containing the entire record in csv format

      Creating a text object as a variable using CHAR(10) and CHAR(13) alone and in combination) at the end of each line and exporting this with Export Field

       

      Any othe suggestions or advice?

        • 1. Re: Export Field or Export Records to .csv w/ windows line feeds
          SteveMartino

          I'm trying to solve this problem myself.  Need to have the file onto an SD Card to use for an onboard propietary (old) truck computer.  The only work around, which works fine, but isnt the best, is on the last script step I select the entire field, then copy.  Then I open a notepad text file and paste.  Export Records, and Export Field Contents, don't seem to bring the LF and CR with them.

          • 2. Re: Export Field or Export Records to .csv w/ windows line feeds
            beverly

            Single field export might be part of the problem.

            Here's an article about a possible solution:

             

                 <http://filemakerhacks.com/2012/09/23/export-field-contents-as-utf-8/>

             

            This type of "export" preserves whatever characters you put into the field, including CRLF if needed. XML does not convert these. Something similar can be used with many field(s) and/or record(s), using an appropriate XSLT.

             

            This is one of my XSLT for creating ".csv". It quotes all the field values and adds the appropriate end-of-line (you need $#13;$#10; ):

             

            <?xml version="1.0" encoding="utf-8"?>

            <xsl:stylesheet

                 version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

                 xmlns:fm="http://www.filemaker.com/fmpxmlresult"

                 exclude-result-prefixes="fm">

                 <xsl:output method="text" version="1.0" encoding="utf-8" indent="no" />

                 <!-- end-of-line = carriage return, change as needed -->

                 <xsl:variable name="eol"><xsl:text>&#13;</xsl:text></xsl:variable>

                 <xsl:template match="/">

            <!-- Header line, inserted only once; if not desired, remove -->

                      <xsl:text>Name,One,Two,Date_Created,TS_Created</xsl:text><xsl:value-of select="$eol" />

            <!-- LOOP THROUGH THE RECORDS (as ROW) -->

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

            <!-- LOOP THROUGH THE FIELDS (as COL/DATA) -->

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

            <!--

                 simplified with &x22; (also know as double-quote character)

                 instead of text, value, text, just CONCATENATE

                 bvoth 2008-11-11

            -->

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

            <!-- ADD A COMMA AFTER A FIELD IF IT IS NOT THE LAST -->

                                <xsl:if test="position() != last()"><xsl:text>,</xsl:text></xsl:if>

                           </xsl:for-each>

            <!-- BY DEFAULT THE END OF THE RECORD/ROW GETS A RETURN -->

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

                      </xsl:for-each>

                 </xsl:template>

            </xsl:stylesheet>

             

            Note also that the first column (header) is the fields named as you want (the same order as your export). If you have related field export, then that's something different.

             

            Plug-ins may also assist you with the type of export you need.

            Beverly

            • 3. Re: Export Field or Export Records to .csv w/ windows line feeds
              scodoc

              The problem is that Filemaker using a single CR to terminate text lines. In Notepad it appears a a square box character in that long line. Unfortunately the developers at Filemaker seem to live in an Apple way of doing things only world. The rest of the world uses CRLF as is the proper way to terminate a line for greatest compatibility. CR means carriage return, or move to the beggining of the current line. LF is Line feed and means move cursor down one line. In a proper text terminal a LF by itself wil mocve down one line without oving to the beggining of the line. SImilar with CR will move to the beggining of the line without moving down a line.

               

              From a programming perspective, using a single token character to indicate the end of a line is more efficient and manageable. Filemaker uses a CR, Unix use LF, most programs interpret a CRLF pair as a single operation and internally replaces the pair with a single character such as CR or LF. The trick is to do a conversion back to CRLF when writing the file.

               

              A way to do this is to search and replace the CR (That Pi 'ish looking symbol) character with

               

              & Char(13) & Char(10)  &

               

              Note the catenate operaters. A special case to consider is the end of the text. You do not want that last catenate. Often it is best to have a final CRLF as the last characters in a file.

               

              ADDED:

               

              One other note. since you are operating within a text string, you will probably have to unquote then quote to use the replacement. use the slash quote to use it in the replacement string.

              • 4. Re: Export Field or Export Records to .csv w/ windows line feeds
                beverly

                If you see my article, I explain this. That is why I use XML export with XSLT to give me the characters in fields (even calculated). In addition, characters can be added by the XSLT as needed.

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                • 5. Re: Export Field or Export Records to .csv w/ windows line feeds
                  deninger

                  Beverly,

                   

                  This worked (as soon as I realized that your XSLT file only added the CR (&#13;) and not both a CRLF (&#13;&#10;). Thank you for your response. It has encouraged me to learn a little bit more about XLST (something that I knew little of before)

                  • 6. Re: Export Field or Export Records to .csv w/ windows line feeds
                    beverly

                     

                     

                    These are simple examples and unless you have really complex XML schema to output, may be all you need to learn!

                     

                    -- sent from my iPhone4 --

                    Beverly Voth

                    --

                    • 7. Re: Export Field or Export Records to .csv w/ windows line feeds
                      deninger

                      One last bit of housekeeping...

                       

                      The exported file using this technique does not have a file extension (despite my export path specifying one explictly by concatonating the path and document name with ".csv").

                       

                      While not a deal breaker, it really would be nice to know why...

                      • 8. Re: Export Field or Export Records to .csv w/ windows line feeds
                        beverly

                        one last bit of housekeeping:

                             you're not exporting correctly to make the extension as you need it!

                         

                        How are you naming your export? Manually? scripted? are you setting a variable first and using the variable in the dialog? You need to NAME the file, fully even if you use XML export method.

                         

                        I do this all the time with XML. Just make it the extension you need....

                         

                        Beverly

                        • 9. Re: Export Field or Export Records to .csv w/ windows line feeds
                          deninger

                          Beverly,

                           

                          I am setting the output path for the Export Records script step with a variable ($path)

                           

                          This variable is a concatonated string of Get (DocumentPath) & fileNameFromAField & ".csv"

                           

                          The variable resolves properly in Data Viewer (when debugging) to:

                           

                          /Users/mdeninger/Documents/examplefilename.csv

                           

                          But when the file is created upon export, it is just called "examplefilename"

                           

                          At first I thought that I had forgotton to unhide extensions in the Finder Preferences, but that was not the case. Using Terminal.app, the file clearly does not have an extension at all.

                          • 10. Re: Export Field or Export Records to .csv w/ windows line feeds
                            beverly

                            the "path" needs the prefix:

                                 "file:"

                                 "filewin:"

                                 "filemac:"

                            ....

                            I wonder if that's making the document extension less?

                             

                            Also, any other apps running or plug-ins?

                             

                            What are the permissions on the Documents folder/directory? Might you need to run an OS utility to fix permissions or something?

                             

                            Beverly

                            • 11. Re: Export Field or Export Records to .csv w/ windows line feeds
                              deninger

                              I figured out why I was not getting the extension. The file name comes from a passed parameter into the script, and Get (ScriptParameter) was receiving a LF so my path was resolving to:

                               

                              "filemac:/Users/mdeninger/Documents/filename

                              .csv"

                               

                               

                              The extra line feed is coming from a field that was populated via a SQL Server import (and was entirely unexpected). Sometimes I forget just how much I love computers!

                              • 12. Re: Export Field or Export Records to .csv w/ windows line feeds
                                beverly

                                I hate when that happens! I tend to put in a "substitute(... ; "¶" ; "") when setting the path. I also try to remove things that may break (":" , "/" , basically any standard path delimiters). I don't like spaces either.

                                 

                                Beverly

                                • 13. Re: Export Field or Export Records to .csv w/ windows line feeds
                                  DamianKelly

                                  If ever there was a job for a custom function (either to simply remove illegal characters or, if you are playing on the interweb, swop them for the relevant encoding)

                                  • 14. Re: Export Field or Export Records to .csv w/ windows line feeds
                                    Malcolm

                                    Were you getting invisible files (.csv) being created too? I’m wondering if the command is happy to work with a list.

                                     

                                    malcolm

                                    1 2 Previous Next