You are here:  Home Electronic Reporting Importing Data

How to Import Data from your Spreadsheet

Introduction

The Census Bureau's Surveyor software lets you import information from your own spreadsheets. This requires several steps to set up, so, if you only need to enter information for a single location or a few stores, it is usually less time-consuming to enter the information through the interactive mode of Surveyor or on the paper forms you received in the mail.

If you have a large number of stores, importing the data from one or more spreadsheets may save you a great deal of time. Importing helps you...

• to easily transfer information you already have in spreadsheets
• to enter data for many establishments, particularly in those cases where the same responses apply to each store.

Surveyor can import data from any spreadsheet where each row is associated with a separate establishment. Nonetheless, these instructions assume that you will set up a spreadsheet tailored to Economic Census reporting and then copy data into it from other sources.

A. Verify the Form Inbox

Open Surveyor, and update to the latest version of the software. Then, make sure that the Form Inbox includes every one of your stores, complete with a "Store/Plant" number that you can use to link to information in company records.

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

2. Select Tools | Check for updates. If you originally downloaded Surveyor prior to March 10, you will need to click Next, and later Finish, to download and install the latest copy. Close the Welcome Screen again bring the Form Inbox into view.

3. The Form Inbox should include a line for each of your stores. It may also include a company-wide line associated with a NC-99001, NC-99002, or NC-99003 form.

   

4. If the Store/Plant identifier for any store in the list is blank or incorrect, click on the incorrect Store/Plant identifier and change it right there. Each Store/Plant identifier must be unique. Most stores are numbered 1, 2, 3..., but you may use any alphabetic or numeric characters--whatever will link to your own records. If there are no store/plant identifiers shown, you must add them in order to import data, even if you make them up only for this purpose. (Exception: NC-99001, NC-99002, and NC-99003 forms cannot be imported, so do not require store numbers.) The store number is the only field that can be modified on this screen.

5. If you need to add more stores of the same types for which you already have forms…
   1. Select File | New
      

      
      

   2. Highlight the appropriate form type and click Add.
      

   3. Repeat steps a. and b. until you have a line for each store. (The more stores you have, the longer this step takes for each additional store.)

   4. Give Surveyor a store/plant number for each new form.
      1. Click in the Store/Plant field on the line in the Form Inbox for the desired new store.

      2. Enter the store number that you want to use for matching to company records. Make sure no two stores have the same identifier.

      3. You don't need to enter name, address, and other information at this point, since this information can be imported from a spreadsheet. Only the Store/Plant identifier must be entered interactively.

6. If you need to add stores for which you do not have an appropriate form, select the most similar form from among the ones you already have and perform steps 5.a., b., c., and d. for those stores. Later you will need to explain any question that may not be answerable, in the "Remarks" section of these new forms.
   

B. Export the List of Establishments (optional)

You can export the list of establishments from the Form Inbox, as well as any other data you may have previously entered in Surveyor, for loading into your spreadsheet. This process requires a "map", so, if you have not yet created a map in Step E below, right-click on this link template1-4.gmf, select Save Target As or Save Link Target As (depending on your browser), and save the file to a location you will remember on your computer.

You can correct address or other information once the data are in your spreadsheet--except that the store number must be edited interactively in Surveyor, so that it can guide the importing process.

The result of the export includes data only, no column headings. The only output format available is .csv (comma-separated values), but that format can be loaded into Excel or other data manipulation programs.

1. From the Form Inbox, select Tools | Export...

2. Select Next.

3. Enter the name and location of the map file, e.g., template1-4.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 template1-4.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. Select Next.

   

4. Select Finish.

5. Select Close.

   The resulting .csv file is not attractive, but Excel or other software will load it into columns with no difficulty.

   

C. Mark up a Form

1. Locate a 2002 Economic Census form you received in the mail, or print out a copy from Surveyor. (To print a form, from the Form Inbox, double click on the line for a store to open its form, then select File | Print.) Skip any NC-9900x form, which must be completed interactively. If you have more than one type of form (not counting NC-99001, 2, or 3), pick the one that applies to the most stores.

2. Mark or highlight on the paper form all of the fields that apply to any of your establishments. Where there are Yes or No options, you will need to mark both, unless all of your stores are one or the other.

D. Create a Spreadsheet

Create a spreadsheet with a column for each response category marked in the Step C. above. Include column headings that will be meaningful to you. You may start with the downloadable template spreadsheet (see below).

