Link to home
Start Free TrialLog in
Avatar of jaysolomon
jaysolomon

asked on

Zip Code db

I was just wondering if anyone has or know were i can get a zip code db that has the city state zip in it


I am willing to pay for it, but if someone has one and does not mind emailing it to me for free that would be awesome.

I prefer MSAccess DB or if someone has it in excell format that would be fine also.

If you even have a link to one on the internet i will check it out

Thanks

jAy
Avatar of LazyStudent
LazyStudent

ASKER CERTIFIED SOLUTION
Avatar of bvinson
bvinson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm surprised this forum would allow posts from someone who admits illegal activity (i.e. "I cracked some database with zips for you"), especially regarding some of the very lame (and often incorrect) posts that "LazyStudent" has provided.

Someone should ban this guy...

Avatar of jaysolomon

ASKER

bvinson

I go back to work on monday the 24th at 7am central time.

If you would be kind to leave it up until 8 or 9ish i will download it at work. I have T1 there and it will be alot faster than this slow Dial up i have at home.



jAy
Not a problem.
Offer of cracked database deleted.

Netminder
EE Admin
The US Census Bureau offers a 1999 zipcode database for free to the public.  If you're interested in that one:

  http://ftp.census.gov/geo/www/tiger/zip1999.html
to whammy: I didn't know that it is forbidden to use word "cracking":-(
I realy apologize - i didn;t know that it is forbidden...
to whammy: oops.. so hard to be banned
I am wondering what "lame" in my posts?
:-|
whammy: Silence is the gold - so don't waste it for trifles.
Nice person - ready to offend but not ready to answer for his words.
You offered him a database that you said yourself _you_  cracked. I don't have anything to answer for.
whammy : Hm..sure I will never do it again but
I was wondering what "lame" in my posts?
I apologize for that statement. I was a bit offended by your post. Can we get this thread back on topic now?
Please...if you guys need to continue this conversation, could you please exchange email addresses?  :)

I keep coming back here to see if there is info I need to see that is on topic...

Thanks guys/gals(?)

bvinson
Sorry about the emails - I really shouldn't even be answering the posts immediatly above.

I was getting a bit frustrated last night with emails as well, as I keep getting no emails from EE, then about 30 or 40 at once.

However, I will post something pretty much on topic:

I don't have a database you can use, although a google search will turn up quite a few. However, I do have some pretty decent ASP functions for you that might help with this whole scheme:

Function IsPostalCode(str)
   Dim ipcRegEx
   Set ipcRegEx = New RegExp
   ipcRegEx.IgnoreCase = True
   ipcRegEx.Pattern = "^([a-z]{1}\d{1}[a-z]{1})[ .-]?(\d{1}[a-z]{1}\d{1})$"
   IsPostalCode = ipcRegEx.Test(str)
End Function

Function IsProvince(str)
   Dim ipRegEx
   Set ipRegEx = New RegExp
   ipRegEx.IgnoreCase = True
   ipRegEx.Pattern = "AB|BC|MB|NB|NF|NT|NS|NU|ON|PE|QC|SK|YT"
   IsProvince = ipRegEx.Test(str)
End Function

Function IsState(str)
   Dim isRegEx
   Set isRegEx = New RegExp
   isRegEx.IgnoreCase = True
   isRegEx.Pattern = "AL|AK|AZ|AR|CA|CO|CT|DC|DE|FL|GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|MA|MI|MN|MS|MO|MT|NE|NH|NJ|NM|NY|NV|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|TX|UT|VT|VA|WA|WV|WI|WY|AA|AP|AE|AS|FM|GU|MH|MP|PW|PR|VI"
   IsState = isRegEx.Test(str)
End Function

Function IsZipCode(str)
   Dim izcRegEx
   Set izcRegEx = New RegExp
   izcRegEx.Pattern = "^(\d{5})[ .-]?(\d{4})?$"
   IsZipCode = izcRegEx.Test(ExtractNumbers(str))
End Function

