U.S. States CSV / SQL
I needed a mySQL table containing the common names of the U.S. States and their two letter abbreviations. I quickly Googled and couldn't find anything. Maybe I didn't search long enough. All I found was a Wikipedia article that listed them.
I grabbed the contents, formatted them with BBEdit to a CSV and imported them into mySQL. Just so no one else has to do that, here you go: state.csv, state.sql
Hope this helps someone.
I grabbed the contents, formatted them with BBEdit to a CSV and imported them into mySQL. Just so no one else has to do that, here you go: state.csv, state.sql
Hope this helps someone.
Labels: CSV, mySQL, U.S. States
posted by Scott at
12:08 PM
26 Comments:
Thanks. I needed this quickly and got it off your site. It did come in handy.
If anyone who comes across this finds any variant of the information useful, such as a Tab file, let me know and I can add it. Also if anyone has any similar types of info, like Canadian Territories, I'd happily post that as well.
thanks a lot saved me some time.
rock on. thanks. helped a bunch.
Nice one, thanks for making this available.
Cheers,
Stephen
thanks!
Your generosity is greatly appreciated! Thanks for thinking of others!
thanks for the sql script!
Thanks, saved me some time. Good karma heading your way!
Thanks, Scott! You helped me out as well!
Cheers!
Thanks a bunch. Your list has saved me time on more than one occasion.
Thank you, was going to grab off Wikipedia :D
Thank You SOOOOO MUCH!
awesome, thanks, couldn't believe i couldn't find it anywhere else.
Thanks a ton.
Just what I was looking for!
Thanks for the files.
This file is from phpMyAdmin like yours. However it was not outputted but copied and pasted which is easier to read and edit.
I added some codes for USA non states (e.g. Virgin Islands). Created better names. If you replace RegionsT with a table name you like (e.g. StatesT) before you import, your work is finished.
I am also looking for Country codes, Postal codes, etc.
I am trying to maintain a global sense - hince RegionsT rather than StatesT.
-- phpMyAdmin SQL Dump
-- version 2.11.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 12, 2008 at 05:05 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `template`
--
-- --------------------------------------------------------
--
-- Table structure for table `RegionsT`
--
DROP TABLE IF EXISTS `RegionsT`;
CREATE TABLE IF NOT EXISTS `RegionsT` (
`Name` varchar(50) NOT NULL,
`Abbr` char(2) NOT NULL,
PRIMARY KEY (`Abbr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `RegionsT`
--
INSERT INTO `RegionsT` (`Name`, `Abbr`) VALUES
('Armed Forces - Americas', 'AA'),
('Armed Forces - Europe', 'AE'),
('Armed Forces - Africa', 'AF'),
('Alaska', 'AK'),
('Alabama', 'AL'),
('Armed Forces - Pacific', 'AP'),
('Arkansas', 'AR'),
('American Samoa', 'AS'),
('Arizona', 'AZ'),
('California', 'CA'),
('Colorado', 'CO'),
('Connecticut', 'CT'),
('District of Columbia', 'DC'),
('Delaware', 'DE'),
('Florida', 'FL'),
('Federated States of Micronesia', 'FM'),
('Georgia', 'GA'),
('Hawaii', 'HI'),
('Iowa', 'IA'),
('Idaho', 'ID'),
('Illinois', 'IL'),
('Indiana', 'IN'),
('Kansas', 'KS'),
('Kentucky', 'KY'),
('Louisiana', 'LA'),
('Massachusetts', 'MA'),
('Maryland', 'MD'),
('Maine', 'ME'),
('Marshall Islands', 'MH'),
('Michigan', 'MI'),
('Minnesota', 'MN'),
('Missouri', 'MO'),
('Northern Mariana Islands', 'MP'),
('Mississippi', 'MS'),
('Montana', 'MT'),
('North Carolina', 'NC'),
('North Dakota', 'ND'),
('Nebraska', 'NE'),
('New Hampshire', 'NH'),
('New Jersey', 'NJ'),
('New Mexico', 'NM'),
('Nevada', 'NV'),
('New York', 'NY'),
('Ohio', 'OH'),
('Oklahoma', 'OK'),
('Oregon', 'OR'),
('Pennsylvania', 'PA'),
('Puerto Rico', 'PR'),
('Palau', 'PW'),
('Rhode Island', 'RI'),
('South Carolina', 'SC'),
('South Dakota', 'SD'),
('Tennessee', 'TN'),
('Texas', 'TX'),
('Utah', 'UT'),
('Virginia', 'VA'),
('Virgin Islands', 'VI'),
('Vermont', 'VT'),
('Washington', 'WA'),
('Wisconsin', 'WI'),
('West Virginia', 'WV'),
('Wyoming', 'WY');
I used this today. Thanks.
thanks so much baby
Thanks, that was helpful. Those looking for a postal code list with lat/long will find this helpful as well:
http://www.aggdata.com/free/zip-code
Very handy, thanks!
Thank you so much.
Much appreciated. Thank you!
thanks!!! This really helped me and saved a lot of time!!!
Hi Scott,
Found your list quite useful. I added Canadian provinces for a site I'm working on so I figured I would post it here.
"Alberta","AB"
"British Columbia ","BC"
"Manitoba","MB"
"New Brunswick","NB"
"New Foundland","NL"
"Northwest Territories","NT"
"Nova Scotia","NS"
"Nunavut","NU"
"Ontario","ON"
"Prince Edward Island","PE"
"Quebec","QC"
"Saskatchewan","SK"
"Yukon Territory","YT"
"Alabama","AL"
"Alaska","AK"
"Arizona","AZ"
"Arkansas","AR"
"California","CA"
"Colorado","CO"
"Connecticut","CT"
"Delaware","DE"
"Florida","FL"
"Georgia","GA"
"Hawaii","HI"
"Idaho","ID"
"Illinois","IL"
"Indiana","IN"
"Iowa","IA"
"Kansas","KS"
"Kentucky","KY"
"Louisiana","LA"
"Maine","ME"
"Maryland","MD"
"Massachusetts","MA"
"Michigan","MI"
"Minnesota","MN"
"Mississippi","MS"
"Missouri","MO"
"Montana","MT"
"Nebraska","NE"
"Nevada","NV"
"New Hampshire","NH"
"New Jersey","NJ"
"New Mexico","NM"
"New York","NY"
"North Carolina","NC"
"North Dakota","ND"
"Ohio","OH"
"Oklahoma","OK"
"Oregon","OR"
"Pennsylvania","PA"
"Rhode Island","RI"
"South Carolina","SC"
"South Dakota","SD"
"Tennessee","TN"
"Texas","TX"
"Utah","UT"
"Vermont","VT"
"Virginia","VA"
"Washington","WA"
"West Virginia","WV"
"Wisconsin","WI"
"Wyoming","WY"
Thank you oh so very much.
Thanks!
Dan Moore
Post a Comment
Links to this post:
Create a Link
<< Home