1. You may use any software that allows you to create a file in .xls (e.g., Excel) or .csv (comma-separated values ASCII) format.

2. Be sure to include a column for the store number. This is what you will be using to merge the information into the forms.

3. Everything does not have to fit in one spreadsheet. You can import different columns from different spreadsheets, as long as each has consistent store numbers.

4. Leave no columns blank for formating. You may also omit any columns for fields that none of your stores need to report.

5. You may wish to do a practice run with only a few columns in your spreadsheet, and carry it through creating a “map” (see below), before you come back and complete this task.

6. Save your spreadsheet to a location you can find again.

7. You may download the template spreadsheet template1-4.xls that includes column headings for Items 1 to 4, which are standard for nearly all forms. That way you only have to add column headings for additional fields in Item 4 (if any) and the remaining Items 5 to 29 that are applicable to one or more of your stores. Save your work to a new file name.
   • If you did not already do so in Step B above, download the mapping file template1-4.gmf by right-clicking on this link, selecting Save Target As, Save Link As, or Save Link Target As (depending on your browser), and saving the file to a location on your computer that you will remember.

8. Item 27 (Leased Departments) or Item 28 (Locations of Operation) on selected forms require special instructions. See the note at the end of Step E.

9. If you have more than one form type to report (not counting NC-99001, 2 or 3), see the discussion under H. below.

E. Create a “Map”

Create a "map" to tell Surveyor which columns in your spreadsheet go with which fields on the form.

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

2. From the Form Inbox, select Tools | Create Import/Export Map.

3. Select Next.

4. Highlight one particular form type corresponding to your spreadsheet and click Next.

5. Enter the file name of your spreadsheet or click on the folder button to bring up a Windows file selection dialog.

   

6. If you are picking up where you left off on a previously saved map, specify its name. If you built your spreadsheet on template1-4.xls, you may enter the location of the mapping file template1-4.gmf here. Click Finish. If you are starting with the template, you may then skip step 8 below.

   

   If you are starting from scratch, the mapping software will open with a screen looking something like this.

   

7. In the upper left, enter a description, any name that will be meaningful to you.

   

8. Link the store number. In the spreadsheet view at the bottom of the screen, where the first rows of your spreadsheet are displayed, make sure the column for the store number is highlighted, and click . This step has been done for you if you started with template1-4.gmf.

   The link then appears in the spreadsheet view at the bottom of the screen.

9. Link each remaining spreadsheet column to a field.
   1. Highlight a column in your spreadsheet

      

   2. Click on the field in the questionnaire where that information should go.

      

   3. Click

      The spreadsheet will now look like this:

      

      Notice in the screen illustration under 9.b. that the “Data Element” RECORD_NAME1 is selected in the "tree view" (righthand column).  RECORD_NAME1 is the name for the place that the Surveyor stores your information for the pink highlighted field, and is the name that is used to identify the link in your map. You may choose to narrow the tree view so that you can see more of the form.

10. Repeat these three steps, E.9.a. through E.9.c., for remaining fields on this questionnaire page, then click the Next Page button, and continue linking fields to columns.

11. Save your map file:
    1. Click File | Save Map File, or, if you started with template1-4.gmf, click File | Save Mapping File As...

    2. Enter a file name.

       

    3. Click Save.

12. Close the visual mapping tool.

    Select File | Close.

Notes:

If, in the process of creating the map or filling the spreadsheet, you find that you left out a column you need, the easiest option is to add that column after the last mapped column in the spreadsheet. If you modify your spreadsheet to insert extra columns among columns previously mapped, you will need to respecify the mapping on all columns to the right of the insertion. The mapping is based on column sequence, not any heading you use.

Certain forms have items that, because they repeat a variable number of times, must be mapped and imported separately. See the special instructions:

• Forms RT-44801, RT-45201, and RT-45202: See Importing leased departments for Item 27.
• Forms FI-52250, FI-52450, FI-52451, IN-51250, IN-51251, IN-51750, IN-51751, and UT-22150: See Importing establishment data into consolidated forms for Item 28.

F. Fill in the Spreadsheet

1. Assign each separate line in the spreadsheet to a different store, and identify it by the unique store number.
   • If you exported the list of establishments to a .csv file in Step B, open that file in Excel, copy the appropriate cells, and then paste them into the spreadsheet where you set up column headings for mapping.

   • If the establishments as exported by Surveyor are not in a sequence that matches your records, resort the data rows using functionality in your spreadsheet software. The importing process pays attention only to store number, not sequence of rows. For example, to sort by store number in Excel, mark all of the data in the establishment rows and select Data | Sort | Column A and OK.

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

