1 2 Previous Next 22 Replies Latest reply on Jun 23, 2010 9:14 AM by joshonefive

    export file(.CSV) has extra null record

    joshonefive

      Title

      export file(.CSV) has extra null record

      Post

      Hi guys,

       

      I am using FileMaker Pro 9.0v3 and have an export script that keeps creating an extra null record as the last record in my (.CSV) export.  This is an issue as the application I import the (.CSV) export file into aborts if there is a null record at the end.

       

      I appreciate any help or assistance.

       

      Thanks

       

      Josh

        • 1. Re: export file(.CSV) has extra null record
          philmodjunk

          I hope you've checked this already, but to rule out the obvious...

           

          Any chance you have an extra, blank record the set of records you are exporting?

          • 2. Re: export file(.CSV) has extra null record
            FentonJones

            I just tried this on 11 and got the same thing, blank line at the end. This is kind of normal for csv files, so I'm a little surprised the other software chokes; you'd think they'd adjust it. 

             

            Otherwise you could either post-process the file, to remove that last line, or use XML Export, with an XSL stylesheet. The following xsl, produces a csv file, no header line, no trailing line, Unix line endings.

             

             

            <?xml version='1.0' encoding='utf-8'?>
            <xsl:stylesheet xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
            exclude-result-prefixes="fmp" version="1.0"
            <xsl:output method="text"/>
            <xsl:template match="/">
            <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="concat('&#x22;', ., '&#x22;')" disable-output-escaping="yes"/>
            <xsl:choose>
            <xsl:when test="position()!=last()"><xsl:text>,</xsl:text></xsl:when>
            <xsl:when test="position()=last() and $posRow!=$lastRow">
            <xsl:text>&#10;</xsl:text>
            </xsl:when>
            </xsl:choose>
            </xsl:for-each>
            </xsl:for-each>
            </xsl:template>
            </xsl:stylesheet>

             

            • 3. Re: export file(.CSV) has extra null record
              joshonefive

              Hi Phil,

               

              Thanks for the quick response!

               

              My script finds 3 records that match a specific search criteria... I can see that 3 records are found after the script has been run in Filemaker and they are all not blank.

               

              Any other suggestions?

               

              Josh

              • 4. Re: export file(.CSV) has extra null record
                joshonefive

                Hi Fenton,

                 

                Thanks for the quick response!

                 

                They do adjust the the other software to accept the file; but i was hoping i could fix this on my end.

                 

                How would I post-process the file to remove the last line?

                 

                Thanks for the XLS info i'll give that a go and post my results.

                 

                Josh

                 

                 

                 

                • 5. Re: export file(.CSV) has extra null record
                  joshonefive

                  Fenton,

                   

                  Is "the following xsl" what i specify as an  HTTP request when i specify XSL options in the export?

                   

                  Thanks

                   

                  Josh

                  • 6. Re: export file(.CSV) has extra null record
                    ryan

                    I'm not running FM11, but curious if exporting the records as a Merge (.mer) file and then replacing the file extension with .csv would work?  I use this "trick" to bypass some other issues i've encountered when exporting from FMP to CSV.

                    • 7. Re: export file(.CSV) has extra null record
                      FentonJones

                      The XSL file can be either local (relative or absolute path), on a file server, or on a web server. I imagine the web server is the most logical if you have lots of client machines. Another method is to stick the XSL into a container field, and use Export (of 1 record) or Export Field Contents, to a known location (like the FileMaker application folder). It's a small file, takes almost no time to export.

                       

                      Best to test this on a backup copy of your file, until it is reliably working. If there is a problem with an XML Import (especially encoding mismatches), FileMaker can crash. I have not seen that recently however, likely they made it a bit more robust.

                      • 8. Re: export file(.CSV) has extra null record
                        FentonJones

                        Merge has an extra line in the export file also. As I said, the extra line is usually considered OK for csv. But it isn't, you've got to do something.

                        • 9. Re: export file(.CSV) has extra null record
                          joshonefive

                          rdowler,

                           

                          i'm not using fm11 either, but FM9.0v3.  i'll give your method shot and get back to you.

                           

                          is there a way you know of where i can test and ensure the (.csv) file does not have an extra null record? i would think as long as there is no carriage return or an extra line-feed at the end i should be OK.

                           

                          thanks,

                           

                          Josh

                          • 10. Re: export file(.CSV) has extra null record
                            joshonefive

                            Can't i just manually edit the file in notepad or a (.csv) file editor to remove the extra line? perhaps i am not understanding it correctly.

                             

                            thanks

                             

                            josh

                            • 11. Re: export file(.CSV) has extra null record
                              FentonJones

                              The XML Export just produces a text file. You can give it an extension of ".csv". Just click in where the extra line would be. It'll either be there or not (hopefully).

                              • 12. Re: export file(.CSV) has extra null record
                                philmodjunk

                                When I tested a sample file, the generated csv file does not have any extra blank line. It just looks like it does. Since any record in the csv file is terminated with a return character--including the last line, you appear to have an extra blank line at the end of the text file when you open it in a text editor. This is normal expected behavior as the return character at the end of the last record places the cursor at the beginning of the next empty row automatically. The only way to remove the "blank line" is to delete the return character from the end of your last row and this will make your last row different from all the others.

                                 

                                If the application that imports the data from this CSV file has a problem with that last return character, it has a significant bug that should be corrected so that you don't have to jump through such hoops just to import the data.

                                • 13. Re: export file(.CSV) has extra null record
                                  joshonefive

                                  Hi Phil,

                                   

                                  Thanks for the response.

                                   

                                  Here is response from the application managers:

                                   

                                  "There are actually null values after the last line-feed in the file.  The program is interpreting the null values as an additional record of invalid values, causing the job to abort."

                                   

                                  I'm not sure if this is what you're referring to is your last response unless the null value = the return carriage.

                                   

                                  I deleted the return character from the end of my last row making it different from the others.  According to the program managers, this will fix the issue but i'll have to wait until tomorrow to see if that is true.

                                   

                                  Thanks again,

                                   

                                  Josh

                                  • 14. Re: export file(.CSV) has extra null record
                                    philmodjunk

                                    Since "null value" means "no value", I'd be curious to know what "null values" they have detected after the last line feed.

                                     

                                    Keep in mind that this is an export format that has been used by filemaker since the earliest versions to exchange data with other applications. While I haven't had to deal with issues concerning CSV imports very often, this is the first case that I've seen where such a problem was "detected".

                                     

                                    Other questions I'd ask:

                                     

                                    Can your application import CSV data exported from other applications such as MS Access or Excel?

                                    1 2 Previous Next