5 Replies Latest reply on Apr 17, 2013 1:16 PM by philmodjunk

    Importing from Foxpro file

    jameshoty

      Summary

      Importing from Foxpro file

      Product

      FileMaker Pro

      Version

      12 v.3

      Operating system version

      OS X v.10.8.3

      Description of the issue

      Hi

      I am trying to convert my 20 years old customer who are using Foxpro database. While there are few challenges I encounter, the following issue puzzled me and want to know how to resolve it:

      [Background] I manage to import Customer.DBF into one of the table in FICI.fmp12, next I import Stock.DBF and 74 records were not imported. The reason could be due to the date field, so I clean up the wrongly entered year, eg. instead of 1991, the user enter 991.

      [Current Problem]
      I proceed to import STOCKDET.DBF total of 62,871 records are reported in Foxpro. I use the same Import command but 90% of the imported records are corrupted. I can see the contents but they are "shifted". This files has only 4 fields, Stock Code, Description, Weight, Unit Cost. I use Excel to open STOCKDET.DBF directly without problem, save to .XLS and import to FICI.fmp12 using the .XLS without problem. All records can properly populated. Not sure why FileMaker Import result in "corrupted" records? The other peculiar issue is that Excel imported on 62,628 records, instead of 62,871 as indicated in Foxpro.

      I understand that you may not be able to answer all the issues (Foxpro is beyond the scope here) but maybe I want to know why my import of stockdet.DBF fail?

      Thanks.

      Regards,
      James

        • 1. Re: Importing from Foxpro file
          philmodjunk

               Please explain what you mean by "Shifted". I can guess, but would prefer not to guess wrong.

               Import processes can filter out records that result in invalid data in a field, depending on the options you specify on the field and for the import, so that could explain the issue with the dates. If data is really messy, you sometimes have to import into text fields and then move the data into your final table after cleaning up the data issues.

          • 2. Re: Importing from Foxpro file
            jameshoty

                 I went to the customer site to backup the latest DBF files as wll as Export all the DBFs to XLS using the "older" MS-Excel.

                 The observations :

                 1. If I import directly from StockDet.DBF into fmp12, the record will "shift" (see attach "Record shifted after import.png"). 

                 2. If I import directly from the XLS (which I exported at my client office), it seems "perfect"

                 3. If I export using a higher version of spreadsheet (ay my home), the import imto fmp12 will not only cause record shifting but also partial import

                 4. I also notice that the COST has been "unknowingly" changed to 2.4999999 instead of the original cost of 2.50 when it is imported to fmp12 using the DBF file

                 In order to proceed with the data conversion, I have without choice to use the spreadsheet exported at my client office for the import into fmp12

                  

            • 3. Re: Importing from Foxpro file
              philmodjunk

                   The issue with 2.4999999 vs. 2.50 is due to how the two applications internally store floating point numbers and can be easily cleaned up post import.

                   Here's a report on that issue:

                   For More Information see:     Import from Excel decimal errors

                   This is one of many acknowledged bugs that can be found in the Known Bug List thread here in the Report an Issue section of the forum.

                   It can also be downloaded as a database file from:    https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

              • 4. Re: Importing from Foxpro file
                jameshoty

                     Thanks for sharing. does it mean that this is a bug that I am facing now. Another puzzling observation is that it is not "consistent" throughout the entire database, only some records will have the decimal places changed and I am unable to find a pattern to it. 

                      

                      

                • 5. Re: Importing from Foxpro file
                  philmodjunk

                       I don't consider it as a bug but rather a symptom of the fact that a floating point number such as 3.5 isn't stored in that format in a computer program.

                       As I understand it, It's stored in a kind of Binary Scientific notation where part of the storage space reserved for that number stores a binary "mantissa" and the rest stores an exponent.

                       If you remember your basic math, Mantissa times base raised to the exponent power produces your value. and since this is in binary, the software also has to convert the resulting value into base 10 for us humans to readn and understand what we are looking at.

                       Values such as 4.5 showing up as 4.99999999 are a result of the two programs using a slightly different number of places (significant figures or "precision") for the mantissa. To see why one value shows the change and not another would require seeing the precise string of ones and zeroes involved and then knowing how to "do the math" to produce the value you see on your computer screen.