Castell Computers

Free Zip Code Database GoldMine Transfer set

by Doug Castell @ 1:55 pm. Filed under General, CRM, GoldMine, Technology, SQL

Do you love the ability of GoldMine to automatically fill in the city and state when you enter the zipcode but HATE hand-keying 42,000+ zip codes?

I see a request for a good source of zip codes for GoldMine just about once a week on the various support forums. The answers always vary from, “Buy an out-of-date list and import it with out-of-date freeware tools.” to “Tough luck.”

Not being one to accept such things at face value, I googled around a bit and found www.populardata.com where a meticulously maintained comma delimited list of zip codes (with the lat/lon coordinates as well!) is offered for free, direct download with no strings attached — a labor of love, to say the least!

I contacted the maintainer and he was happy to allow me to redistribute the list for free in GoldMine-compatible form. Inspired by his altruism, I proper-cased the city names with BR7 from GoldBox’s site and imported it into a clean GoldMine database. I then made a sync transfer set suitable for retrieval into GoldMine 6.7 (Standard or Corporate Edition.)

The file can be downloaded here.

To import the zip codes, you’ll probably first want to delete the existing zip codes in your current GoldMine database. This may sound simple, but if you synchronize, you’ll need to delete them in a sync-friendly way. This means a tool like GoldBox or a properly written GoldMine API-leveraging program. Whichever way you go, you’ll want to delete the records returned by this query:

select * from lookup where fieldname like '|%'

Obviously, take care when doing this as you could delete the wrong things, importing 42,000+ new lookups will impact remote synchronizers, you may duplicate existing zip code lookups, etc.

For the more technically inclined, you might want to obtain the .TXT file from the Popular Data site and Beyond Gold from DJ Hunt’s site and use the zip code import tool built in there. You’ll end up with all UPPERCASE cities, though, unless you take action to deal with that.

Enjoy!

[link]

**If you liked this article, you might also like:**

5 Comments to “Free Zip Code Database GoldMine Transfer set”

tc3 Says:
April 12th, 2007 at 7:30 am

Doug,

You’ve made an undeniably substantial contribution to the entire GoldMine Community with this little gem. I just finished the process and now have the 42,471 entries in place - where there were 112 before.

In order to benefit the not-so-technical users like me, I’d like to make my own contribution by adding a few details that might make the import process less “scary”. Feel free to edit my post for technical accuracy.

===

The first thing I did was to make a full back up of my GoldMine System by copying the ENTIRE GoldMine Root folder to a safe location.

Next, I did away with the existing 112 zip code lookup entries by popping the F2 Lookup Box on the zip code field and alternating between clicking the DELETE Button and pressing “Y” on the keyboard. This took about 25 seconds which leads me to believe I could delete records at a rate of almost 300 per minute (I mention this to make users aware that even several thousand entries can be done away with in a Sync Aware way in a very short amount of time WITHOUT using special utilities or programming).

I then Packed and ReIndexed the system - just cause I like a clean slate

I extracted the zip file you provided into a GoldMine Sub Folder named “Utilities”.

In GoldMine, I brought up the Sync Wizard, started a New Session, and selected “Retrieve a Transfer Set”.

I pointed to \goldmine\utilities\zip_transfer\ and on the next page I CLEARED ALL THE BOXES and checked only F2 Lookups.

I selected the appropriate GoldMine Database and choose to Log The Details (in case of trouble) and let her rip.

A couple minutes later all the entries were in and life is good.

Thanks for your work on this!

Ted Chmura III - Composer
TC3 Music
ted@tc3music.com

** Hear my latest tune, Proof - on SoundClick at http://www.soundclick.com/bands/pagemusic.cfm?bandID=519328 **
Doug Castell Says:
April 12th, 2007 at 7:46 am

thanks for that, Ted. I admit it, I wrote the article in a hurry. The main goal was to get the file out there and let readers know about it.

Note that retrieving ONLY lookups is unnecessary as I took precautions to not include anything but the lookups for the zip codes in this transfer set. The curious could use the ‘unpack the transfer set ONLY’ option in the sync wizard to open the transfer set and examine exactly what’s in there. It’s just 42,471 zip code lookups and 42,471 sync log stamps to go with them.
newguy2007 Says:
April 13th, 2007 at 10:53 am

What about those of us on 7.0?
Doug Castell Says:
April 13th, 2007 at 12:45 pm

good question! I haven’t got a cut-and-dry answer for that at the moment. It’s on my ‘list’, though!
Doug Castell Says:
September 20th, 2007 at 4:26 pm

Here’s another free zip code database:
http://geocoder.ibegin.com/downloads.php

Be sure to check out the interesting work being done there as well at www.ibegin.com ! Good stuff!