Converting a string to a numeric value when exporting from a dbf to excel

[Luiz Eduh]

Hi all, I need of your help.

I'm currently using this code to export data to excel from 2 dbf and it works fine, except 2 fields export data as text and i would like to convert them into numeric, the fields are Price and totalprice;

select line, order.itemno, percentage, invoice, cusno, table1.descrip as Description,;
table1.sccal as Scientific_cal, str(int(Percentage/100*table1->pack),4) as pack,;
val(price(table1.fob)) as price,;
str(round2(int(Percent/100*table1->pack)*val(price( table1->Fob ))),7,2) as totalprice;
from order;
inner join table1;
on order.itemno = table1.itemno;
where invoice=invnum;
into cursor TemapCursor
copy to c:\download\file XLS

Like I mentioned above the data exports fine, but these 2 are exporting as text

val(price(table1.fob)) as price,;
str(round2(int(Percent/100*table1->pack)*val(price( table1->Fob ))),7,2) as totalprice;

I can format the data in excel to convert it to numeric but is double work, i was just wondering if it can be converted into numeric on my code above, any help will be much appreciated.

Thank you

 

[Dan Olsson]

Price SHOULD come over as numeric but since you use STR() on TotalPrice it would be text.
On the other hand Excel has it's own standard format where it tries to examine the fields and determine what type it contains. Sometimes that does not work

 

[JRB-Bldr]

Excel has it's own standard format where it tries to examine the fields and determine what type it contains. Sometimes that does not work

One problem I have often encountered is just what is described above - where Excel itself seems to decide, on its own, unless 'told' otherwise, how to display numbers - sometimes as Integers, sometimes as Real numbers, and sometimes even as Characters.

The best way to ensure that the numbers from your VFP Data will be shown as you want them to be in Excel, is to use VFP Automation of Excel.

You can either write out all of your data with VFP Automation, or write out the data as you are and then go back and use VFP Automation to 'fix' Excel's numeric cells.

Good Luck,
JRB-Bldr

 

[SkipVought]

Try multiplying by 1...

val(price(table1.fob)) * 1 as price,;
str(round2(int(Percent/100*table1->pack)*val(price( table1->Fob ))),7,2) * 1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

Str(1)*1 => Data type mismatch.
Skip, VFP is not strictly typed, but that doesn't make it act on expressions like String * Number as JS or PHP do. And in fact, VFP is very strictly typed, when it comes to tables.

Luiz, as Dan Olsson already said the STR() makes it a string, arriving as string (char) in Excel, too.

You don't show how you create your excel sheets, the simplest case would be EXPORT TO export.xls TYPE XLS or COPY TO copy.xls TYPE XLS.

Anyway, you better not create a string in VFP, if you copy that over to excel via setting cell value it surely arrives as string, if you EXPORT/COPY TO TYPE XLS/XL5 it also gets over as a string, only pasting it via Excels Paste method would make Excel infer a type, but as far as I tested this has the worst results, because what VFP puts on the clipboard via _VFP.DataToClip depends on your SET DECIMALS and SET POINT settings and how that arrives in Excel also may depend on Excels locale, it's not easy to control this and in tests I did right now it still also arrives as string/char/text. Besides at least in Excel 2007 The sheet you paste into has to be visible for paste to work, which is unelegant and slow expert to excel, as users see how you automate it.

But once you have numeric fields in the query result and you use COPY or EXPORT TO TYPE XLS that gets over fine.

The other possibility also is much more verbose and precise about cell types is to export Office XML and this does it well:

http://praisachion.blogspot.de/2017/01/export-dbf-to-msexcel-xlsx.html

In that case also don't convert to the visual srting you want, keep it anumeric or float/double or currency in VFP and it arrives as such in Excel. Formatting then should be set in a columns or cells NumberFormat to eg "0.00" to have two decimal places.

Bye, Olaf.

 

[NasibKalsi]

* Add 2 numeric fields to the TemapCursor i.e. NEWPRIX and NEWTOTP
select 00000000.00 as NEWPRIX, 00000000.00 as NEWTOTP, ;
line, order.itemno, percentage, invoice, cusno, table1.descrip as Description,;
table1.sccal as Scientific_cal, str(int(Percentage/100*table1->pack),4) as pack,;
val(price(table1.fob)) as price,;
str(round2(int(Percent/100*table1->pack)*val(price( table1->Fob ))),7,2) as totalprice;
from order;
inner join table1;
on order.itemno = table1.itemno;
where invoice=invnum;
into cursor TemapCursor ;
READWRITE

* Added line
replace all NEWPRIX with PRICE, NEWTOTP with val(TOTALPRIC)

copy to c:\download\file XLS

Else you may follow JBR-Bldr' post

 

[Olaf Doschke]

No need for such a complicated construct, you can CAST() to the type you want. And it's stupid to go for STR(numeric,7,2) if you want a numeric field, simply don't convert to STR.

You might ensure the N(x,y) type of fields by CAST and then COPY TO XLS, but I don't even show that. The main solution is to go through COPY TO / EXPORT or for total control export via automation.
In the end you can get all the typical types 1:1 in excel, when you just already have them in VFP, but you should never expect a string formatted to a certain number of places and digits to come over as numeric, when it is a string on the VFP side already.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

You may want to try this

select line, order.itemno, percentage, invoice, cusno, table1.descrip as Description,;
table1.sccal as Scientific_cal, ;
[i][b]INT(percentage/100 * table1.pack) as N_Pack, ;
VAL(price(table1.fob)) as N_Price, ;
INT(Percent/100 * table1.pack * val(price(table1.Fob)) as N_Totalprice ;[/b][/i]
from order;
inner join table1;
on order.itemno = table1.itemno;
where invoice=invnum;
into cursor TemapCursor

[COLOR=#4E9A06][i][b]copy to c:\download\file.xls TYPE XL5[/b][/i][/color]

Btw - why did you define PRICE as c-Field? Numeric or Currency would be better.

hth

MK

 

[mjcmkrsr]

Hi,
Please forget my comment on PRICE.
Nevertheless the expression [highlight #EDD400]val(price(table1.fob)) as price[/highlight] seems to me to be a typo - unless PRICE is a function.
hth
MK

 

[Mike Lewis]

Please forget my comment on PRICE

Did you know that you can now edit your posts? If you post something incorrectly, then just click the Edit button (in the bottom right corner of the post) to correct it. (But this is only available for a limited period after you first submitted the post or last edited it.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads