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.
- 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.
- 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.
- 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.
- 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.
- If you need to add more stores of the same types for which you already have
forms…
- Select File | New
- Highlight the appropriate form type and click Add.
- 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.)
- Give Surveyor a store/plant number for each new form.
- Click in the Store/Plant field on the line in the Form Inbox for
the desired new store.
- Enter the store number that you want to use for matching to company
records. Make sure no two stores have the same identifier.
- 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.
- 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.
- From the Form Inbox, select Tools | Export...
- Select Next.
- 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.
- Select Finish.
- 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
- 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.
- 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).
- You may use any software that allows you to create a file in .xls (e.g.,
Excel) or .csv (comma-separated values ASCII) format.
- Be sure to include a column for the store number. This is what you will
be using to merge the information into the forms.
- 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.
- Leave no columns blank for formating. You may also omit any columns for
fields that none of your stores need to report.
- 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.
- Save your spreadsheet to a location you can find again.
- 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.
- 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.
- 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.
- Start Surveyor if you have not already. Choose Start |
Programs | U.S. Census Bureau | Surveyor. Close the Welcome Screen.
- From the Form Inbox, select Tools | Create Import/Export Map.
- Select Next.
- Highlight one particular form type corresponding to your spreadsheet and
click Next.
- Enter the file name of your spreadsheet or click on the folder button to
bring up a Windows file selection dialog.
- 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.
- In the upper left, enter a description, any name that will be meaningful
to you.
- 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.
- Link each remaining spreadsheet column to a field.
- Highlight a column in your spreadsheet
- Click on the field in the questionnaire where that information should
go.
-
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.
- 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.
- Save your map file:
- Click File | Save Map File, or, if you started with template1-4.gmf,
click File | Save Mapping File As...
- Enter a file name.
- Click Save.
- 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:
F. Fill in the Spreadsheet
- 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.
- Cut and paste information from other spreadsheets into this spreadsheet.
- 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.
- Note that all dollar fields (such as sales and payroll) are to be reported
in thousands of dollars.
- 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.
- 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.
- 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.
- 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.
- 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.)
- 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
- From the Form Inbox, select Tools | Import.
- Follow the instructions on the next two screens.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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)
If you need assistance, please call 1-800-838-2640.
Source: U.S. Census Bureau
Last Revised: 04/17/2003
|