Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Hartford, CT

    Unanswered: How to parse an address data


    I am working with two tables. One has an address field that was filled as free-text data. The data is not delimited by anything and sometimes not complete. The second table has a master list of the city, state, and zip codes of the United States. I am trying to parse the city, state, and zip from the address table. My theory was to see if any part of the address field matches a city and a state from the second table, then copy the city, state, and zip to separate fields on the address table. Would that work? If it would, what would the query look like? If not, any other ideas?

    Thanks a lot.

  2. #2
    Join Date
    Sep 2003
    You have to parse it right to left. Test the 1st string for numeric data. If so you have the zip code otherwise you might have either an address city or state ...

  3. #3
    Join Date
    Sep 2003
    Your approach would work, and it's not one query. I have done this before, and have found that it takes multiple approaches. Generally, I don't use a query, I use code, and any queries that can be done, are done through CurrentProject.Connection.Execute "SQL".

    Because your repository of Postal Data is so huge, I would consider partitioning it and your legacy data by state so you're not looking up TN addresses against NY ones.

    I usu try to set some time aside to quickly review (manually) the initial segregations of data. It is time consuming, but is generally less time than trying to figure out what went wrong after the fact.

    You may also want to put aside a trouble table with a trouble reason, such as no numeric data (req for zip and most addrs), addrs with chars less than X characters (where X is the minimum chars you would expect for an address), no state found, etc.

    From my data conversion experience, there is no "magic bullet" for this stuff. Manual review is necessary to ensure the target system doesn't repeat the mistakes of the legacy system.
    All code ADO/ADOX unless otherwise specified.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts