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

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