You can look up individual ZIP codes on the USPS website, but I have over 300 addresses in need of ZIP codes. Is there a resource online where I can upload a file and get the ZIPs?
I assume you want a free one? There are tons of paid services that will give all sorts of address enhancement, but I am not aware of one that will do batch processes for free.
Yeah, the company won’t pay for anything like that. I had to ask though, in case there’s a free one!
If your addresses are mostly in one state, the USPO publishes a directory of zip codes for each state (and a national one). This used to be available as a printed booklet (maybe still is). That’s often faster than looking them up online.
Many libraries have either the state of national zip code directory available in the reference section. You might take your list to one of them and spend a few hours looking up all these addresses.
This is not my area of expertise at all but it may be possible to create a short Windows script (can you do that with a Mac OS, too??) that would take each address, run it individually through the USPS zip code checker, then add the zip to the address.
I’m kinda interested in the ability to do little tasks like this and I believe some scripts are pretty simple to write. I’m just not sure I want to devote a portion of my flagging brain-power to learning about it.
ETA: Here’s a couple of links about writing batch scripts
Google Sheets is free*, and it seems it can possibly do what you want: How to populate a column with ZIP codes based on addresses in Google Sheets - YouTube
Of course, that depends on if your data is easily transferable to the Google format.
*It says it is free only for personal use, but I used CrapCleaner on my work PC for years(because the company IT stuff was, well, crap), which also says for personal use only. The software police never came for me - and really it was something that I only personally used.
Seconding this. Quick and free.
I’ll check it out. The spreadsheet I have the ZIP-less addresses in is in Excel format, though I can easily make it .csv or fixed-format text.
I was a little concerned at first, since the ‘Loading…’ part was taking a long time. I put the complete addresses into their own column by concatenating Address 1, City, and State. It seems to have worked, only I’m getting a bunch of errors. The ZIP field says #ERROR, and when I mouseover I get this:
[indent][indent]Error
Service invoked too many times in
a short time: geocode. Try
Utilities.sleep(1000) between
calls. (line 7).[/indent][/indent]
How do I fix this?
The error indicates you need to put a delay before or after each call to geocode.
I’d put the suggested “Utilities.sleep(1000)” on the line above geocode. (I don’t know the syntax, may need a ; after that)
Brian
You can just try 100 addresses at a time. There’s a quota to how many you can do per hour/minute/day, etc.
Or I copied that thing and added the sleep 1000 to it, if you want to try it on this spreadsheet (make a copy of it to be able to edit it):
USPS used to do this sort of thing free for non-profits. It’s been years, but it used to be that you submit a database and they send you back a corrected one. It was worth their time making sure bulk mailings were using correct zip-codes. (I think it’s funny that they actually propose a fix to your code in their error message. They really are trying to help.)
Here’s the script I copied and pasted:
function geo2zip(a) {
var response=Maps.newGeocoder()
.reverseGeocode(lat(a),long(a));
return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lat
}
function long(pointa) {
var response = Maps.newGeocoder()
.geocode(pointa);
return response.results[0].geometry.location.lng
}
Where exactly would it go?
That code is inefficient, doing it 3x for every address when you only really need to do it twice. Try this code:
function geo2zip(address) {
var location=Maps.newGeocoder()
.setRegion('US')
.geocode(address).results[0].geometry.location;
Utilities.sleep(1000); // To prevent timeouts
var zipCode=Maps.newGeocoder()
.setRegion('US')
.reverseGeocode(location.lat, location.lng).results[0].address_components[6].short_name;
return zipCode;
}
It still might time out. In that case you, can try changing the sleep(1000) to like sleep(10000) with an extra 0, and letting it run overnight.
Hold up, that logic is wrong… it doesn’t matter how long you make the sleep in the script, because all the rows are calling it at once. That’s what’s tripping it up. Let me see how to get around that…
OK, here’s a script that should work. Tested it with 200 or so at a time and no problem:
function geo2zip(address) {
var apiKey='INSERT_YOUR_API_KEY_HERE'; // replace with your own
var geocodeResponse = JSON.parse(UrlFetchApp.fetch('https://maps.googleapis.com/maps/api/geocode/json?&key=' + apiKey + '&address=' + address).getContentText());
var addressComponents = geocodeResponse.results[0].address_components;
var zipCode;
for (var x = 0 ; x < addressComponents.length; x++) {
var zipTest = addressComponents[x];
if (zipTest.types[0] == 'postal_code') {
zipCode = zipTest.short_name;
}
}
return zipCode;
}
I’ll PM you a API key. Anyone else that wants to use the script, you can sign up for your own API key (it’s free up to a few thousand a day) here: Use API Keys | Maps JavaScript API | Google Developers
The standard Google Sheets will let you do a few dozen no problem, but once you get into the few hundred range, you need your own key. That required rewriting the script to manually implement the geocoding. Anyway, eventually you have to start paying, but I think that’s only after the tens of thousands of requests/day.
It might ask you for some permissions when you first run the script. I’m not sure why it does that for something as simple as a URL fetch. But you can see the whole script yourself, it’s not doing anything aside from downloading a URL and looking through it for a zip code.
FYI, the script will only work for valid, well-formed addresses. Google’s geocoding service will try to guess what you mean if you give it a bad address, but sometimes it’ll just error out if it can’t figure it out or if you weren’t specific enough (like if you just typed in “New York, NY” without a specific street).
Thank you, Reply.
I’ve copied from the PM into the script editor. I’ve PM’d you the numbers. There are about 100 ZIP fields that were not populated. Some of the addresses are invalid, but others can be looked up on the USPS site so they should have been populated. There are about a dozen errors as well.
I’ve just been letting records with incomplete addresses drop. If I’m going through the data and there are only a couple of missing ZIP codes (or whatever), I’ll clean them up manually. When there are hundreds of them, my program just drops them. 300 or so records out of nearly 40,000 don’t matter all that much. But I hate to lose any data!
How can you tell? 
Seriously, script languages are completely unlike the programming I do (Easytrieve, kind of similar to COBOL or old-school BASIC).
Hmm, I didn’t think about PO Boxes. I don’t think Google’s geocoding service understands them, since they’re not a geographic thing but a USPS idiosyncrasy. So I rewrote it using USPS’s own API. Here’s a new spreadsheet you can copy for your own use:
The upside is that you no longer need the App Script. The downside is that you have to separate out the different address components (street, city, state) into different columns, but it sounds like your data is already like that. If so, now it’s just a formula using IMPORTXML:
=importxml("https://secure.shippingapis.com/ShippingApi.dll?API=Verify&XML=<AddressValidateRequest USERID=%22235NA0006329%22>
<Revision>1</Revision>
<Address>
<Address1>"&A2&"</Address1>
<Address2>"&B2&"</Address2>
<City>"&C2&"</City>
<State>"&D2&"</State>
<Zip5></Zip5>
<Zip4></Zip4>
</Address>
</AddressValidateRequest>","concat(//AddressValidateResponse/Address/Zip5,'-',//AddressValidateResponse/Address/Zip4)")
If my userID hits its quota, you can apply for your own free one here:
Web Tools APIs | USPS and just replace it where it says %22235NA0006329*%22* (but leave the %22s in there, they’re important).
I mean, the script itself was fine, but every single row calls that function at the same time (as you can see by all the LOADING…s showing up all at once, instead of one row at a time). That means every single row was trying to hit the API altogether, with 3 requests per row = a sudden attack of 1500 requests all at once.
A simpler option is to use Geocodio. You can upload a spreadsheet of addresses lacking zip codes, and it will add them, as well as latitude and longitude and lots of other info. It can handle data where all the address info is one big field, or when it’s split up into city, state, etc. It’s free for up to 2500 lookups a day.
I used it to add county names and census tract codes (the latter an extra-cost option) to 3500 addresses a few weeks ago, for a project where I needed to classify addresses by neighborhoods within a city. I paid $2.22 total. Highly recommended.