Zip Codes

July 15, 2009 1:45 PM

I need help finding some simple way to find the zip codes for a lot of addresses.

I have a spreadsheet that has, among other things, addresses and zip codes (in different cells). Turns out, though, that most or many of the zipcode are wrong.

I know that I can put the addresses into, say, google maps and get the zip codes, but this is too time consuming considering the total number of addresses I have to deal with. Is there any other, quicker way that I could gather the correct zips?

posted by ecab to Computers & Internet (10 answers total)

This seems to do it. It isn't automatic but it looks pretty easy to scrape.
posted by gregr at 1:56 PM on July 15, 2009

There are lots of vendors that make address validation/correction software that handles this kind of thing for you. It's often required to get bulk mailing rates that you screen out undeliverable addresses in advance.

How many addresses is "a lot?" That could help define the scale of the problem here...
posted by zachlipton at 2:09 PM on July 15, 2009

Response by poster: I think our database has two or three thousand addresses. It's for a nonprofit, and it's our members. I don't manage it, so I don't know the details, but I figured I'd ask here for help since the rest of the office is confused about the problem.
posted by ecab at 2:16 PM on July 15, 2009

You can get under the hood of Google Maps using the Google Maps API. I don't know the API myself, but I'm pretty sure you should be able to look up the addresses you have, get "proper" addresses, and then extract the zipcode from that. Um... others may be able to elaborate.

If you don't get more help, MeFiMail me and I'll see what I can do. I love this kind of thing.
posted by McBearclaw at 2:28 PM on July 15, 2009

Response by poster: Yeah I figured something like that might be possible, but I know more or less nothing about coding, so I worry it might be over my head. I had hoped something like that might have already been written somewhere, but if so I can't find it.
posted by ecab at 2:32 PM on July 15, 2009

Best answer: Melissa Data is one of the top vendors in this space. Their software such as ZIP USA costs $99 and upwards, but they have an online Batch Address Check for data prepared in spreadsheet form (Excel or text format).
posted by dhartung at 4:08 PM on July 15, 2009

Best answer: Okay, if you have Excel and are connected to the internet, the directions below will let you get the zip codes for up to 15,000 addresses in any 24 hour period. It involves using the Google Maps API, pasting some VBA code, and waiting for a little while while it works its magic. If you run into trouble, you can email me (address in profile) and I can send you a spreadsheet.

Note that this is a quick and dirty process, and will not do any error checking. If the address fails to resolve, the zip will remain blank.

So, here's what you do:

1. You'll need to create a Google API key. If you have a Gmail or other google account, you can use it, otherwise you'll have to create a new account. You can do this by going to http://code.google.com/apis/maps/signup.html and completing the info on the bottom of the page (you can use your organization's web URL). You'll be asked to log in to your google account, and then given a long API key -- it will be a whole lot of text characters. Keep that web page open, as we'll be pasting the API key in a few moments.

2. Create a new spreadsheet in Excel

3. Make A1 = "Address" and B1= "Zip Code"

4. Fill A2 downward with addresses without zip codes (e.g., "1600 Pennsylvania Ave, Washington, DC")
--if your existing data has the address in multiple columns, you can join them together with a formula (assuming F2 is 1600 Pennsylvania Ave and G2 is Washington and H2 is DC you can make I2 =F2 & " " & G2 & ", " & I2. Then you drag the formula down to all the rows, then select the concatenated addresses, copy, go to the new spreadsheet, click on A2, right-click, choose "paste special", choose "values" and click ok.

At this point, you should have a new spreadsheet with a column A showing addresses without zip codes.

Save the new spreadsheet before we go on.


5. Now, in Excel, click "Alt-F11" and the Visual Basic for Applications window will open up. On the top left corner, there should be a bold item "VBA Project (your_sheet_name)" below that in non-bold it should have an item "ThisWorkbook." Double-click "ThisWorkbook"

This should open a blank white area on the right side of the window. Paste the following in that window:

Const MYKEY = "your_key_goes_here"

Sub BatchGeocode()
    With Sheets(1)
        r = 2
        Do While .Cells(r, 1) <> ""
            .Cells(r, 2) = getzip(.Cells(r, 1).Value)
            
            'the next 4 lines ensure that we don't abuse Google by querying them too fast
            t = Timer
            Do While Timer < t + 0.3
                DoEvents
            Loop
            
            r = r + 1
        Loop
    
    End With
    
    MsgBox "Done getting zips"
End Sub


Function getzip(myAddress As String) As String
myAddress = Replace(myAddress, " ", "+")
myURL = "http://maps.google.com/maps/geo?q=" & myAddress & "&output=xml&oe=utf8&sensor=false&key=" & MYKEY
Dim objHttp As Object
Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
Call objHttp.Open("GET", myURL, False)
Call objHttp.Send("")
Results = objHttp.ResponseText

sloc = InStr(Results, "<PostalCodeNumber>") + Len("<PostalCodeNumber>")
eloc = InStr(sloc, Results, "</PostalCodeNumber>")
If eloc > sloc Then getzip = Mid(Results, sloc, eloc - sloc) Else getzip = ""

End Function

Scroll to the top of the window, and replace your_key_goes_here with your key from Google. -- it should be surrounded by quotes.

6. In the Visual Basic for Applications window, click Tools > References..., and a window will pop up that says "References - VBAProject." In the listbox, scroll down to Microsoft XML, v2.0 and click the check box next to it -- note, if you don't have 2.0, choose the lowest number that is higher than 2.0. The entries in this window are alphabetical, which should help you find the item you want. click Ok

7. Click the little "Save" icon at the top of the window.


8. Now, we're ready to go. Put your cursor anywhere in the Sub BatchGeocode section, and click F5.

This will start the geocoder. It will go through your list of addresses in column A, sending a query to Google every .3 seconds and retrieving the zip code for each entry. This will work for up to 15,000 entries a day.

Good luck.
posted by i love cheese at 5:03 PM on July 15, 2009 [4 favorites]

Wow, I sure typed a lot. A quick correction in step 4, the formula in I2 would be: =F2 & " " & G2 ", " & H2

(the last part should be H2, not I2 as I listed above)
posted by i love cheese at 5:06 PM on July 15, 2009

Response by poster: Wow, thank you both. I'm going to try your suggestions on the dataset when I'm back at work tomorrow.
posted by ecab at 7:27 PM on July 15, 2009

Response by poster: I used i love cheese's program and it worked perfectly. Thanks so much.
posted by ecab at 7:53 PM on July 16, 2009