You are here:  Home Electronic Reporting Importing Data Consolidated Forms

How to Import Establishment Data into Consolidated Forms

What is a Consolidated Form?

Consolidated forms allows a single report covering multiple locations for most measures, like sales, but also require an enumeration of the covered locations together with their kind of business, employment and payroll. Consolidated reports require special treatment in the importing process because the number of responses is variable. We use the term "establishment" to refer to each individual location at which business is done, and "sequence" to refer to the set of responses for a variable number of establishments.

Note three important details concerning consolidated forms:

1. These procedures apply only to selected forms, forms with "(Consolidated)" in the title: FI-52250, FI-52450, FI-52451, IN-51250, IN-51251, IN-51750, IN-51751, and UT-22150. Only Items 28A and 28B in the consolidated forms involve sequences.

2. Importing data in a sequence is an operation separate from importing data into the main portion of the survey. Further, you can import data into only one type of sequence at a time, so if you need to import data for both pre-listed locations (Item 28A) and new locations (Item 28B), you will go through the importing dialogue twice.

3. Before you can import data for new locations in Item 28B, you must first create a record for each new location interactively in Surveyor.

A. Download Template Files

• template28.xls spreadsheet template
• template28a.gmf map for pre-listed locations (Item 28A)
• template28b.gmf map for new locations (Item 28B)

These templates should be adequate, but if you want to understand how the map was created see the discussion of creating a map for sequence data.

B. Export the List of Establishments

The Economic Census requires a record for every one of your locations. Those locations already in the census database are pre-listed in this instrument, so it logically makes sense to export that list to serve as the basis for the spreadsheet you will fill out and later import back into Surveyor.

1. Start Surveyor if you have not already. Choose Start | Programs | U.S. Census Bureau | Surveyor. Close the Welcome Screen.

2. Supply an artificial store number. On the line for the form with number ending in 50 or 51, click in the Store/Plant cell, then enter an identifier. Logically no store number applies, since this is a consolidated report for a number of locations, but the importing process requires a store number. Any character string will do, as long as it is consistent between this form and the spreadsheet to be imported. If your company received more than one consolidated form, enter a different Store/Plant identifier for each one.

   

3. Select Tools | Export...

4. Select Next.

5. Enter the name and location of the map file template28a.gmf. You may click on the folder button at the right end of that line to bring up a Windows file selection dialogue, point to the directory where you saved template28a.gmf, select that file, and click Open. Next, specify a valid file name for the output you are about to create, including the .csv extension. The only output format available is .csv (comma-separated values), but that format can be loaded into Excel or other data manipulation programs.

   

6. Select Next.

7. Select Finish.

8. Select Close.

C. Load Pre-Listed Locations into a Spreadsheet and Add New Locations

1. Open template28.xls in your software. This gives you column headings.

   

2. Open the .csv file you exported in your spreadsheet software. If you are using Excel, select File | Open, navigate to the appropriate directory, select a file type of "Text files (*.prn, *.txt; *.csv)", and select the file you just exported.

   

3. Copy and paste the data from the .csv into the template spreadsheet. In Excel, click on the gray row label for row 1, then shift-click on the gray row label for the last data row, and select Edit | Copy. Select Window | template28.xls, click in cell A3, and then select Edit | Paste.

   

4. The first column is the Store Number, as was entered on the consolidated record in the Form Inbox. The second column is a "Sequence Index" identifying each location. This number is generated by Surveyor, and must not be changed for pre-listed locations. Note that it starts with ‘0’. Unlike the store number, which can be any unique character string, the Sequence Index must take the form 0, 1, 2, 3, etc. If you have your own store number that you need in order to match with your own records, you can enter that in column J.

5. At this point, you will want to review the list to identify if any of your establishments are missing from the list. Add the new ones, perhaps copying and pasting from another spreadsheet. Make sure you have not accidentally duplicated an establishment already pre-listed.

6. Assign a Sequence Index for the new locations. For each row in which there is no number in the "Seq-Pre" column, enter a number in the "Seq-New" column. These Sequence Indexes start over with 0 and continue in the series 0, 1, 2, 3.... The last Sequence Index will be one less than the total number of new locations.

7. Save your work to a new name.

8. The Form Inbox and spreadsheet below illustrate a company that received two consolidated forms. The motion picture group was assigned a store identifier of Mopic and the sound recording group was labelled Sound.

   

   Each group has a few pre-listed establishments and two or three new establishments. Note that all of the data can be assembled in a single spreadsheet, but that sequence numbers must start over at zero within each group and type.

   

D. Fill in the Spreadsheet

