Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Eddie Ruiz
  Where is Eddie Ruiz?
 Hawthorne
 United States
 Eddie Ruiz
 To: Stefan Wuebbe
  Where is Stefan Wuebbe?
 Hamburg
 Germany
 Stefan Wuebbe
 Tags
Subject: RE: extracting data
Thread ID: 393937 Message ID: 393942 # Views: 42 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Thursday, December 26, 2013 8:10:37 PM         
   


> > Hello I need some help, Im using the following code to extract data from a dbf to an excel file
> >
> > LdFrom=date(2013,01,01)
> > LdTo=date(2013,12,24)
> > COPY FIELDS 1.code, 2.name, 3.nodate, 4.nnum, 5.knum, 6.npaid to c:\documents\test.xls type xl5 for BETWEEN(nodate, ldfrom, ldto) and (4.nnum >= "0")
> >
> > The problem that i have is that the 4.nnum contains data numbers and letters, I only want to be able to export only numbers, if this helps there's 7 digits in 4.nnum. In my code above i was trying to export data where 4.nnum >= "0" to try to export only the numbers but it didn't work.
> >
> > Thanks in advanced for the help.
>
>
> For one, it's a matter of data types, a string comparison works according to the current Collate sequence, so that for example "a" is greater than "0", Command Window example:
>
? "a" > "0" && .T.
> 

>
> Perhaps you would need to add more info what you want to do exactly -
> if you want to export numeric types, perhaps only the leading numbers in a string, you could simply use the Val() function, or Int(Val())
>
>
? VAL("a") && 0.00
> ? INT(VAL("a")) && 0
> ? INT(VAL("7")) && 7
> ? INT(VAL("7a")) && 7
> 

>
> If you want to handle all numbers in a string, say concatenate them by removing any not-a-number characters, you can use a nested ChrTran() expression
>
c = "7a7"
> ? INT(VAL(c)) && do you want 7 or 77?
> ? CHRTRAN(c, CHRTRAN(c, "0123456789", ""), "")
> 

>
> So that your solution would perhaps be to put an expression that matches your requirements, maybe similar to the previous samples, either into the field list and/or into the For clause of your command - does that work?
>
>
>
> hth
> -Stefan

Thank for your suggestions Stefan. If you see the screenshot on the nnum field most of the already extracted data in that field contains numbers (7 digits) but if you see on that same column there's also data that Starts with letters "W" etc... In the code I used above is extracting the data but really want to filter an only export when there's numbers only in that nnum field.

Thanks




ENTIRE THREAD

extracting data Posted by Eddie Ruiz @ 12/26/2013 6:05:37 PM
RE: extracting data Posted by Stefan Wuebbe @ 12/26/2013 7:11:01 PM
RE: extracting data Posted by Eddie Ruiz @ 12/26/2013 8:10:37 PM
RE: extracting data Posted by Stefan Wuebbe @ 12/26/2013 9:10:01 PM