3. On dollar and other quantitative fields, add a row for totals. This is for your own use in confirming that store figures add to appropriate company totals. To help you keep it straight, assign this line a store number of "Total". If you have occasion to re-sort the data rows, be sure to reconfirm that the totals still work.

4. Note that all dollar fields (such as sales and payroll) are to be reported in thousands of dollars.

5. For checkbox entries, like yes or no options, use the number "1" or the letter "T" (not "x") as your check mark in the appropriate cell.

6. Note any special formating requirement in the instrument. For example, date fields (e.g., in Item 29 where applicable) are expected to be in MMDDYYYY or MMYYYY format, with no - or / characters.

7. Excel users needing to enter a ZIP Code, EIN, date or other 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.

8. 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, even the ones supplied in the template file. On an item with lots of options, like Item 22, you may wish to include the "census use" key codes in your column headings to help you discriminate the categories.

9. The freeform "Remarks" field below Item 29 cannot be imported. Any explanations necessary for understanding reported data must be entered interactively in Surveyor. (If you need to enter more remarks than the 120-character field allows, use the Business Help Site to send an e-mail message containing the additional information. Include the UID for reference. Click on Contact Us | E-mail Page.)

10. Each field has a maximum length. Any data cell over these limits will not be imported, so it is necessary to truncate long entries in your spreadsheet before attempting to import the data.
    • Name 1 - 36 characters
    • Name 2 - 36
    • Street - 36
    • City - 36
    • Store number - 12
    • Percents - 3 (note, no decimals are accepted)
    • Writein fields - 128

G. 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 map file. You may click on the folder button at the right end of each line to bring up a Windows file selection dialogue, point to the directory where you saved your spreadsheet or mapping file, select that file, and click Open. Select Next.

   

4. Be patient. The importing process can be time consuming for companies with many establishments. Depending on processor speed and the number of data items being imported, importing can take 2 to 5 seconds per establishment. A company with 1,000 establishments could take more than an hour.

5. The importing process will generate a log. Every line not matched will be noted, including the top rows normally devoted to field descriptions, which is not a problem. If the importing process identifies significant problems, click Cancel, modify your spreadsheet or map, and reimport. Otherwise, click Finish to save the results of the importing process. You can alway repeat the importing process, and all nonblank cells will overwrite information previously entered.

H. What if I Need to Complete Multiple Types of Forms?

If you have more than one type of form to complete (not counting NC-99001, 2 or 3), you have several options.

1. If most of your establishments use one form type and you have only a few stores to report on other forms, you will likely create your basic spreadsheet for the items on the most-used form. You may include data for your other stores in the same spreadsheet, insofar as the items apply.
   • You can then enter remaining items for the other stores interactively in Surveyor after your data importing is complete, or

   • You can create one or more supplementary spreadsheets that include only the items that were not included in the first spreadsheet, for only the stores to which they apply. You will need then to create maps and do importing for each additional spreadsheet.

2. You can create independent spreadsheets and corresponding maps for each separate type of store. If the same store number appears in more than one import, the last-imported data will overwrite anything imported previously.

3. You may create an all-encompassing spreadsheet covering the data required for all of your stores or plants on the applicable forms. You will initially map only those fields that apply for the first form you select, but then you may reopen that same map with a second form as a base, and map those fields new to the second form, etc. One importing operation can then bring in all data.

If you have a company with very many establishments, you may elect to use different spreadsheets for different parts of the data, or even to store data for different groups of stores in different spreadsheets, simply to reduce importing times.

If you are responding for multiple companies, or have arranged for the Census Bureau to accept separate reports for different parts of your enterprise, you will need to undertake separate importing operations for each, although the same maps may work. You are dealing with multiple companies or parts if, when you select View | Survey List, the "Available Surveys" pane at the left of your Form Inbox lists "2002 Economic Census" multiple times. You may navigate from company to company by clicking on different lines in that pane. There is no way to change the text on those lines to make them more descriptive.

I. Review Your Work

Review the data in Surveyor for at least selected stores to confirm that the right data were imported into each location on the form. Use File | Print to review a form in hard copy.

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, submit the forms with Tools | Submit Responses … If you have problems with the upload and have a large number of establishments, select Tools | Check for updates to download the newest version of the software, then resubmit.

Thank you.

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