Hello, I have a large database fil 3

[Phil Thoms]

Hello,
I have a large database file with a field for the address. Unfortunately the postcode has been input together with the town:- Warrington WA14 1BQ, Chester CH12 3GG etc.
Is there a quick way to separate the town from the postcode and place the postcode in a new field? I am currently using VFP version 6.
Thanks

 

[captsnappy]

You could write a short prg that would parse the address field. I'm not familiar with the postcode "mask" of your country, but if it is always "4 characters, space, 3 characters" then you could search for the second SPACE from the right to find the position within the string to extract the postcode. If I'm understanding your question correctly....

Trey

 

[captsnappy]

crudely put:

npos = rat(alltrim(address), " ", 2)		&& search from right for second occurence of a SPACE

mpostcode = substr(alltrim(address), npos+1)	&& extract the postcode

 

[Mike Lewis]

Have a look at my CheckPostcode() function.

You can apply this function to the last two words in the line (use GETWORDNUM() and GETWORDCOUNT() to extract those words). If CheckPostCode() returns .T., move the two words to the new field.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

The following code should do the trick:

* Assume Address5 is the last line of the address, and
* Postcode is the field that is to contain the postcode

* Get number of words in the last line
lnWords = GETWORDCOUNT(Address5)

* Get last two words
lcLastTwo = ALLTRIM(GETWORDNUM(lnWords - 1)) + " " + ;
  ALLTRIM(GETWORDNUM(lnWords))

IF CheckPostcode(lcLastTwo)
  * Last two words look like a postcode
  REPLACE Address5 WITH STRTRAN(Address5, lcLastTwo)
  REPLACE PostCode WITH lcLastTwo
ENDIF

I haven't tested this, but I think it's more or less correct.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Phil Thoms]

For Mike,
I don't think Getwordcount and Getwordnum are to be found in VFP version 6 which I said I was working in.

Thanks

 

[Olaf Doschke]

IN VFP6 those two functions are still just in foxtools.fll, but you can SET LIBRARY TO FOXTOOLS.FLL and then also use GetWordnum in VFP6.

Bye, Olaf.

 

[Olaf Doschke]

To be more precise Foxtools.fll contains a function called WordNum(), not GetWordNum().

But Captsnappy's RAT() is surely also available in VFP6.

Bye, Olaf.

 

[Mike Lewis]

Here is my code, modified for VFP 6.0:

* Assume Address5 is the last line of the address, and
* Postcode is the field that is to contain the postcode

[b]SET LIBRARY TO FOXTOOLS.FLL[/b]

* Get number of words in the last line
lnWords = [b]WORDS[/b](Address5)

* Get last two words
lcLastTwo = ALLTRIM([b]WORDNUM[/b](lnWords - 1)) + " " + ;
  ALLTRIM([b]WORDNUM[/b](lnWords))

IF CheckPostcode(lcLastTwo)
  * Last two words look like a postcode
  REPLACE Address5 WITH STRTRAN(Address5, lcLastTwo)
  REPLACE PostCode WITH lcLastTwo
ENDIF

For the sake of clarity, I've hightlighted the differences in bold.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Captsnappy's approach is also good, but it assumes that the postcode is present in every record. If any addresses have the postcode missing, or if they already have the postcode in a separate field, then you will be moving part of the actual address to the postcode field, which is not what you want. By contrast, my code only kicks in if the postcode is actually present in the last line.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Well, you can extract the postcode via substr using Rat and then also call your CehckPostcode function. It doesn't matter how you find the postcode, does it? Getting the last two words with WordNum you also could extract something else, if the postcode is already removed.

I agree it's good to check what you extract before you delete it. I would therefore not delete the postcode at all in the first run, and only fill the new postcode field. Then you can still remove the postcode in a second run after you looked into the data.

Bye, Olaf.

 

[Mike Lewis]

I would therefore not delete the postcode at all in the first run, and only fill the new postcode field. Then you can still remove the postcode in a second run after you looked into the data.

The problem is if the postcode is present in some records but not others (which is likely to be the case), then you would have to inspect every record visually before deciding whether or not to delete the postcode in each case. Better to programmatically test for it, surely?

Also, another issue with Captsnappy's method is that it will fail if the postcode happens to have more than one space between the two halves. Maybe that doesn't happen very often, but it's something to keep in mind. (Captsnappy, I'm not purposely trying to denigrate your code. I'm just trying to see the question from all angles.)

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

You missunderstand me, still.

Doesn't matter much. I would split the process in tweo parts/passes:

Pass 1: Extract postcode (and check it via your function)

* Assume Address5 is the last line of the address, and
* Postcode is the field that is to contain the postcode

SET LIBRARY TO FOXTOOLS.FLL

* Get number of words in the last line
lnWords = WORDS(Address5)

* Get last two words
lcLastTwo = ALLTRIM(WORDNUM(lnWords - 1)) + " " + ;
  ALLTRIM(WORDNUM(lnWords))

IF CheckPostcode(lcLastTwo)
  REPLACE PostCode WITH lcLastTwo
ENDIF

Pass 2: Remove postcode from main address field if, and only if the new postcode field is containing a postcode.

REPLACE ALL Address5 WITH StrTran(Address5, Postcode, '') FOR NOT EMPTY(Postcode)

In between these two passes you can manually check how extraction of postcode got correct post codes, you can do this fully or taking some sample records. Whatever.

I would never do this all programmatically, even with your CheckPostcode function.

Bye, Olaf.

 

[Mike Lewis]

OK, Olaf. I see what you mean. Look OK to me.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

And now you could also do

* Get last two words
npos = RAt(AllTrim(Address5), " ", 2)
If npos>0
   lcLastTwo = SubStr(AllTrim(Address5), npos+1)	&& extract the postcode
ELSE
   lcLastTwo = ""
ENDIF

That way you don't need the Foxtools.Fll

Bye, Olaf.

 

[captsnappy]

Ha! Thank you, philtoms! I so rarely see a post that I can attempt to contribute an answer to. So I jumped at the chance. And of course I ended up learning more than I bargained for in Mike and Olaf's responses. Heck, I wasn't even aware of WORDS and WORDNUM. This forum is really, really awesome.

trey

 

[Phil Thoms]

Thanks to all, everything working well.