| Documentation: | ACS 2013 (5-Year Estimates) |
you are here:
choose a survey
survey
document
chapter
Publisher: U.S. Census Bureau
Survey: ACS 2013 (5-Year Estimates)
| Document: | ACS 2013-5yr Summary File: Technical Documentation |
| citation: | Social Explorer; U.S. Census Bureau; American Community Survey 2013 5yr Summary File: Technical Documentation. |
ACS 2013-5yr Summary File: Technical Documentation
This tool is another way data users can read the Summary File into Excel, and it is designed to be compatible with any version of the software. While this example uses the 2007-2009 ACS 3-year Summary File, the file layout is similar for the most recent ACS Summary File. To begin using this tool, users must first download the Summary File data, the Excel template file, and the Excel geography file.
The 2007-2009 ACS 3-year Summary File is available at http://www2.census.gov/acs2009_3yr /summaryfile/. In this example, we are accessing the data by selecting "2007-2009 ACSSF By State All Tables," then selecting "California All Geographies.zip." Once downloaded and unzipped to a local directory, this compressed file contains individual text files and one geography file per Summary File sequence. We are using Sequence file 1, so you need to open and save the corresponding estimate and margin of error text files (e20093ca0001000.txt and m20093ca0001000.txt).
Next, you need to download the Excel template file, SummaryFileXLS.zip, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/. (Note: SummaryFileXLS.zip is named SummaryFileTemplates.zip starting with the 2011 release.) Once unzipped to a local directory, this compressed file contains an Excel file for each Summary File sequence. Again, we are using Sequence File 1, so you need to open Seq1.xls.
Finally, you need to download and save the Excel geography file, mini_geofile.xls, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/Geography/ to the same local directory. Each worksheet in the mini_geofile.xls represents geographies that are within a state. Note that the "US" worksheet only contains summary levels that can cross state boundaries. "US" is not the data at the national summary level.
We will walk through an example using sequence 1 for the state of California:
1) When the template file is open in Excel it should appear as below:
2) Place your cursor in cell A3 and click on the Data tab in the Excel tool bar. See below:
3) To import the Summary File text file into Excel click on From Text in the Get External Data section of the tool bar. In this example, we are opening the estimate file for California (e20093ca0001000.txt).
4) Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, and then click Next.
5) Step 2 of the Excel Text Import Wizard will appear. Under Delimiters choose Comma. Users may click Finish to import the file or choose Next to format the Excel columns.
6) A Pop up window will appear to confirm cell A3 as the correct cell. Click OK.
7) The summary file will be imported in Excel as below:
Row 1 - Contains a unique identifier of Table ID and Line Number with a "_" between them
Row 2 - Contains the associated metadata for each unique Identifier
Row 3 - Is the first Row of the imported data
Column A - Is a constant value of "ACSSF" (stands for ACS Summary File)
Column B - Contains the associated metadata for each unique Identifier
Column C - Is the first Row of the imported data
8) Read in the estimates and margins of error for each sequence needed. For example, here is the screenshot of the estimates for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current data):
9) Next, you need to pad zeroes for the logical record number LOGRECNO.Add a column next to LOGRECNO. To do this, Highlight column G, then Right Click and click Insert.
a. For cells G1 and G2, put in LOGRECNO as the label.
b. Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then hit Enter.
c. Copy and paste the formula in cell G3 down column G to the last row of the data.
10) 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 Excel geography file mini_geofile.xls and keep both Seq1.xls and mini_geofile.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,[mini_geofile.xls]ca!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,[mini_geofile.xls]ca!C:D,2,0)
d. Highlight 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.
10) Repeat steps 1 through 10 to obtain the margin of errors for the same sequence for California.
The 2007-2009 ACS 3-year Summary File is available at http://www2.census.gov/acs2009_3yr /summaryfile/. In this example, we are accessing the data by selecting "2007-2009 ACSSF By State All Tables," then selecting "California All Geographies.zip." Once downloaded and unzipped to a local directory, this compressed file contains individual text files and one geography file per Summary File sequence. We are using Sequence file 1, so you need to open and save the corresponding estimate and margin of error text files (e20093ca0001000.txt and m20093ca0001000.txt).
Next, you need to download the Excel template file, SummaryFileXLS.zip, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/. (Note: SummaryFileXLS.zip is named SummaryFileTemplates.zip starting with the 2011 release.) Once unzipped to a local directory, this compressed file contains an Excel file for each Summary File sequence. Again, we are using Sequence File 1, so you need to open Seq1.xls.
Finally, you need to download and save the Excel geography file, mini_geofile.xls, from http://www2.census.gov/acs2009_3yr/summaryfile/UserTools/Geography/ to the same local directory. Each worksheet in the mini_geofile.xls represents geographies that are within a state. Note that the "US" worksheet only contains summary levels that can cross state boundaries. "US" is not the data at the national summary level.
We will walk through an example using sequence 1 for the state of California:
1) When the template file is open in Excel it should appear as below:
2) Place your cursor in cell A3 and click on the Data tab in the Excel tool bar. See below:
3) To import the Summary File text file into Excel click on From Text in the Get External Data section of the tool bar. In this example, we are opening the estimate file for California (e20093ca0001000.txt).
4) Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, and then click Next.
5) Step 2 of the Excel Text Import Wizard will appear. Under Delimiters choose Comma. Users may click Finish to import the file or choose Next to format the Excel columns.
6) A Pop up window will appear to confirm cell A3 as the correct cell. Click OK.
7) The summary file will be imported in Excel as below:
Row 1 - Contains a unique identifier of Table ID and Line Number with a "_" between them
Row 2 - Contains the associated metadata for each unique Identifier
Row 3 - Is the first Row of the imported data
Column A - Is a constant value of "ACSSF" (stands for ACS Summary File)
Column B - Contains the associated metadata for each unique Identifier
Column C - Is the first Row of the imported data
8) Read in the estimates and margins of error for each sequence needed. For example, here is the screenshot of the estimates for sequence 1 (the screenshots are for illustration purposes only and may not reflect the current data):
9) Next, you need to pad zeroes for the logical record number LOGRECNO.Add a column next to LOGRECNO. To do this, Highlight column G, then Right Click and click Insert.
a. For cells G1 and G2, put in LOGRECNO as the label.
b. Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then hit Enter.
c. Copy and paste the formula in cell G3 down column G to the last row of the data.
10) 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 Excel geography file mini_geofile.xls and keep both Seq1.xls and mini_geofile.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,[mini_geofile.xls]ca!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,[mini_geofile.xls]ca!C:D,2,0)
d. Highlight 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.
10) Repeat steps 1 through 10 to obtain the margin of errors for the same sequence for California.
