EXTRACT FROM RIGHT OF FIELD

[powerprinting]

I have a rather large database that came from the county in print format. I've never liked county files. Anyway, when I import it into a dbf format, the first line looks likes this...
rec 1 B02-002400302011-000 COMBS SUSAN S TRUSTEE LOT 577 500 0.360 4,000 (all in one field of course)

the second and rest are similar

rec 2 = B02-722230000005-001 COMMISSIONERS PREBLE CO S PT SE & SE PT SW 620 13.000 26,000

My goal is to extract all the numerical data on the right to a seperate field. Since the data is not consitant in position for each record, I require a snippet to run that will do this for me. I know of the RAT() funtion but don't know how to account for all the spaces. Is extraction of one set of numbers at a time the only way to accomplish this? All the records are consitant in that they have 3 sets of numbers. Any help would be appreciated. Thanks

 

[Stella740pl]

powerprinting,

It looks like you've got a delimited text file. How do you
import it into DBF?

 

[BPeisch]

I think you can do this pretty easily if you employ a couple of tricks.

First of all, you can pull numeric data out of any string with this code:

lcNumericStr = CHRTRAN(lcString,CHRTRAN(lcString,"0123456789",""),"")

If you want to preserve the spaces too, to make sure you have separation between the numbers, add " " to the string with the digits:

lcNumericStr = CHRTRAN(lcString,CHRTRAN(lcString,"0123456789 ",""),"")

Now, it looks like you don't want to do this on the entire string in the record, because of the numbers you have at the beginning. So, set lcString = right(DataRecord,50) or some reasonable number of characters to make sure you strip off the beginning.

I'm not sure if you want to include the lot number from the first record in the numbers you want to pull off. The example I gave you will include that.


-BP (Barbara Peisch)

http://www.peisch.com

 

[dbMark]

I think what stella740pl is asking is whether the data always comes through in the same column order. If so, then you could set up the table structure so that when you APPEND FROM <textfile> TYPE SDF you'll get the numbers dropped automatically into the proper fields. If the length and positioning is not 100% consistent then you'd better go with the concept you had at first, reading each number one by one using the spaces as delimiters. This demonstration pulls out the 3 numbers and displays them:

FUNCTION get3nums
PARAMETER tcString
PRIVATE cString,  cNum1, cNum2, cNum3, x
IF VARTYPE(tcString)=&quot;C&quot; .AND. tcString <> &quot;&quot;
   cString = tcString
ELSE
   cString = &quot;Test with this data 123.45  100   6.3345&quot;
ENDIF
STORE &quot;&quot; TO cNum1, cNum2, cNum3
* get #3
x = RAT(&quot; &quot;,cString)
cNum3 = SUBSTR(cString,x+1)
cString=RTRIM(LEFT(cString,x))
* get #2
x = RAT(&quot; &quot;,cString)
cNum2 = SUBSTR(cString,x+1)
cString=RTRIM(LEFT(cString,x))
* get #1
x = RAT(&quot; &quot;,cString)
cNum1 = SUBSTR(cString,x+1)
* display results
? &quot;[&quot;+cNum1+&quot;]&quot;
? &quot;[&quot;+cNum2+&quot;]&quot;
? &quot;[&quot;+cNum3+&quot;]&quot;
RETURN

dbMark

 

[CDavis]

Assuming that the data is currently in a single field: MyTable.OrigFldName

Add a new field to store the number data: MyNewNumbersFldName

Back up your original data!

Then Assuming that the data you submitted is typical of the whole dataset, something like the following could be used.

Use MyTabel In 0
Go Top
Scan
MyTempVar = Alltrim(MyTable.OrigFldName)

Replace MyTable.MyNewNumbersFldName with Alltrim(Substr(MyTempVar,len(MyTempVar)-30,31))

Replace MyTable.OrigFldName with Alltrim(Left(MyTable.OrigFldName, At(MyTable.MyNewNumbersFldName,MyTable.OrigFldName)-1))

Endscan

You might have to play with the number of characters you work with -- I chose 30 and it seemed to work with the data examples you posted.

CDavis

 

[craigsboyd]

Here, this is a function I wrote after reading your post, give it a try (cut-n-paste it into a prg file and run it from within VFP):

CREATE CURSOR crsTemp (justnums c(100), cntydata c(254))
INSERT INTO crsTemp (cntydata) VALUES ("B02-002400302011-000    COMBS SUSAN S TRUSTEE     LOT 577                   500   0.360        4,000")
INSERT INTO crsTemp (cntydata) VALUES ("B02-722230000005-001        COMMISSIONERS PREBLE CO   S PT SE & SE PT SW           620  13.000       26,000")

Replace ALL justnums WITH SUBSTR(cntydata, NumsAt(cntydata))

BROWSE

FUNCTION NumsAt(tcString)
	LOCAL llHitADigit, lnCounter, lnStringLength, lnSpaceAreaHit, lcCharacter, llInSpaceArea
	#DEFINE TOTALNUMS 3 &&Change if you need to grab less or more than 3
	lnStringLength = LEN(tcString)
	lnCountThreeSpaces = 0
	llHitADigit = .F.
	lnSpaceAreaHit = 0
	llInSpaceArea = .F.
	lcCharacter = ""
	FOR lnCounter = lnStringLength TO 1 STEP -1
		lcCharacter = SUBSTR(tcString,lnCounter,1)
		IF !INLIST(lcCharacter,CHR(9), CHR(32))
			llHitADigit = .T.
			llInSpaceArea = .F.
		ELSE
			IF llHitADigit && Must have hit a digit already
				IF !llInSpaceArea
					llInSpaceArea = .T.
					lnSpaceAreaHit = lnSpaceAreaHit + 1
					IF lnSpaceAreaHit = TOTALNUMS
						lnCounter = lnCounter + 1
						EXIT
					ENDIF
				ENDIF
			ENDIF
		ENDIF
	ENDFOR
	RETURN (lnCounter)
ENDFUNC

Slighthaze = NULL
_{craig1442@mchsi.com}

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

 

[powerprinting]

I'm back from my short vacation and ready to get back to this project. Thanks to all for the replys. To answer the question about data in the same column order.. no they are not... that would be too easy.. .right? I am toying with all your suggestions but have yet to be able to extract only the very right set of numbers. I do not need lot numbers etc...just the income value, the very right number. Maybe from all the code examples I am missing something. I'll keep at it. Thanks again.

 

[powerprinting]

Just to add to this... I know this simple command allt(substr(field1,1,at (' ',field1,1))) will extract the first set of numbers, I thought there was a variation of this reads from the right.

 

[DSummZZZ]

This will extract only the right value:

STORE 'B02-002400302011-000    COMBS SUSAN S TRUSTEE     LOT 577                   500   0.360        4,000 ' TO cText
Substr(cText, Rat(' ',Alltrim(cText)) + 1)

But since &quot;,&quot; isn't a number, you may want to convert it:

STORE 'B02-002400302011-000    COMBS SUSAN S TRUSTEE     LOT 577                   500   0.360        4,000 ' TO cText
STORE Substr(cText, Rat(' ',Alltrim(cText)) + 1) TO cVal
cVal = Substr(cText, Rat(' ',Alltrim(cText)) + 1)
nVal = Chrtran(cVal, ',', '')
?nval

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[powerprinting]

this works like a drea....

Substr(cText, Rat(' ',Alltrim(cText)) + 1)

Not to say the other solutions are not what I could have used but... as usual Dave, the simple answer is always the one I am looking for! Thanks alot.