Closed

MySQL or FileMaker Data Base Import from U.S. Census

This project received 4 bids from talented freelancers with an average bid price of $16 USD / hour.

Get free quotes for a project like this
Employer working
Skills Required
Project Budget
N/A
Total Bids
4
Project Description

Export Census Data from FFIEC website and Import into MySQL or FileMaker DB

Steps:

1. Download 2012 Census Windows Application from [url removed, login to view]
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.
4. Save
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.
Trim Spaces
Substitute "/" with "_"
Substitute " " with "_"
Precede all Fields beginning with a number with "f"

Examples.

I have provided several examples and a base working framework.

1. [url removed, login to view] (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. [url removed, login to view] - 5 primary key fields and then the First 30 something fields.
[url removed, login to view] - 5 primary key fields and then the NEXT 30 something fields.
[url removed, login to view] - 5 primary key fields and then the NEXT 30 something fields.
[url removed, login to view] - 5 primary key fields and then the NEXT 30 something fields.
[url removed, login to view] - 5 primary key fields and then the First 30 something fields.
[url removed, login to view] - 5 primary key fields and then the First 30 something fields.

3. FFIEC [url removed, login to view] - 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).

Looking to make some money?

  • Set your budget and the timeframe
  • Outline your proposal
  • Get paid for your work

Hire Freelancers who also bid on this project

    • Forbes
    • The New York Times
    • Time
    • Wall Street Journal
    • Times Online