Unexpected behaviour with APPEND FROM ... TYPE XL8

[Mike Lewis]

A user has reported some unexpected behaviour when importing an Excel file into VFP. I wonder if anyone has seen this before, or can throw a light on it.

The Excel file has three columns (A, B and C). Column B contains a social security number - exactly nine digits. The cell format is General.

This is a simplified version of what my program does:

CREATE CURSOR csrData (F1 C(32), F2 C(32), F3 C(32))
SELECT csrData
APPEND FROM <the Excel file> TYPE XL8

Now, you would expect the middle field (F2) to contains nine digits, padded with spaces. What it actually contains is a NULL byte, that is, CHR(0), followed by eight digits, padded with spaces. The final digit of the SSN is lost.

I can't see anything unusual about the Excel file. I have compared with others that the program has successfully imported, and it looks perfectly normal.

I have tried the following:

1. I ran the above code in the command window, and saw the same behaviour, this eliminating any glitches elsewhere in my application.

2. I tried using Automation to extract the SSNs. The values were correct.

3. I tried using the Office 2007 ODBC driver to extract the SSNs. The values were again correct.

In other words, it is only when using APPEND FROM that I get the problem.

In view of point 3 above, I wondered if the file was originally created in Excel 2007 or later and saved to Excel 95 format (it couldn't have been saved to 97 - 2003 format, because APPEND FROM wouldn't have been able to read it). Unfortunately, the user doesn't know where the file came from or what version was used to produce it.

Has anyone ever seen anything like this? I have been importing Excel files into VFP for years, and have never come across it before.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike,

if the source was Excel2007 this isn't a new issue. The backward compatibility formats Excel 2007 or 2010 produces are not binary compatible, older Excel version can read them, but foxpro can't append them. That's why we recommend to change to ODBC or OLEDB rpoviders or automation to read from excel files for quite some time now.

You know yourself, that Excel 2007 woud be a source of the problem, so the main point is finding out where the excel came from, really.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

Thanks for your comments.

I haven't been able to find out which version of Excel produced the file. The user processes files from many sources, and can't trace them all.

In fact, the import works correctly if I use ODBC (and also Automation). I am considering switching to ODBC in the application, but there are snags.

The user would either need to have Office 2007 or above installed (most don't) or I would have to supply the Office Data Connectivity Pack, which would add 72 MB to my install package.

Also, there are difficulties in using ODBC with Excel when you don't have control over the layout of the spreadsheet (such as column headers not being valid VFP field names).

But I'll keep plugging away, and report back.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Maybe rather work via Excel OLEDB Provider.

http://connectionstrings.com/excel-2007

hints a solution for the headers: HDR=No in the connection strings lets vfp read in the header row as data, also IMEX=1 treats all columns with mixed types in the cells or with general type throughout the column as text columns.

You might still first try with HDR=YES and IMEX=0.

Cetin Basoz has written an EXCEL2VFP function based on OLEDB, you find it at foxite, MessageID 153776.

Bye, Olaf.

 

[Mike Lewis]

I've now figured out what was going wrong. For the benefit of anyone else who might have the same problem ....

Normally, the colummn that caused the problem contains a nine-digit social security, either as a number or a string, usually formatted as "general".

Either way, VFP has no problem importing these via APPEND FROM.

In this particular case, the user had entered the SSNs as numbers. But a few of them had leading zeroes, which weren't showing up in the cells (which is what you would expect). So the user put the SSNs in quotes, and put an equals sign in front of them. In other words, she made them into a formula. It was those SSNs that VFP choked on.

Now I know what was causing the problem, but I've still got to figure out how to deal with it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

A cell value of ="012345678" actually is stored as such, but would end up in vfp as a string. Working with oledb provider could import such a column with single or all cells put this way with the IMEX=1 option, as it would import all cell values as text. You'd only need an extra stpe of converting texts tu number or dates or whatever type you normally would expect and need.

The simpler solution would perhaps be to provide a template, where the column is set as Text explicitly, so leading zeros will not be cut off. Or tell them to not care about missing leading zeros, if that is what missing you can expect it and pad the missing digits with leading zeros.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

I already use the OLE DB provider to import from Office 2007 and above, so it wouldn't require much new code to use it for all Excel files. My main worry is that the user would have to download and install the provider if they don't have Excel installed.

Re "tell them to not care about missing leading zeros". I agree that would be the simplest option. But my client (the publisher of the application) tells me that there are hundreds of users - maybe thousands - and he has no control over how they use Excel, and no way of communicating that advice to them.

I'll give it some more thought, and report back. Meanwhile, thanks for your suggestions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy