INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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;

CODE --> vfp9

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

CODE --> vfp9

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

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

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 :)

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

Quote (drdolittle)

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

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

Try multiplying by 1...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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



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

CODE

* 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

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

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.

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

Hi,

You may want to try this

CODE -->

select line, order.itemno, percentage, invoice, cusno, table1.descrip as Description,;
table1.sccal as Scientific_cal, ;
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 ;
from order;
inner join table1;
on order.itemno = table1.itemno;
where invoice=invnum;
into cursor TemapCursor

copy to c:\download\file.xls TYPE XL5 

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

hth

MK

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

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

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


Quote:

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close