2007/10/11

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.

Labels: , ,

posted by Scott at 12:08 PM

36 Comments:

  • Thanks. I needed this quickly and got it off your site. It did come in handy.

    By Anonymous Anonymous, At 12/04/2007 3:43 PM  

  • 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.

    By Blogger Scott, At 12/04/2007 5:40 PM  

  • thanks a lot saved me some time.

    By Anonymous Anonymous, At 3/19/2008 5:42 PM  

  • rock on. thanks. helped a bunch.

    By Anonymous Anonymous, At 4/08/2008 12:33 AM  

  • Nice one, thanks for making this available.

    Cheers,
    Stephen

    By Anonymous Stephen, At 4/11/2008 3:38 AM  

  • thanks!

    By Anonymous Anonymous, At 4/12/2008 6:00 AM  

  • Your generosity is greatly appreciated! Thanks for thinking of others!

    By Blogger Myles Bostwick, At 4/21/2008 4:06 PM  

  • thanks for the sql script!

    By Blogger Mark D., At 5/10/2008 10:06 PM  

  • Thanks, saved me some time. Good karma heading your way!

    By Blogger Matthew R Hall, At 5/30/2008 11:24 AM  

  • Thanks, Scott! You helped me out as well!

    Cheers!

    By Anonymous Harmony, At 6/17/2008 8:32 AM  

  • Thanks a bunch. Your list has saved me time on more than one occasion.

    By Anonymous Jerimiah Brown, At 6/23/2008 9:43 PM  

  • Thank you, was going to grab off Wikipedia :D

    By Blogger Xander, At 7/05/2008 8:49 PM  

  • Thank You SOOOOO MUCH!

    By Anonymous Anthony, At 7/21/2008 6:34 PM  

  • awesome, thanks, couldn't believe i couldn't find it anywhere else.

    By Blogger Alexis, At 7/23/2008 11:48 AM  

  • Thanks a ton.
    Just what I was looking for!

    By Anonymous Renegade, At 7/31/2008 6:02 AM  

  • 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');

    By Anonymous Randall, At 8/12/2008 10:18 PM  

  • I used this today. Thanks.

    By Anonymous Anonymous, At 9/03/2008 2:13 AM  

  • thanks so much baby

    By Anonymous Ali, At 9/11/2008 11:11 AM  

  • 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

    By Anonymous Chadwick Meyer, At 9/22/2008 7:05 PM  

  • Very handy, thanks!

    By Anonymous Anonymous, At 10/07/2008 5:41 PM  

  • Thank you so much.

    By Anonymous Anonymous, At 10/25/2008 11:35 AM  

  • Much appreciated. Thank you!

    By Anonymous Sam, At 10/28/2008 6:15 PM  

  • thanks!!! This really helped me and saved a lot of time!!!

    By Anonymous John, At 10/29/2008 2:45 PM  

  • 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"

    By Blogger Spoom, At 11/13/2008 8:35 AM  

  • Thank you oh so very much.

    By Blogger Nikolai Rasputin, At 12/05/2008 7:23 AM  

  • Thanks!

    Dan Moore

    By Anonymous Anonymous, At 12/05/2008 7:14 PM  

  • Thanks. This page is the first result in Google for "us states list csv", by the way.

    By Anonymous Jarett, At 1/22/2009 1:10 PM  

  • Thanks. Just what the doctor ordered. Let me know if we can return the favor to you sometime.

    By Anonymous ROIGuy, At 2/10/2009 10:37 PM  

  • www.Broomfield-Designers.com thanks you for the custom code. We needed cities too.....

    For cities, we used this.
    Gov't census data by state. Cut out the first row from each one for the city data. Add them as you need them. Alabama is 461 cities for instance.

    http://www.census.gov/popest/cities/SUB-EST2007-4.html

    By Blogger tbone, At 3/01/2009 12:10 PM  

  • Thank you very much for the SQL..Saved me a bunch of time..

    By Blogger Kudithipudi, At 3/18/2009 4:22 PM  

  • Thanks a million... Or at least 50.

    By Anonymous Anonymous, At 4/10/2009 6:59 PM  

  • Thanks a lot for the data

    By Blogger nomadic, At 4/20/2009 2:17 AM  

  • Awesome! Thanks for the time saver!!

    By Anonymous JRedd Designs, At 8/31/2009 12:48 PM  

  • awesome, thank you so much for this time saver

    By Anonymous chris hough, At 9/10/2009 5:52 AM  

  • You are the man!!! Many thanks from New York! ;)

    By Blogger Justin, At 10/26/2009 10:00 PM  

  • this is exactly what i needed! saves me many valuable minutes. thanks from a busy non-profiteer!

    By Blogger Jessica, At 3/05/2010 10:42 AM  

Post a Comment



Create a Link

<< Home