I have a client database which contains the address and post code in a single memo field. because of the variety of UK address and user input the post code can appear on any line and not necessarily at the end. is there any way that i could extract the post code and populate an additional text field.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
UK Post Codes in Memo Fields
- Thread starter cverrall
- Start date
darrellblackhawk
Programmer
You'll have to create a parsing algorithm that
compensates for the variability.
I think 'Mike Lewis' is your best bet for pointers,
since he deals with U.K. issues.
Darrell
'We all must do the hard bits so when we get bit we know where to bite'
compensates for the variability.
I think 'Mike Lewis' is your best bet for pointers,
since he deals with U.K. issues.
Darrell
'We all must do the hard bits so when we get bit we know where to bite'
Mike Lewis
Programmer
Darrell,
I think 'Mike Lewis' is your best bet for pointers,
since he deals with U.K. issues.
I am flattered you thought of me. However, I'm not sure that this is a "UK issue" (or that I particularly deal with UK issues). It seems to be a general parsing problem.
Stil, I suppose that now my name has come up, I had better have a shot at answering it <g>.
Mike
Mike Lewis
Edinburgh, Scotland
I think 'Mike Lewis' is your best bet for pointers,
since he deals with U.K. issues.
I am flattered you thought of me. However, I'm not sure that this is a "UK issue" (or that I particularly deal with UK issues). It seems to be a general parsing problem.
Stil, I suppose that now my name has come up, I had better have a shot at answering it <g>.
Mike
Mike Lewis
Edinburgh, Scotland
Mike Lewis
Programmer
Cverall,
First, are you sure the postode is not always on the last line? It is very rare for someone to write their address with the postcode other than on the last line. The only common variation is to have the postcode on a line by itself, as opposed to concatanted with another address element.
If the postcode really can appear anywhere in the address, I suppose it would be a matter of looking for a string that matches the particular combination of letters and digits that make up a postcode. As you probably know, there are three possibilities:
EH1 1XY
ED10 2QW
SW1A 1AA
Another approach would be to purchase a postcode validation package, which would do the hard work for you. Essentially, you would give it the entire address; it would return the postcode; you would then search the address within your table for the postcode and remove it.
The trouble is that such packages are very expensive, especially if you want annual updates to the postcode file.
Mike
Mike Lewis
Edinburgh, Scotland
First, are you sure the postode is not always on the last line? It is very rare for someone to write their address with the postcode other than on the last line. The only common variation is to have the postcode on a line by itself, as opposed to concatanted with another address element.
If the postcode really can appear anywhere in the address, I suppose it would be a matter of looking for a string that matches the particular combination of letters and digits that make up a postcode. As you probably know, there are three possibilities:
EH1 1XY
ED10 2QW
SW1A 1AA
Another approach would be to purchase a postcode validation package, which would do the hard work for you. Essentially, you would give it the entire address; it would return the postcode; you would then search the address within your table for the postcode and remove it.
The trouble is that such packages are very expensive, especially if you want annual updates to the postcode file.
Mike
Mike Lewis
Edinburgh, Scotland
MrFancyteeth
Programmer
Morning..
I worked in the mail industry for years & we always encountered Post code Problems.
You are correct in the defintion of 3 basic postcode types (6,7 & 8 chars long)
The post code is broken down into a 3 or 4 character Postout segment followed by single space then a 3 character Post In segement.
From experience about 25% of your data (dependent on source) is likely to Contain ONLY a post out field (e.g Bristol, BS1) or no Space (e.g Bristol, BS13XX).
For Industry strength Postcode extraction you need to obtain a datafile of valid Postcodes from the Royal mail.
If you need to perform any actions on the data (walksort, mailsort, CBC etc) you will need a Highly advanced program to take into account Posttowns, and Postcode cleaning - we had many in house programs to perform these functions, but ended up using Matchit (which uses DBFs).
Mrf
I worked in the mail industry for years & we always encountered Post code Problems.
You are correct in the defintion of 3 basic postcode types (6,7 & 8 chars long)
The post code is broken down into a 3 or 4 character Postout segment followed by single space then a 3 character Post In segement.
From experience about 25% of your data (dependent on source) is likely to Contain ONLY a post out field (e.g Bristol, BS1) or no Space (e.g Bristol, BS13XX).
For Industry strength Postcode extraction you need to obtain a datafile of valid Postcodes from the Royal mail.
If you need to perform any actions on the data (walksort, mailsort, CBC etc) you will need a Highly advanced program to take into account Posttowns, and Postcode cleaning - we had many in house programs to perform these functions, but ended up using Matchit (which uses DBFs).
Mrf
AndrewCoates
Programmer
If you've got Outlook on the machine on which you're doing the parsing then you can use the (rather good in my experience) parser that Outlook itself uses. I wrote this up in FoxTalk a couple of years ago and you and get the article from their site, or from mine: (the article's called "Using Microsoft Outlook to Parse Names and Addresses from VFP".
I've not tried it with UK addresses, but it works very nicely for Australian and US addresses.
Cheers,
Andrew
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
.I've not tried it with UK addresses, but it works very nicely for Australian and US addresses.
Cheers,
Andrew
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
Mike Lewis
Programmer
Andrew,
I've just read your article. It look like a good solution. My only reservation is that Cverrall's addresses appear to be out of sequence -- the postcodes are mixed up with the other address lines, rather than on a line of their own at the end. I wonder if the Oulook parser would handle that?
Mike
Mike Lewis
Edinburgh, Scotland
I've just read your article. It look like a good solution. My only reservation is that Cverrall's addresses appear to be out of sequence -- the postcodes are mixed up with the other address lines, rather than on a line of their own at the end. I wonder if the Oulook parser would handle that?
Mike
Mike Lewis
Edinburgh, Scotland
AndrewCoates
Programmer
One way to find out ... <g>
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
Mike Lewis
Programmer
Andrew,
OK, I just tried it with some random UK addresses. It worked pretty well with valid addresses -- that is, with the postcode in the last line -- but it failed in every case if the postcode was before the last line. In all those cases, it returned a blank postcode.
So it won't solve the original problem. But, still, it's a very useful technique to know about. Thanks for telling us about it.
Mike
Mike Lewis
Edinburgh, Scotland
OK, I just tried it with some random UK addresses. It worked pretty well with valid addresses -- that is, with the postcode in the last line -- but it failed in every case if the postcode was before the last line. In all those cases, it returned a blank postcode.
So it won't solve the original problem. But, still, it's a very useful technique to know about. Thanks for telling us about it.
Mike
Mike Lewis
Edinburgh, Scotland
AndrewCoates
Programmer
Well, it will solve the problem for those that it understands and will allow you to raise an automatic flag (based on the blank postcode) for manual review of those that it doesn't.
I was very pleasantly surprised to discover that Outlook exposed this powerful engine. Note that it also works for parsing names into preffix, first name, middle and last names and suffix.
Cheers,
Andrew
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
I was very pleasantly surprised to discover that Outlook exposed this powerful engine. Note that it also works for parsing names into preffix, first name, middle and last names and suffix.
Cheers,
Andrew
Andrew Coates
OzFox 2003 Australia's VFP Conference -- ------
DISCLOSURE
We are the Australasian Distributor for the Hentzenwerke Series of Books. (By the same token, I really do think they're the best printed VFP resource out there -- that's why we sell them)
TheMissionary
IS-IT--Management
Great fun trying to find the programmer solution ain't it. From a business point of view, how many addresses do not have the postal code on the last line? If you only have 1-200 addresses with a postal code mixed into the middle, why not simply edit those addresses? Fix the problem using a user.
- Status
Similar threads
