The Import from Excel feature allows you to upload the questions in an assessment by means of an Excel spreadsheet. This worksheet is designed to facilitate the "authoring" of assessments. Its primary purpose is to expedite data entry that could be performed as an author through the GUI. The administrator will download the data entry spreadsheet to their hard drive, populate the spreadsheet with the relevantinformation, and then upload the spreadsheet back into the Assessment Management Engine. Please note that this feature will work only for page-based assessments deployed in en-US culture.
Note: You will not be able to populate the spreadsheet for Project type assessments. For more information, see Create a Project Assessment
The data entry spreadsheet contains the following worksheets:
Response Types: This worksheet allows an author to create new multiple choice response types and values that can be used within both Standard and Object based assessment types.
Question: This worksheet allows an author to create a new Standard assessment or a new page based assessment that includes pages, questions, and response types.
Available Tokens: This worksheet contains available tokens grouped by assessment type. An author can use these tokens as additional information inside the question text/row text. The tokens can only be used within its respective assessment type. There are no tokens available for standard based assessments. Fields available within this worksheet are based on those fields displayed within the RCM core search at the time the template is downloaded.
Each worksheet within the utility except the Available Tokens worksheet includes some required and optional fields. All fields within the Response Types worksheet and the Question worksheet are color coded to assist users in completing the spreadsheet.
The Response Types worksheet allows an author to create new multiple choice response types and values that can be used within both Standard and Object based assessment types. You can include new response types and new response values in this worksheet. For example, on a "yes/no" scale, the response type may be "yes/no" and a value will be "yes" while a second value will be "no". Each response value must be associated with the respective response type.
The following table explains the columns in the Response Types worksheet:
Fields
Details
Length
Required
Response Type
This column contains the response type name. This usually is a single line text input.
Up to 75 Characters
Yes
Response Type Layout
You can select the multiple choice layout options from the drop-down list. For instance, you can select the layout from Radio button Vertical, Radio button Horizontal, Drop-down, Checkbox Vertical, and Checkbox Horizontal.
NA
Yes
Response Value
This field shows the response value associated with the selected response type.
Up to 2000 characters
Yes
Default
This field shows a drop-down list having Yes and No as choices. Note that only one value within a response type can be set to Yes.
The Questions worksheet allows you to create a new Standard assessment ot a Page Based assessment that includes pages, questions and response types. Populate the following fields in the Questions tab to upload a question.
Assessment Name: This field contains the existing assessment name where you want to create the new question.
Assessment Type: Select the assessment type from the drop down list. The assessment type selected in this field should be the same as selected in the assessment management engine for a particular assessment.
Page Name: This is a multi-lingual field wherein an author needs to enter the page name. The author can only use the languages those are selected in the assessment in the Assessment Management Engine.
Page Description: This is an optional field. You can enter the desired page description in this field in the languages as selected in the Assessment Management Engine for the particular assessment.
Page Sort Order: Enter the page number in the order in which the pages will be presented to the assessor.
Question Text: Enter the question in any of the the languages as selected in the Assessment Management Engine for the particular assessment.
Question Short Name: This is an optional field. You can provide an alias or a short name for the question.
Question Sub Text: Enter the question sub-text (if any) in any of the languages as selected in the Assessment Management Engine for the particular assessment. This is an optional field.
Question Sort Order: Enter the numeric value at which the question will be presented in the page.
Question Type: Select the type of question from the drop-down list. The options in the drop-down list will vary as per the Assessment Type selected. For instance, if an author selects Standard as the assessment type, the drop-down in the Question Type field will have Standard Response Type and New Response Type as the options. However, if an author selects any of the object as the assessment type, the drop-down will also include the Object Evaluation as the third option.
Response Type: Select the response types from the drop-down list. The response types in the drop-down list vary as per the Assessment Type and the Question Type selected.
Default Response Type: This is an optional field. Select the default response type from the drop-down list. Note that this field is not applicable in case of a Standard assessment.
Comment: This is an optional field. Select whether the comments will be optional or required from the drop-down list.
Attachment: This is an optional field. You can make the attachments option by selecting Optional from the drop-down list.
Required: This is an optional field. You can make the question itself as Optional by selecting Yes from the drop-down list.
Select the Default GRC context from the context menu.
Click the Workflow Management tab.
Select Assessments from the Assessment Management group.
Click Import from Excel.
Click Browse in the Select the spreadsheet for processing section.
Locate the modified Excel spreadsheet on your hard disk.
Click the Click here to process the spreadsheet link. All records successfully uploaded will be listed in the processing log. Any errors will be displayed in the exception log.
If an error occurs, update the Excel spreadsheet based on the exception log and process the spreadsheet again.
As this is a data migration process, care and attention are required to ensure its success. The worksheet and application provide some validation, but the user is responsible for ensuring the completeness, quality, and accuracy of the information both in the sheet and in its final form in the Assessment Management Engine. It is highly recommended that post-upload, authors perform some assurance within the application to validate the data migration. Following are some of the key points that needs to be kept in mind while processing the spreadsheet.
Hidden Worksheets - The spreadsheet may certain hidden worksheets which must NOT be tampered with. Any modification in these may result in a processing error and an unsuccessful upload.
Renaming Worksheets - You may rename the spreadsheet while downloading it your desktop. But you must NOT rename any worksheet within the spreadsheet.
Duplicates - The first step in the data conversion process is the removal of unnecessary duplicate values. Occasionally, records can be double entered or values can be repeated (due to human error, different abbreviations, etc.). Duplicates should be identified by sorting through the data and then carefully removed.
Accuracy - In systems that have been in operation for several years, data can often become corrupted or inaccurate over time. By scanning carefully through the data, it is possible that all of the records are valid and current. If there is data present that is no longer needed, remove it from the system. If there is data present that is inaccurate or not current, update it accordingly. Only current and accurate data should be uploaded into the new system.
Spelling - Spell-checking is a simple step that is often overlooked. If possible, run an automated spell-checker on the data to identify and correct spelling mistakes. If the data cannot be spell-checked in the host system, spell-check it once it has been extracted, using Excel or similar.
Spacing - In addition to spelling and accuracy, spaces in words or values are interpreted like any other character in the system. Therefore, it is especially important to remove additional or erroneous spaces from data. For example, a frequency value of “Weekly” is seen as something completely different and distinct from “Weekly ” (with the trailing space at the end), even though they may look similar. Spaces are often hard to spot, and generate duplicate values that could be overlooked. When data is uploaded, the system (actually SQL) automatically cleans trailing spaces and double spaces between words. This can be problematic when attempting to link data through the utility. The reason? When the data is uploaded, the erroneous spaces are removed, but they still may exist within the Excel spreadsheet when attempting to link two data objects together. Thus, when attempting to create the linkage, the system is unable to recognize the values to be linked since there is no longer a corresponding name in the system. BEFORE uploading data, all trailing and double spaces should be cleaned from the Excel spreadsheets by using the TRIM function.
Special Characters - In a similar way to spacing, special characters can also affect the uniqueness of values, and therefore need to be used consistently. For example, “ACME & Co” is different from “ACME and Co”. Additionally, the placement of special characters in a value can cause issues. Be careful to check the placement and consistent usage of dashes “-”, colons “:”, periods “.”, etc.
Download the Spreadsheet Template
Import from Excel.