I have many missing zip codes in my data. Does anyone know of a way to quickly find/fix this in 2000 customer address lines?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Photo of zachary

zachary

  • 0 Points

Posted 2 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
You will be better served making sure your application is consistent and validated during the original data entry.

That said, you can use a procedure like the following to clean up your missing data:

(1) create a formula URL to lookup the zip code from [address1], [address2], [city] and [state] fields using the US Zip code Lookup Service:

"https://tools.usps.com/go/ZipLookupResultsAction!input.action" &
"?resultMode=1" &
"&companyName=" &
"&address1=" & [address1] &
"&address2=" & [address2] &
"&city=" & [city] &
"&state=" & [state] &
"&urbanCode=" &
"&postalCode=" &
"&zip="

Make the URL open in a new window.

(2) create a report the selects those records with missing zip codes and includes the URL formula field from step (1)

(3) in your spare time run the report from step (2) and click the button. Copy the zip code returned by the USPS zip code lookup service and paste it into record with the missing zip code

(4) occasionally refresh the report page and the number of records with missing zipcodes will shrink and eventually return zero records.

This general procedure of using a report to detect errant records and locating the missing information is the best way to methodically clean up a database. In parallel with this cleanup procedure you should improve your data entry process so that only verified and complete records get entered. It may seem like a time consuming task to clean up missing zip codes, phone number, emails but if you schedule this work during less active periods and consistently come back to it you will find the cleanup process goes smoothly. Also, the same process can be used to detect and correct inconsistently entered data such as states (eg MA, Mass, Massachusetts) etc.

See also this question/answer that used script and regular expressions to clean up errant records:

What is the "Clear the Swamp" Technique?
https://quickbase-community.intuit.com/questions/1465790-what-is-the-clear-the-swamp-technique
You might be able to use a Geocoding Service to (1) en masse export you data (2) run it through the geocoding service, and (3) import the zip code enhanced records . Or it might introduce additional complexity - YMMV. See this FREE Geocoding Service:
Texas A&M  Geocoding Services http://geoservices.tamu.edu/Services/Geocode/ If you need assistance implementing this solution fee free to contact me using the information in my profile:

https://quickbase-community.intuit.com/users/513/