Parsing an address from one field

[jmireports]

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

[LaRetta]

Hi Brian, welcome to FM Forums!

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

[jmireports]

Hi Brian, welcome to FM Forums!

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".

[LaRetta]

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 July 30, 2012 by LaRetta

[jmireports]

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

[LaRetta]

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: