Export Census Data from FFIEC website and Import into MySQL or FileMaker DB
1. Download 2012 Census Windows Application from http://www.ffiec.gov/hmda/censusproducts.htm#censusdata
2. Export all Colorado Data for Demographics, Housing, and Income. Exports are limited to 30 fields. Each Table has 300+ Fields.
3. Use 3 FileMaker or MySQL Tables for importing Demographics, Housing, and Income data. Relate tables using County Code, MSA Code, State Code, Tract Code and Year.
Numeric Fields should be type numeric. If you cant tell by the data, make it type text.
4. Import relevant Census Data into Filemaker table. Maintain Exact Field names across Import.
Notes. The 2012 Census Windows Application does not perform a clean Excel export. Rather, one should plan to export approximately 10-15 excel extracts per Census table and:
1. Export to Excel
2. Open Excel and Concatenate Rows 1 & 2 into a new row #1
3. Delete the old rows 1 and 2.
5. Prepare the FileMaker or mySql Table for Import
6. Import into Filemaker or mySql Using original field names and in the order they originally appear.
Please save the Excel sheets with a naming convention that identifies the underlying data.
Fields. Convert the Fieldnames in this manner.
Substitute "/" with "_"
Substitute " " with "_"
Precede all Fields beginning with a number with "f"
I have provided several examples and a base working framework.
1. FFIEC.fmp12 (If you would like to do this in 8,9,10 or 11 that is Fine!)
This is the started FileMaker database. I have imported the first 4 extracts of the Census Demographics table, the 1st extract of the Income table and the 1st extract of the Housing Table.
"Extract" means that you can only export 35 fields at a time. The first 5 fields of each export should be the matching fields (identified above) and then the 30 next fields.
THE FOLLOWING EXCEL FILES ARE THE EXCEL RESULTS AFTER I HAVE MODIFIED THE COLUMN NAMES.
2. Demographics01.xls - 5 primary key fields and then the First 30 something fields.
Demographics02.xls - 5 primary key fields and then the NEXT 30 something fields.
Demographics03.xls - 5 primary key fields and then the NEXT 30 something fields.
Demographics04.xls - 5 primary key fields and then the NEXT 30 something fields.
Income.xls - 5 primary key fields and then the First 30 something fields.
Housing.xls - 5 primary key fields and then the First 30 something fields.
3. FFIEC Data.bmp - Screenshot of the Census Windows Application.
4. If you plan on using MySQL rather than FileMaker, contact me for login details.
5. If you want to use FileMaker fm7 format, contact me for a baseline database (My example is FM12).