Import raw Census PUMS data to a database

Abstract

This is a python script for importing raw Census PUMS data into a database. In short, it will take any 1% or 5% sample raw data file, parse the contents into fields, then dump the records into into two tables, 1 each for persons and households.

Author: Jesse Ayers, Center for Urban Simulation and Policy Analysis, University of Washington, www.urbansim.org
First submitted on: 9/28/2007
Last modified on: 9/28/2007

Comments, questions, and suggestions are welcome. See the 'people' section at http://www.urbansim.org for my contact information.

Download

• See attachments section below

Instructions

• Software Requirements:
  • Python 2.5.1
  • SQL Alchemy 0.4
  • Installed and running database (any DBMS supported by SQL Alchemy)
    • This script has been tested on MySQL, Microsoft SQL Server, and PostgreSQL
  • Python database connectivity package for your database type
    • see http://www.sqlalchemy.org/docs/04/intro.html#overview_sqlalchemy_dbms
  • This script
• Run instructions:
  • Download the 1% or 5% raw data file for the state of your choice from http://www.census.gov/main/www/pums.html
  • Edit the script using any Python editor or text editor (e.g. notepad)
  • Scroll to the bottom of the script to the section underneath ' if _name_ == "_main_": '
  • Adjust the various variables to correspond to what you want the script to do
  • Save the changes you made and close the script
  • Run the script:
    • On Windows:
      • Open a command window (start>run then type cmd and press 'OK')
      • Then type <path to python.exe> <path to script>
      • For example: c:\python25\python.exe c:\raw_pums_to_db.py
      • You may also be able to simply double-click the script, but you could miss error messages that way