Full USA Zip Code Database Download
Recently I needed a zip code database table for a project but had a horrible time finding one on-line. Seeing as how this is public data I was astounded at how difficult it was to find. Well finally I put together a csv file of all the USA Zip Codes and associated city and county information and have provided the full file, free to download, on my blog. You can click on the link below to download the zipped CSV file with all the zip code informaiotn. This CSV zip code file can be imported into many database programs. I used the DB Web Admin with Plesk to import it into a MYSQL database. The one problem I did find is that I believe the file has too many rows for an Excel Import.
Columns Included in the CSV Zip Code database file: Zip Code, City, State, Area Code, City Alias, City Alias Abbreviation, City Type, County name, State FIPS, County FIPS, Time Zone, Daylight Saving Status, Latitude, Longitide, and Elevation.
Click Here to Download the Zip Code CSV File
NOTE: Because this file includes not only the City but also the City Alias each zip code has multiple rows in the zip code table. Once you have the table set up you can always drill down the zip code data in any way you need. I personally removed all of the city aliases to trim down the table.
Columns Included in the CSV Zip Code database file: Zip Code, City, State, Area Code, City Alias, City Alias Abbreviation, City Type, County name, State FIPS, County FIPS, Time Zone, Daylight Saving Status, Latitude, Longitide, and Elevation.
Click Here to Download the Zip Code CSV File
NOTE: Because this file includes not only the City but also the City Alias each zip code has multiple rows in the zip code table. Once you have the table set up you can always drill down the zip code data in any way you need. I personally removed all of the city aliases to trim down the table.
4 Comments:
Excellent!! As often as it is used there is nothing else available in the public domain. Did you by chance automate this data collection process so that you could run it, say monthly, to update the list? If not, I would be interested in your sources so I could do it.
Thanks
Here's how to get it into MySQL using mysqlimport:
mysqlimport -L --ignore-lines=1 --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" -c zip_code,city,state,area_code,city_alias_name,city_alias_abbr,city_type,county_name,state_fips,county_fips,time_zone,day_light_saving,latitude,longitude,elevation -u root -p -v [NAME_OF_DB] [NAME_OF_CSV_FILE]
And here's some SQL to create your table (do this first):
CREATE TABLE `zipcodes` (
`zip_code` int(5) unsigned default NULL,
`city` varchar(100) default NULL,
`state` char(2) default NULL,
`area_code` varchar(20) default NULL,
`city_alias_name` varchar(100) default NULL,
`city_alias_abbr` varchar(100) default NULL,
`city_type` char(1) default NULL,
`county_name` varchar(100) default NULL,
`state_fips` int(3) unsigned default NULL,
`county_fips` int(4) unsigned default NULL,
`time_zone` int(2) default NULL,
`day_light_saving` char(1) default NULL,
`latitude` float default NULL,
`longitude` float default NULL,
`elevation` int(5) default NULL
) TYPE=MyISAM;
Thank you very much. Why we have to go through such loss of time for information that should be simple and FREE I'll never really know. I guess its just greed on the part of those that 'hold' the information. Well you do not operate this way and for that I thank you for sharing your information. Mitch
Man This is a noble deed :) you saved me hours of work. Thanks, thanks and thanks a lot :)
Post a Comment
<< Home