Jump to content
Sign in to follow this  
jmireports

Parsing an address from one field

Recommended Posts

I have a client who sent me a txt file with data I need to get into Filemaker 11. I can handle pretty much everything but am stuck on the address, which happens to all be in one field.

Now getting the zip code and state is not an issue since those are the last two items on the address line, but where I am having issues are the streed address and city. Some cities are one word, some two and even some three. Addresses can begin with a number ( typical) but some don't. And no types of delimiter information in the field such as commas

Attached is the file in question. Thank you in advance for any help!

Brian

foinspct.txt

post-107149-0-49088100-1343678956_thumb.

Share this post


Link to post
Share on other sites

Hi Brian, welcome to FM Forums! :laugh2:

Please see attached for one approach. If you have, or can get, a zip-code database, you can use it because the zip code KNOWS the city. Since you can know the city, you can specify everything to the left of the city position to get the street address. If you lack a zip database, I'm sure I have one I could find fairly fast.

Zips.zip

Share this post


Link to post
Share on other sites

Hi Brian, welcome to FM Forums! :laugh2:

Please see attached for one approach. If you have, or can get, a zip-code database, you can use it because the zip code KNOWS the city. Since you can know the city, you can specify everything to the left of the city position to get the street address. If you lack a zip database, I'm sure I have one I could find fairly fast.

Thank you for the quick reply...

Yes, I have thought of that option too. The issue I have (being a novice with parsing) is how you would position the city, when in fact it could be a different city. For example, 43017 here in Ohio falls into three counties and could be associated with potentially 3 cities. Thus if the city is different on the client record field, how would you know where to stop the extraction of the street address without also potentially pulling in the city too?

Say the client record says "Columbus" and the zip code look up says "Dublin".

Share this post


Link to post
Share on other sites

There may be a few exceptions you have to handle by hand. That calculation, if it fails to find a matching city for that zip code, will produce no result for the city. You can search for empty results to manually correct. Your other option is to add those 'duplicate' cities to the zip code database and change the calculation to look for value matches.

EDITED: We have that situation here all the time - because most computers go by zip, it tells us we are in the other city which is close by and also uses our zip code.

Once it has 'assigned' the city, you can scan for oddities pretty quickly just comparing those two pieces.

Edited by LaRetta

Share this post


Link to post
Share on other sites

Thank you, I will give this a shot and see how it goes. I agree, there may be only a handful of manual changes. That is till they send me a file with about 15,000 entries!

Thanks for your quick help,

Brian

Share this post


Link to post
Share on other sites

I've done this very thing in migrations. You could create another temp calc as:

RightWords ( LeftWords ( string ; WordCount ( string ) - 2 ) ; 1 )

... to make visual comparison easier. Good luck on your project! :laugh2:

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    DaCo 
×

Important Information

By using this site, you agree to our Terms of Use.