Examsoft - Mapping and Load Process

Owned by jrussel1
Last updated: Feb 03, 2023 by Jessica Marshall

Overview

The following guidelines are recommended for the USF Morsani College of Medicine community in mapping required exams. This will identify specific course exams to each medical student for a given semester. These test scores are loaded to the Health Education Data Warehouse and other student information.

Location

\\tup-sapetlprod.forest.usf.edu\ExamSoft\    (requires USF VPN connection http://vpn.usf.edu/ )

The folder also has the following sub-folders:

  • Archive - Contains the Course Exams Mapping files that have been loaded to the database in the past.
  • Error_Logs - contains the following two files:
    • EXMS_Incorrect_Exams.csv - Contains invalid exams IDs in the file when data was last available.
    • EXMS_Incorrect_Students.csv - Contains U-number records which don't match any Health Banner students in the file when data was last loaded.
  • Required file format - Contains the template file 'CourseExamsMapping.xls'. Please use it to generate any files that need to be loaded to the database as it contains the necessary columns in the correct order.

Back to Top


Table of Contents

Rules for Data

Exams - Tab 1

  • Required columns include ExamID, PostingID, Seq, and Term.
  • Must have a valid Exam and Posting ID which can be found within Examsoft.
  • Multiple rows per Exam ID are allowed with different Posting IDs, but mapping should match between the lines.
  • Must have a valid Course Abbreviation and Course Number available in Health Banner or a number in Courses.
  • Must have a valid term code available in Health Banner.
    • Example format: 201707
    • The first 4 digits are the year during which the term begins.
  • Sequence numbers are used to identify the order the exams were administered or types of exams
    • 1-4: Course Exams
    • 20-23: Reserved for Anatomy Practical Exams
    • 30 and 31: EBCR Exams
    • 75: Remediation exams that may cover more than 1 original exam topic
  • Columns must be in the order indicated on the 'CourseExamsMapping.xls'

Exams with multiple banner courses (Course 1) need to be listed on both of the tabs. The second tab allows individual questions from the one Exam ID to be separated between multiple courses.

2_Exams_Per_Banner_Course - Tab 1

  • Required columns include ExamID, PostingID, CourseAbbr, CourseNum, Term, and Courses.
  • Must have a valid Exam and Posting ID which can be found within Examsoft.
  • Must have a valid Course Abbreviation and Course Number available in Health Banner.
  • Must have a valid term code available in Health Banner.
    • Example format: 201707
    • The first 4 digits are the year during which the term begins.
  • Columns must be in the order indicated on the 'CourseExamsMapping.xls'

Back to Top

New File Loading Process

Follow these steps to add a new Exam to be loaded into the Educational Data Warehouse:

  1. Go to the folder location.
  2. Open the CourseExamsMapping.xls file.
  3. Add a new row for each exam you would like loaded with all required fields to Exams - Tab 1.
  4. For any Exam ID that maps to more than one course, add two rows to 2_Exams_Per_Banner_Course - Tab 2.
  5. Save and close the file.

Back to Top

Data Correction and Loading Process

Note

You will be notified if there are any source data errors while loading the file(s). The email will mention the location and file name of the error log.

Follow these steps to provide a corrected mapping file for loading the Examsoft exams to the database:

  1. Go to the folder location.
  2. In the Error_Logs sub-folder, open the error log file mentioned in the email.
  3. For Incorrect Students, identify the student's correct U-Number and enter it into Examsoft. The next time the load runs, the student will be correctly identified.
  4. For Incorrect Exams, identify the correct Exam ID or Posting ID and update the mapping file in the folder location.

Back to Top

Examsoft Tests that are used for multiple courses

The exams that have multiple courses for a singular exam (currently Course 1) need to be listed on 2 separate tabs.

  1. Exams - First tab used to pull in the student scores for the singular exam from Examsoft.
  2. 2_Exams_Per_Banner_Course - Second tab is similar to the first tab, except it allows the ability to split an exam into separate banner courses based on the description of the individual question (currently MS or CA). 

If there is ever a change in the descriptor fields or new descriptors need to be added, it will require a change in the process that loads the Examsoft data.

Back to Top

Load Schedule

It is important that the Excel files be closed and not open for editing before the load process begins. When the data load begins if the file is open, then the process will error out. The load happens on every Saturday at 9:15AM.

Back to Top