1 2 Previous Next 15 Replies Latest reply on Jun 30, 2012 9:34 AM by disabled_JackRodgers

    Bug importing dBase data into File Maker Pro 10 Demo (OSX)

    mattb

      Summary

      Bug importing dBase data into File Maker Pro 10 Demo (OSX)

      Description of the issue

      FileMaker Product(s) involved 10.0v3 [demo version] Operating System(s) involved OSX 10.5.6 Detailed description of the issue When importing dBase files into File Maker Pro database, the first letter of each paragraph is missing, when the sourcefield is a dBase Memo field (comes from the .DBT file) Exact steps to reproduce the issue Using a newly-created FMP database: 1) File pulldown menu, select Import -> File2) In the Show popup, select dBase files3) Select a dBase file that has Memo fields in it (DBF/DBT pair)4) In the Import Field Mapping window, select the name of table from the Target popup5) Click the Import button Expected Result I would expect data to be imported 100% intact from the source database, not dropping characters! Actual Result 6) All text data imported from a dBase Memo field is missing the first character in each paragraph  Exact text of any error message(s) that appeared There were no error messages Any additional configuration information/troubleshooting that is relevant to the issue The original source data comes from Visual Foxpro databases.  Visual Foxpro is able to create dBase files, so thefiles I am using for import are dBase files created by Visual Foxpro.  These dBase files have intact data in them,which leads me to believe there is a bug in the File Maker Pro import procedure. Any workarounds that you have found I have not found any and would greatly appreciate knowing about any workarounds to this issue.   Matt Bloomfieldmatt.bloomfield@gmail.com(310) 397-8232  

        • 1. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
          TSGal

          mattb:

           

          Thank you for your post.

           

          I have forwarded your information to our Development and Software Quality Assurance (Testing) departments for review.  When more information becomes available, I will post here.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
            TSGal

            mattb:

             

            The following information was forwarded to me regarding Visual FoxPro and memo fields:

             

            http://www.foxite.com/archives/how-to-import-memo-field-into-filemaker-0000032147.htm

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
              mattb
                

              The following information was forwarded to me regarding Visual FoxPro and memo fields:

               

              http://www.foxite.com/archives/how-to-import-memo-field-into-filemaker-0000032147.htm

               

              TSGal

              FileMaker, Inc. 


              TSGal:

               

              Thanks for the reply.  I had already seen the above thread before I posted here and while pursuing the ODBC method of import might be worthwhile (especially if it works correctly), the reply given in that message was to a different problem than mine.  

               

              The problem of the person writing that message in 2003 using Visual Foxpro 6 (and whatever version of FMP was current in 2003) was that all of his memo fields on the Visual Foxpro side wound up inside of a single text field in FMP.

               

              In my case, using Visual Foxpro 9 and FMP 10, the memo fields stay separated just fine using the import from file method.  My problem is that the first character of each paragraph of the imported memo field gets dropped by the time it arrives in FMP.  So what I'm talking about is an actual data loss problem and I believe there is still a bug in the current FMP 10 import from file function for dBase files. 

               

              I will look into the ODBC workaround but I would also appreciate if my specific situation can be addressed.  There's no reason the import from file function

              shouldn't work 100% correctly.  

               

              Thanks for your attention to this...

               

               

               

              Matt Bloomfield

               

               

               

              • 4. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                TSGal

                mattb:

                 

                Looking at the dBASE file structure, it appears that memo fields allow up to 4096 characters - ten blocks of 512 characters.  Also, since dBASE originally appeared on MS-DOS, lines are separated by a carriage-return/line-feed pair.  It sounds like FoxPro may be separating lines with only a carriage-return, which would account for the first character of each paragraph being dropped.  Without seeing the .DBT file, can you confirm the first character of the memo field is NOT dropped?  That is, the first character is dropped only on subsequent lines?

                 

                TSGal

                FileMaker, Inc. 

                 

                 

                • 5. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                  mattb
                    

                  TSGal:

                   

                  >Looking at the dBASE file structure, it appears that memo fields allow up to 4096 characters - ten blocks of 512 characters.  Also, since dBASE originally >appeared on MS-DOS, lines are separated by a carriage-return/line-feed pair.  It sounds like FoxPro may be separating lines with only a carriage-return, >which would account for the first character of each paragraph being dropped.  Without seeing the .DBT file, can you confirm the first character of the memo >field is NOT dropped?  That is, the first character is dropped only on subsequent lines?

                   

                  Yes, I can confirm this, but only in the resulting FMP import.  That is, the first character of the first paragraph is intact, but the first character of any succeeding paragraphs is dropped.  So if a memo field contains 4 paragraphs, the 1st paragraph is fine, the 2nd paragraph is missing the first character, the 3rd paragraph is missing the first character, and the 4th paragraph is missing the first character.  However, when I convert the Foxpro files (DBF/FPT pair) from Visual Foxpro into a dBase DBF/DBT pair, and look at these converted files, everything is intact when viewed with VFP.  It is only when the DBF/DBT pair is imported into FMP do you see the characters dropping out.  I could use the MS-DOS DEBUG utility to examine the dBase files to see whether the line feed (OA) character is indeed missing between paragraphs - that sounds like a likely theory.  But if true, then what to do about this?  It would be as laborious (or more) to insert line feed characters into the proper spot in the dBase files before importing into FMP as it would be to replace missing characters in the FMP file after importing.   

                   

                   

                   

                  Matt Bloomfield

                  (310) 397-8232

                  matt.bloomfield@gmail.com 

                  • 6. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                    TSGal

                    mattb:

                     

                    Thank you for the confirmation.

                     

                    First of all, the DBT file with ten blocks of 512 characters should make 5120 characters, but I see differing accounts of this online.

                     

                    It appears that FoxPro is saving the dBASE memo field with a carriage return without the line feed.  If the line feed is missing, inserting a line feed into a file is not an easy task.  Instead, as a workaround, add a space at the beginning of each paragraph.  This way, the expected line feed is replaced by a space, and all of your information remains intact.

                     

                    I have forwarded this information to our Development and Software Quality Assurance (Testing) departments for review.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                      mattb
                        

                      TSGal:

                       

                      It appears that FoxPro is saving the dBASE memo field with a carriage return without the line feed.  If the line feed is missing, inserting a line feed into a file is not an easy task.  Instead, as a workaround, add a space at the beginning of each paragraph.  This way, the expected line feed is replaced by a space, and all of your information remains intact.

                      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                       

                      Nope.  I conducted some experiments this morning and your theory does not bear out.  I looked directly at the dBase DBT file with the MS-DOS Debug

                      utility and found (as expected) 2 pairs of CR-LF entries between paragraphs:  0D 0A 0D 0A.  

                       

                      It seems that it is FMP that has trouble with the 0A line feed entries if they are immediately followed by data.  For the 4 bytes above, I substituted 20 20 0D 0D (2 spaces followed by 2 carriage returns) and it imported perfectly into FMP.  Of course this is not a practical workaround as it involves replacing bytes with the Debug utility.

                       

                      I tried what you suggested - adding a space before the first letter of each paragraph (effectively 0D 0A 0D 0A 20 between paragraphs) and that worked in the sense of not dropping any data  but it resulted in 2 lines of blank space between paragraphs instead of 1.

                       

                      With those results in mind, I then tried a modified version of your suggestion:  remove one line between paragraphs and then add a space.  This results in a 0D 0A 20 in between paragraphs.  This method also imported perfectly into FMP.

                       

                      But since pressing the Enter key when entering text always generates a CR-LF (0D 0A) pair of bytes into the file (and that is the industry standard), it's now more clear to me than ever that the FMP import routine for dBase files is what's at fault.  I shouldn't have to remove lines and add spaces to make the import work right.  And while now I've got a "workaround" where data doesn't get dropped, it makes for a giant headache in converting my VFP files to FMP.  This is for commercial software with tons of files to convert and would be very labor-intensive should I undertake it.  And even if I undertook this labor-intensive activity for files we distribute with the program, there's still the issue of what to do with customer-created VFP files should they wish to convert to a FMP version of our program.  

                       

                      Perhaps I should investigate the ODBC import method as suggested in the post you referred me to.  If it can make a clean import of a VFP file without having to mess with the data as it currently stands, that would be far more preferable.

                       

                       

                       

                      Matt Bloomfield

                       

                       

                       

                       

                      • 8. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                        TSGal

                        mattb:

                         

                        Thanks for the information.  Obviously, my theory was incorrect, but I did see something in your post that stuck out....

                         

                        "0D 0A 0D 0A" should create two lines, but in your case, only one.  It would seem that the first character after a "0D 0A" pair would be ignored, but if true, then the second "0D" would be ignored, and 0A followed by the first letter of your paragraph would appear.  Strike that.

                         

                        In any case, I have forwarded your findings to our Development and Software Quality Assurance (Testing) departments for review.

                         

                        Until this is changed, either use a space at the beginning of each paragraph (which modifies your data), or use ODBC import.

                         

                        TSGal

                        FileMaker, Inc. 

                        • 9. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                          mattb
                            

                          TSGal:

                           

                          Until this is changed, either use a space at the beginning of each paragraph (which modifies your data), or use ODBC import.

                          -------------------------------------------------------------------------------------------------------------------------------------------

                           

                          I tried the ODBC import method today and FYI, here is what I found:

                           

                          1.  It will import directly from Visual Foxpro files, without having to convert them first to generic dBase format, so that's good.

                           

                          2.  It doesn't drop any data, so that's very good.

                           

                          3.  There is a bug using this method, however.  It doubles up on the CR-LFs.  That is, where in the VFP file there is a single blank

                          line between paragraphs, when imported into FMP using this method, there are now 2 blank lines between paragraphs.

                           

                          Better an extra blank line than missing characters, I suppose.  The extra blank line can be removed by removing the single

                          blank line on the VFP end before importing, but I'm trying to avoid doing such things like the plague.  

                           

                           

                           

                          Matt Bloomfield

                           

                          • 10. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                            philmodjunk
                               You might also be able to use something like Substitute (YourTextField; "¶¶"; "" ) in a replace field contents or looping script to strip out the extra blank lines after importing.
                            • 11. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                              TSGal

                              mattb:

                               

                              Our Testers have requested a sample DBF and DBT file so they can replicate the problem.  I have sent you a private message (top of this page - right side - X Messages) with instructions where to send the files.

                               

                              TSGal

                              FileMaker, Inc. 

                              • 12. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                                TSGal

                                mattb:

                                 

                                I received your files, replicated the problem, and sent the files and my findings to the appropriate tester.  Thank you.

                                 

                                When more information becomes available, I'll let you know.

                                 

                                TSGal

                                FileMaker, Inc. 

                                • 13. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                                  dan206

                                  I am in the process of converting a solution from FM6 to FM10, and have found that FM6 could import DBF/DBT files with no problem, yet the FM10 "hangs" if there is a Memo field in the import.

                                   

                                  Has anyone found a method to allow FM10 to import properly?

                                   

                                  Thank you

                                  Dan

                                  • 14. Re: Bug importing dBase data into File Maker Pro 10 Demo (OSX)
                                    mattb

                                     


                                    DanA wrote:

                                    I am in the process of converting a solution from FM6 to FM10, and have found that FM6 could import DBF/DBT files with no problem, yet the FM10 "hangs" if there is a Memo field in the import.

                                     

                                    Has anyone found a method to allow FM10 to import properly?

                                     

                                    Thank you

                                    Dan


                                     

                                     

                                    Dan,

                                     

                                    I "solved" (worked around is a better description) my issue by using ODBC import with the ODBC Foxpro driver rather than using File import and selecting dBase file type.  However, I never had any issues with "hanging" - the import always worked, although it would, as I described, chop off the first letter of each paragraph in an imported memo field.  And this was with FMP 10.   I've upgraded to FMP 11 subsequently, but the issue remains.  I suppose your problem could be the source of DBF/DBT files.  Mine were originally Foxpro files that were "converted backwards" from Foxpro to dBase DBF/DBTs.  What is the source of your DBF/DBT files?

                                     

                                     

                                     

                                    Matt Bloomfield


                                    1 2 Previous Next