1. Once the sequence indexes are set, you may resort the file as convenient for merging in your other data

2. Cut and paste information from other spreadsheets into this spreadsheet.

3. On employment and payroll items, you may find it useful to add a row for totals to help you determine that establishment figures add to appropriate totals for this group. As long as you leave the "Seq" column blank, the totals will not interfere with the importing of the other data.

4. Note that all payroll figures are to be reported in thousands of dollars.

5. Be sure to use the questionnaire and its accompanying instructions as your guide on how to respond in each column, not the cryptic column headings in your spreadsheet.

6. For checkbox entries (columns U to Y), use the number "1" or the letter "T" (but not "x") as your check mark in the appropriate cell.

7. There are some differences between the items requested for pre-listed locations (in Item 28A) and for new locations (in Item 28B).

• Pre-listed locations are assigned a NAICS code, but for new locations you are asked to supply a two-digit code (as illustrated in the spreadsheet above). The values are documented at the top of the interactive Surveyor screen for Item 28B. The set of codes varies from form to form.

• Pre-listed locations are asked their operational status (in operation, inactive, ceased operation, sold, other) (Columns U to Y), and, for those that ceased operation or were sold, the date thereof (Column AA). For new locations you are asked to supply a date opened and a date acquired, if applicable. (Columns AG and AH).

• For pre-listed locations sold, you are asked the name and address of the new owner in Item 28A. For new locations acquired, you are asked the name and address of the former owner in Item 28B. This spreadsheet uses the same columns (AB to AF) to cover both types of information.

8. Excel users needing to enter a ZIP Code, date or code field with a leading zero (e.g. the date 06302002) will need to right-click on the appropriate column heading, select Format Cells, then select Text.
   • If you already have codes entered without leading zeroes, such as when you are bringing in data exported from Surveyor as a .csv file, right-click on the appropriate column heading, select Format Cells, then select Custom under Category in the Number tab, and then enter into the "Type" box a string of zeroes equal to the number of characters in the code--5 or 9 for a zip code, 8 or 6 for a date, 9 for an EIN.

E. Create Records for the New Locations

1. From the Surveyor's Form Inbox, double click on the record for the form you have been working on (with number ending in 50 or 51.)

2. In the Navigation Panel, click on Item 28.

3. In Item 28, enter the number of locations currently in operation. Click Next Page.

4. Click Next Page again.

5. At the bottom of this page, click the (plus sign) until the bar in the center reflects the number of locations missing in Item 28A. You will be importing all of the other information requested on this form.

   

6. Select File | Close and confirm Yes that you want to save changes in the form.

F. Import the Spreadsheet Data

1. From the Form Inbox, select Tools | Import...
   
2. Follow the instructions on the next two screens.
   
3. Enter the name of the spreadsheet file and the name of the template map file for Item 28A.

   

4. Be patient. The importing process can be time consuming for companies with many locations. Depending on processor speed and the number of data items being imported, importing can take 2 to 20 seconds per location. A company with 1,000 locations could take hours.

5. The importing process will generate a log. Every line not matched will be noted, including the top rows normally devoted to field descriptions.
   • Importing row 0
     Row 0:The index Seq-Prelisted is invalid
     Importing row 1
     Row 1:The index is invalid
   If you have both pre-listed locations and new ones in the same spreadsheet, you will also get log entries The index is invalid for all lines not involved in the current import action (e.g., lines for new locations when you are importing Item 28A). You may ignore these log entries.

   If the importing process identifies significant problems, you may click Cancel, modify your spreadsheet or map, and reimport. Otherwise, click Finish to save the results of the importing process. You can always repeat the importing process, and all nonblank cells will overwrite information previously entered.

6. If you have any new establishments to import, repeat Steps 1-5, substituting template28b.gmf in the second box in Step 3.

G. Review Your Work

Review the data in Surveyor for at least selected locations to confirm that the right data were imported into each field on the form. Use File | Print Attachment to review information from Items 28A or 28B in hard copy. Your browser will open and list census data element names and the value of your entered data.

If you have only a few corrections, you may make them interactively in Surveyor by double-clicking on the line for the store and entering revisions in the report form. You may, however, prefer to change the data in your spreadsheet, then import the data again. All data in nonblank cells in mapped fields will overwrite data previously entered in Surveyor. Because imported blank cells do not overwrite data already in Surveyor, enter zero or other nonblank response if you need to wipe out a previously entered cell.

When review is complete, and provided the data in the standard forms are complete as well, submit the forms with Tools | Submit Responses …

Thank you.

If you have any problems or comments on these instructions, contact us via our e-mail page (new window).

Return to the general Importing Data from Your Spreadsheet.