Newbies jmireports Posted July 30, 2012 Newbies Share Posted July 30, 2012 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 Link to comment Share on other sites More sharing options...
LaRetta Posted July 30, 2012 Share Posted July 30, 2012 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 Link to comment Share on other sites More sharing options...
Newbies jmireports Posted July 30, 2012 Author Newbies Share Posted July 30, 2012 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". Link to comment Share on other sites More sharing options...
LaRetta Posted July 30, 2012 Share Posted July 30, 2012 (edited) 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 Link to comment Share on other sites More sharing options...
Newbies jmireports Posted July 30, 2012 Author Newbies Share Posted July 30, 2012 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 Link to comment Share on other sites More sharing options...
LaRetta Posted July 30, 2012 Share Posted July 30, 2012 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: Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 4288 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now