It is strongly advised that data users read the most current Summary File Core Tech Doc before attempting to import data into Excel. It is available at https://www.census.gov/programs-surveys/acs/technical-documentation/summary-file-documentation.html.

This document will provide an example of how to read into Excel the 2013 1-year estimates found in Sequence 1 for the state of Maryland. These same procedures can be followed for different data files and geographies.

To read the Summary File into Excel, users will need three files.

1. Summary file data
2. Excel template
3. Excel geography file

Procedures for accessing the summary file data:

1. Visit http://www2.census.gov/programs-surveys/acs/summary_file/
2. Choose year
3. Select data/
4. Choose file type
5. Choose file
   a. If accessing a particular sequence file, first choose State then choose file
   b. Note that the "US" worksheet only contains geographic summary levels cross state boundaries. "US" is not data for the nation.
6. In this example, we're opening and saving the 2013 ACS 1-year estimates in Sequence 1 for the state of Maryland at https://www2.census.gov/programssurveys/acs/summary_file/2013/data/1_year_seq_by_state/Maryland/20131md0001000.z ip.

Procedures for accessing the Excel template:

1. Visit http://www.census.gov/programs-surveys/acs/data/summary-file.html
2. Choose desired year from tabs midway down the page
3. Select appropriate template zip file from Templates section. Note: the template contains two spreadsheet tabs, "E" and "M", to accommodate both the estimates and margins of error.
4. In this example, we're opening and saving the 2013 ACS 1-year Templates zip file at https://www2.census.gov/programssurveys/acs/summary_file/2013/data/2013_1yr_Summary_FileTemplates.zip. The template file we will use is "Seq1."

Procedures for accessing the geography file

1. Visit http://www2.census.gov/programs-surveys/acs/summary_file/
2. Choose your year of interest
3. Select documentation/
4. Select geography/
5. Choose desired geography file:
   a. 1-year files are labeled 1_year_Mini_Geo
   b. For 5-year estimates, select the 5yr_year_geo/ folder then choose a state file
   c. Note that geo files are not available for pre-2009 datasets
   
   Unzip the files to a single local directory. Open the template and follow the steps below.
   Note: The screenshots are for illustration purposes only and may not reflect current data Excel Import Tool Instructions
   
   1) When the template file is open in Excel it should appear as below:
   
   
   
   Note: You may want to adjust the column height and witdth.
   
   2) Place your cursor in cell A3 and select the Data tab in the Excel tool bar.
   
   
   
   3) To import the Summary File text file into Excel, select From Text in the Get External Data section of the tool bar, then choose the desired estimate file. In this example, we are opening the estimate file for Maryland (e20131md0001000.txt).
   
   
   
   4) Step 1 of the Text Import Wizard will appear. Under Original data type choose Delimited, then select Next.
   
   
   
   5) Step 2 of the Excel Text Import Wizard will appear. Under Delimiters choose Comma. Users may select Finish to import the file or select Next to format the Excel columns.
   
   
   
   6) A Pop up window will appear to confirm cell A3 as the correct cell. Select OK.
   
   
   
   7) The summary file will be imported into Excel as shown 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:
   
   
   
   9a) 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 select Insert.
   
   
   
   9b) For cells G1 and G2, make LOGRECNO the label.
   
   
   
   9c) Highlight cell G3 and enter the formula =REPT("0",7-LEN(F3))&F3, then press Enter.
   
   
   
   9d) Next, you must apply this formula to all cells in column G. One way to do this is by clicking on cell G3 then moving your cursor over the bottom right corner so that it becomes a small cross. Click your mouse and drag the cell to the last row of the spreadsheet.
   
   
   
   10a) Add geographies by using common merged keys. Insert two extra columns next to the padded LOGRECNO column G, label them GEOID and Geography Name.
   
   
   
   10b) Add GEOID by using LOGRECNO as the common merged key from both Seq1.xls and 1_year_Mini_Geo.xls. Highlight cell H3 and enter the formula =VLOOKUP(G3,[1_year_Mini_Geo.xlsx]md!B:C,2,0)
   Press enter then locate and select the geography file when prompted.
   
   Note - "1_year_Mini_Geo.xlsx" and "md" in the above formula should change to reflect the particular file and state you are attempting to open.
   
   
   
   10c) Add geography names by using GEOID as the common merged key from both Seq1.xlsx and 1_year_Mini_Geo.xls. Highlight cell I3 and enter the formula: =VLOOKUP(H3,[1_year_Mini_Geo.xlsx]md!C:D,2,0)
   Press enter then locate and select the geography file when prompted.
   The note from Step 10b also applies to this step
   
   
   
   10d) Highlight both cells H3 and I3 and drag the formula to the bottom of the spreadsheet as done in Step 9d, then choose the geography file when prompted.
   
   
   
   Your final spreadsheet should look as follows:
   
   
   
   11) Repeat steps 1 through 10 using the margin of error file to obtain the margins of error for the same sequence for Maryland. The template contains two spreadsheet tabs, "E" and "M", to accommodate both the estimates and margins of error.