Strange value from Excel

[finglem]

Hi Guys, I wonder if anybody has any ideas on an odd issue I am experiencing.

I'm importing a list of product codes from an excel 2007 spreadsheet using Automation.

One of the codes is 111.00206 and my VFP 8 program is interpreting this a numeric value. I am not bothered by the fact that the code is being seen as numeric as I can convert it. The problem I'm having is that my code is seeing the cell value as 111.0020600000 ie appending an extra 5 zeroes to the product code

This means I can't convert it because there is no product code of 11.0020600000.

Does anybody have any idea why my program is seeing this behaviour?

Thanks

 

[Mike Lewis]

This looks normal to me. As far as Excel knows, it's a number. It doesn't know anything about hwo many digits of precision you want.

There are two possible solutions:

1. If it is always adding exactly five unwanted digits to the end, just chop them off. First convert to a string, then take LEFT(lcTheString, LEN(lcTheString) - 5)).

2. Or, in your Automation code, set the format of the cell to Text before you pick up the value. To do so, get a range or selection object on the cell, and set its NumberFormat property to "@".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[finglem]

Thanks Mike

It's not always appending exactly 5 zero's and I have already set the format of the cell with the following code:

oExcel.Columns("A").EntireColumn.NumberFormat = "@"

Is this incorrect?

 

[Mike Lewis]

Yes, that code looks OK to me. (You can double-check it by making Excel visible, then right-click on any cell in Column A, pick Format Cell, and check that it says Text.)

I've just run your code, then entered a number in cell A1. It came back as a character value, as expected.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[finglem]

Hi Mike

Are you using Excel 2007? I have made the spreadsheet visible. I have used the =ISTEXT() function in Excel and that returns True but when I debug the program and look at the value of the active cell in Fox the value has 5 zeroes appended to a numeric value

 

[Mike Lewis]

I'm testing this under an earlier version, but as far as I know that shouldn't matter. The basic formatting should be the same.

I'm seeing ISTEXT() returning false, no matter whether I set the format to General, Text or anything else. However, if I put an apostrophe in front of the number, then ISTEXT() returns true.

To recap:

1. Enter 12.34 in a cell. Leave the format at the default (General). VFP sees the value as a number, with trailing zeroes.

2. Edit the cell to '12.34 (note the apostrophe). Leave the format alone. VFP now sees the value as a string, withuot the trailing zeroes.

At least, that's what I am seeing. If you are not seeing that, Finglem, I'll try some more.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

Is there a fixed number of decimal places that you want to retain?

If so you might want to consider changing the VFP Excel Automation code you are using before you are picking up the value to ensure that the cell is only containing that fixed number of decimal places.

So for 5 decimal places it might be something like:
....Selection.NumberFormat = "0.00000"

Try that and let us know if it changes anything.

Good Luck,
JRB-Bldr

 

[finglem]

Mike
Yes putting an apostrophe in front of the cell does make Fox see the number as a string.

I could see how adding an apostophe would give us what we need however that is the file as we receive it and my code is supposed to import new product parts in a routine that runs overnight with no user interaction.

I guess I'm going to have to open the workbook and save it as a csv file or something.


Jrbbldr
Thanks for your input but I can't use your suggestion as the values in the cell represent somebody's product codes rather than a number so we could never be sure what we were going to receive.

I started using automation when Office 2007 was released because of the problems with the append from command and have never come across this issue before, makes me wonder how much code I have out there that may not work as I thought!!

 

[finglem]

Sorry, I meant to add..

Thanks for your help guys, much appreciated.

 

[Mike Lewis]

Finglem,

Couple of other thoughts:

I take your point about no user interaction, but I wonder if you could programmatically insert the apostrophe.

On second thoughts, that probably won't work. But it might be worth a few minutes experiment.

Saving the file as an CSV is a more promising solution. I've often done that when I've had trouble with Excel dates, for instance.

If you decide to go that route, you don't have to do it manually. It's quite easy to open the file in Excel via Automation and then save it in the required format.

Good luck with this. Sorry we haven't been able to suggest anything more positive.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[finglem]

Mike, as always, I appreciate your help and guidance so thank you.

I spent a little time experimenting but can't really see how programmatically adding an apostrophe can be done effectively in this situation.

I have written a program in the past that needed to save a workbook as a csv file through automation, although it was some time go and I can't remember why it was necessary (or even who the customer was!) so I think that's my only option as I don't think the client has any say in what they receive.

Anyhow, it's not been an entirely fruitless day, at least I have learned a few more things that will help make the choice as to how we approach tasks in the future a little more informed if not easier.

Thanks again for your help.

p.s I read your post about office online...seems automation might not be possible for some of our clients if they do choose to rent their applications anyway!!

 

[Olaf Doschke]

First of all it's irresponsible from the creator of the excel sheet to not preformat the column as text before inserting a product code, which can also be interpreted as a number, because that's what Excel then does.

I think you can't determine how many zeros in the decimal places were copied to Excel, if you read it out as a number the default excel numeric format would apply and perhaps also the SET DECIMALS setting of VFP applies. I don't have the time and nerves to experiment with this.

The first thing you should do is report back, or you never get a better source Excel sheet.

The second thing you can do is, as you know it's just a matter of the number of trailing zeros is seek that product code and keep removing zeros until you find the product code.

If you open the Excel and visually actually see the value with the correct number of decimal places I guess there will be some kind of extra data in the cell indicating the original length entered, but if not it's the fault of the one creating that excel sheet in the first place and you can't make the excel sheet better than it is. Especially if the product code would be new, how could you determine what length to use, if there is no fixed length.

Bye, Olaf.

 

[finglem]

Hi Olaf, thanks for the input, it's much appreciated.

I agree that the provider of the Excel file should take more consideration as to the format of the data but there's not a lot I can do other than provide suggestions.
If the client flaty refuses to do anything other than they are comfortable with (for whatever reasons) then that is that.

I'm a bit concerned with the second option because we are talking about hundreds of thousands of product records and to go through each one saying "does this match? if not chop 1 zero off and try again, does this match? if not chop 2 zeroes off" etc just seems ludicrous and too time consuming.

Thanks for your time though, as I said it's much appreciated.

 

[TamarGranor]

Don't you have an index on the product code field? You could use SEEK to do a quick search; if you don't it, chop a zero off and try SEEK again. Etc.

Should be very fast.

Tamar

 

[Olaf Doschke]

What Tamar says. If you index and seek the product code this loop will still be very fast. Actually you could turn it around and seek for the code without any trailing zeros first with SET NEAR ON and SET EXACT OFF and then see what product code matches best from that point on.

eg you find "11.0020600000" in excel, you remove trailing zeroes and seek "11.00206" with NEAR ON, EXACT OFF and find "11.0020600", which then matches. If your first near match is "11.0020601" or "11.0020700" you see you don't find a match.

Bye, Olaf.