1) Download estimates and margins of error zipped files from the browser Internet Explorer and unzip to a local directory.
2) Download Excel geography files to the same local directory. Geography files are in http://www2.census.gov/acs2009_5yr/summaryfile/UserTools/Geography
. For example for California, download ca.xls, and for summary levels that can cross state boundaries, download us.xls. Note that US geographies (us.xls) and US zipped estimates and margins of error files are for those summary levels that can cross state boundaries. They are not the data at the national summary level.
3) Download the Summary File Excel Import Tool from http://www2.census.gov/acs2009_5yr/ summaryfile/UserTools/2005-2009_SummaryFileXLS.zip
. Unzip the file to the same local directory.
4) Follow the instructions in Section 3.3 to read in the data by the sequence. For example, here is the screenshot for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current 5-year data):
5) Next, pad 0s for logical record number LOGRECNO.
a. Add a column next to LOGRECNO. To do this, Highlight
column G, then Right Click
and click Insert
b. For cells G1 and G2, put in LOGRECNO
as the label.
cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3
, then hit Enter
d. Copy and paste the formula in cell G3 down column G to the last row of the data.
6) Add geographies by using common merged keys.
a. To add geographies, insert two extra columns next to the padded LOGRECNO column G, and label them GEOID and Geography Name. Open the geography ca.xls file and keep both Seq1.xls and ca.xls files open.
b. Add GEOID by using LOGRECNO as the common merged key from both Seq1.xls and ca.xls. Highlight
cell H3 and enter the formula: =VLOOKUP(G3,[ca.xls]Sheet1!B:C,2,0)
c. Add geography names by using GEOID as the common merged key from both Seq1.xls and ca.xls. Highlight
cell I3 and enter the formula: =VLOOKUP(H3,[ca.xls]Sheet1!C:D,2,0)
both cells H3 and I3 and right click
to copy the formulas.
e. Copy and paste the formulas down the H3 and I3 columns to the last row of the data.