SMDS Help Desk

SMDS allows you to import responses using a system-generated Excel template. Responses can be uploaded to any tool in an active evaluation. Entering data into a system-generated Excel spreadsheet is useful if you will be collecting data on a tablet in the field but do not have internet access. From SMDS, you can download a spreadsheet for any tool, enter responses into the spreadsheet, and import those responses into SMDS.

Generating Spreadsheet Templates

• Place your cursor over the Evaluation tab.
• Click Evaluations. 

A list of evaluations is displayed. This list includes both Active and Draft evaluations (see Status column). Only Active evaluations can generate spreadsheets.

A list of tools in that evaluation is displayed. Scroll down to find the tool you will use in the list.

• Click the Excel Format button for that tool to download the Excel file onto your computer/into your files.

NOTE: If you are downloading an Excel spreadsheet of a tool in more than one language, SMDS will ask you which language you want to use for your Excel spreadsheet.

NOTE: To successfully import response data into an evaluation you must use the spreadsheet generated from that evaluation in your SMDS site. This is a useful feature of SMDS, but it will not work if you make any changes to the layout of the spreadsheet. That includes adding, moving, or deleting columns, The system reads the spreadsheet to access background information to ensure your data ends up in the correct evaluation location.

The downloaded worksheet will have four tabs.
 

Evaluation and Org Name - Do not change anything on this page, it holds information that is necessary for an accurate upload to occur.

Data – Use this tab for data entry.

Instructions – Review this tab for instructions on which fields are required, for entering data on Individuals or for Locations.

Lookup – Do not change anything on this page. You can review what is here if you want to see what would be valid data values for any of the data columns, such as how State column values should be formatted, etc.

Whether your data collection tool has Individual or Location as the data source, the steps that you take will be the same. The spreadsheet from a tool that has Location as the information source will have a slightly different set of initial columns from a tool that has Individual as the information source. This guide will show you those differences where they exist.

You will see that several of the column headings have a small red triangle in the upper right corner, indicating that there is a note of instructions that you should pay attention to. Click on the red triangle and a message box will open. The Instructions tab can also provide more information to help you decide which columns your data will need to use.

Location Type allows you to indicate whether the location is a block, a place, or another type of location, by selecting from the dropdown menu.  The three most common types used are Block, Place, and Property. Which other columns you must use is determined by which Location Type you select.

Block generally means a street block, usually from one intersection or landmark to another.  

Place might be used for a specific address or building, or for a park, or other place. 

Property as a Location Type is more complex than Place in that if you select Property as the Location Type you will then need to include more details about City, State, and Postal Code (Zip Code).

If you select Property as the Location Type, you will not use the Location Details column, but instead will use the Location Street, as well as Location-City, Location-State, and Location-Postal Code. Those are all shaded blue and have red triangles holding instructions telling you they are required when Property is the Location Type.

Just after the (blue) column headers about the Respondent or Location information, you will see purple columns wherever your data collection tool has Titles and instructions. The purple columns ARE NOT for data.   Your data collection tool questions and answer options begin to the right of the first purple column.

On the spreadsheet, the first two rows which hold the column headers are NOT for data. The second row holds the column headers of matrix questions where there are multiple lists or statements all using the same answer options.

Your response data will begin on the third row of the spreadsheet. Moving from left to right, you will input the Respondent or Location information, and then progress across the spreadsheet filling in the answer options in the appropriate cells for each respondent.  If a specific question was left blank in data collection, you can leave that cell blank on the spreadsheet.

For questions that are open-ended, meaning on the data collection tool there was a text box for writing in an answer, there will be open cells on the spreadsheet into which you can write the response. Leave the cell blank if no response was provided.

Questions with multiple choice answers will have a dropdown list of answer choices. Be sure to select from the dropdown list. To see the dropdown list, place your cursor in a cell and a list will appear. Select one of the answers from the dropdown list.

After you have entered the data for all your responses, save the spreadsheet before importing the data into SMDS.

Importing Responses from a System-Generated Excel Spreadsheet

Before uploading your data, please review the following:

• Check to make sure there are no “incomplete” rows. If there is anything in a row, the import program will try to read it. Make sure there are no rows hanging at the bottom with a response number but no data.
• Make sure there are no blanks in the required columns:

1. Date Collected.
2. For an individual, the response identifier is either Alternate ID OR Last Name and First Name.
3. For a location, the response identifier is Location Details if you selected Block or Place as the Location Type, OR if you selected Property as the Location Type, it is Location-Street/City/State/Postal Code (Zip Code).

• Finally, scroll through the entire file and look for anything in your data that looks out of place.

To begin the Import process: 

• Place your cursor over the Evaluation tab.
• Place your cursor over the Responses on the dropdown list.
• Click Import Responses. 

The Import Responses page will display.

NOTE: Leave the Confirm Import checkbox unchecked the first time you import the data. This will allow the system to review your data and indicate if there are errors.  

A dialogue box will appear, warning that the import is being executed in simulation mode.

• Click Yes in the dialogue box. 

The initial review by the system of the data may take up to a minute if it is a large file. While the system is reviewing, you will receive a message on the screen that tells you it is working. 

Once the review is completed, the system will tell you whether your Import was successful.

When the system’s review of the spreadsheet is completed, the system will provide details row by row of the spreadsheet. Scan down the list, looking for red X marks.  Read the explanation to locate the error to be fixed on the spreadsheet.

In this example, the first error is on row 5, where the response did not have a Response ID.
 
 

You will need to go back into the spreadsheet to fix any errors. A common error that can occur is typing the answer into the cell instead of using the dropdown list of answer options, which can result in a typo or added space within the answer.

After fixing your errors, upload your document again without checking the Confirm Import checkbox. 

Once the system reviews the spreadsheet data, it will provide details row by row of the spreadsheet.  Each response that is correct will have a green check mark.

Once the simulation process shows that there are no errors, you are ready to do the import with the Confirm Import checkbox selected. This will complete the process of placing the response data into your evaluation.

Select the Confirm Import checkbox.

The system will complete the import and your responses will be added to your evaluation. The system will tell you that the Import Response operation was completed successfully in a small light blue band at the top of the page. 

To confirm that the import was successful, you can go into your Manage Responses page to make sure the new responses are there.