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.
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
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
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.
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.