Function FormatPhoneNumber(byVal str)
   If IsNull(str) Then str = ""
   Dim fpRegEx
   Set fpRegEx = New RegExp
   fpRegEx.Pattern = "^(?:1?[ .-]?)\(?([1-9]\d{2})\)?[ .-]?\s?(\d{3})[ .-]?(\d{4})$"
   FormatPhoneNumber = fpRegEx.Replace(str,"($1) $2-$3")
End Function

Function FormatPostalCode(byVal str)
   If IsNull(str) Then str = ""
   Dim fpcRegEx
   Set fpcRegEx = New RegExp
   fpcRegEx.Pattern = "^([a-z]{1}\d{1}[a-z]{1})[ .-]?(\d{1}[a-z]{1}\d{1})$"
   fpcRegEx.IgnoreCase = True
   FormatPostalCode = UCase(fpcRegEx.Replace(str,"$1 $2"))
End Function

Function FormatZipCode(byVal str)
   If IsNull(str) Then str = ""
   Dim fzcRegEx
   Set fzcRegEx = New RegExp
   fzcRegEx.Pattern = "^(\d{5})[ .-]?(\d{4})$"
   FormatZipCode = fzcRegEx.Replace(str,"$1-$2")
End Function

The State and Province listings are as accurate as I can find information for, and seem to correlate with USPS (that's where I got them in the first place).


Ok...I don't read regular expressions very well (if at all)...so what it this doing?
Basically those are validation and formatting functions for Zip code format, Canadian Postal Code format, and valid States/Provinces (i.e. "AK|AR|AZ..."), for instance:

<!-- #include file="includes/functions.asp" -->
<%
If IsZipCode("90210") Then
   Response.Write(FormatZipCode("90210") & "<br />" & vbCrLf)
End If

If IsZipCode("90210 1234") Then
   Response.Write(FormatZipCode("90210 1234") & "<br />" & vbCrLf)
End If
%>

Output:

90210
90210-1234

P.S. as far as I know this information is still current according to the USPS, although according to the CIA and State Department, a very few (such as Federated States of Micronesia) of the U.S. Territories there are independent now - but if you try to figure postage for them with USPS apparently they still come up as U.S. Territories...

Obviously this won't get the City and State from a Zip or Postal code a user entered since you need a database for that, but will help check that they were entered in the right format, etc.

The formatting is just to make them look pretty when you display them on a page, of course... if you need only numeric value in the database, you can just extract the digits once it matches a valid pattern:

Function ExtractNumbers(byVal str)
   If IsNull(str) Then str = ""
   Dim enRegEx
   Set enRegEx = New RegExp
   enRegEx.Pattern = "\D"
   enRegEx.Global = True
   ExtractNumbers = enRegEx.Replace(str,"")
End Function

P.P.S. Looking at the postal code validation regex (one of the first I ever wrote), it could use a bit of shortening - but it works.
Shortened versions (tested):

Function IsPostalCode(str)
   Dim ipcRegEx
   Set ipcRegEx = New RegExp
   ipcRegEx.IgnoreCase = True
   ipcRegEx.Pattern = "^[a-z]\d[a-z][ .-]?\d[a-z]\d$"
   IsPostalCode = ipcRegEx.Test(str)
End Function

Function FormatPostalCode(byVal str)
   If IsNull(str) Then str = ""
   Dim fpcRegEx
   Set fpcRegEx = New RegExp
   fpcRegEx.Pattern = "^([a-z]\d[a-z])[ .-]?(\d[a-z]\d)$"
   fpcRegEx.IgnoreCase = True
   FormatPostalCode = UCase(fpcRegEx.Replace(str,"$1 $2"))
End Function
I did't know this was going to be so huge of a thread.


TO EVERYONE THAT HAS A LOGITIMATE ANSWER:

I get back in the office at 7am central time monday morning, i will review this again and split points for all logitimate answers.


NetMinder:

I just want you and EE to know that i did not intend for someone to post something about "cracking" db

jAy
Avatar of Eric - Netminder
You can get one from your local post office. It might take a while, but you can get it. It's been several years since I got one, but they will provide it. As I recall, it came as a CSV file.

If you have the addresses and just need the zip codes, the US Postal Service will give you all of the ZIP+4 codes for them. When I had it done, it was free, but it might not be now.

Regards,

ep