A colleague has brought to my attention some rather odd behaviour when appending a certain Excel spreadsheet into a Visual FoxPro table.

I was wondering if anyone here had noticed similar behaviour and might be able to explain it?

The explanation is a little longwinded so please bear with me.

We have reproduced this behaviour on two machines, both running Visual FoxPro 9 (09.00.0000.2412) and Microsoft Excel 2003; one running Windows 2000 and one running Windows XP.

The Excel file we are trying to import contains one worksheet of around 4000 rows and 12 columns; the data is basic name and address fields with a few text based classifications and a URN column that contains text based or numeric data.

The Fox table we are appending into is comprised entirely of character fields.

On performing either an IMPORT FROM ? TYPE XL5 (or XLS/XL8) or APPEND FROM ? TYPE XL5 (or XLS) certain entries in the URN field have somehow transformed into an empty date string - the field is still a character data type but instead of containing the imported cell's contents it contains " / /" - this only appears to occur for those values that are entirely numeric but the values do not appear to have a consistent common factor. They are too long to be interpretted as numeric dates e.g. "1872362745". The affected cells appear to be formatted differently to unaffected cells within the spreadsheet; those unaffected are numerical data formatted strings whilst those affected are formatted as numbers.

Another common factor seems to be that all of the affected cells, when imported without issue, are preceded by 8 spaces after import (but not within the spreadsheet). Some values that I would expect to be effected are not but do come through preceded in the same manner.

My first impression was that these spaces were the cause but looking further at the data I find that not every cell preceded by these 8 spaces is affected.

As a further experiment I copied the contents of the affected column to a new spreadsheet and imported that - the problem did not show itself.

This led me to believe that the problem may be caused by the spreadsheet itself being faulty and so I copied the whole sheet and pasted it into a new workbook. On importing this fresh copy the problem showed itself again.

Finally I selected only those cells containing data (A1:M4409) and copied these to a new workbook. This imported without the problem manifesting.

We have tried importing the file into Access, finding no problem and eventually saved the sheet as a CSV to import it into Fox succesfully - although in these cases none of the previously affected cells were preceded with the 8 spaces.

Has anyone come across similar behaviour? Can anyone explain why it might occur? I am sure it is a problem within the sheet itself rather than the cells but I'm unsure how I can check such.

Many thanks for any help or advice anyone can provide.

Kind Regards,