SPARK and PRS

SPARK (Blackboard Vista) is an online course management application. Many professors use SPARK sites to distribute course documents, hold class discussions on online boards and keep track of an academic calendar, among other things. SPARK also allows students to keep track of their grades as the semester progresses.

Importing PRS Grades to SPARK

Below are the steps for importing PRS grades into SPARK. Essentially, this process requires you to download gradebooks from both SPARK and PRS onto your computer so they can be combined in Excel and then uploaded again to SPARK.

The Office of Information Technology (OIT) has provided a step by step tutorial on merging SPARK data with PRS data in the following PDF file: spark-mergedata.pdf.

Export Gradebook from PRS

SPARK uses the formatted exported gradebook from PRS, so you first need to set up a gradebook in PRS and mark each session which you want to show up in SPARK, saving them in the created gradebook.

Once this is done, go to the “Gradebook” tab and select the gradebook you created.

Click the "Export to Gradebook" button at the top of the screen and when the dialog appears choose "Blackbaord Vista with Student ID" and click OK. Keep track of where you save this file.

Export Roster from SPARK:

You go into SPARK, navigate to your gradebook and click the "Export to Spreadsheet" link on the bottom of the page.

You want to make sure you have All Students selected. Click the "export" button at the bottom of the page and save the file someplace you'll be able to find it.

Merge the two rosters in Excel using VLOOKUP

Open both exported gradebooks in Excel.

From the SPARK file, delete all columns except for the UMass ID and "User ID" (The SPARK IDs) columns. Put the UMass ID column all the way on the left followed immediately by the User ID column.

In the PRS gradebook, add a "User ID" column. Make sure to name it exactly "User ID".

Sort both gradebooks by the UMass ID (called Student ID in the PRS gradebook). Make sure you "expand the selection" so that all the columns are properly sorted.

Select the first cell in the "User ID" column you created in the PRS gradebook. Then select insert -> function -> VLOOKUP

To configure VLOOKUP you need to fill in four fields: "Lookup_value". This tells what value we are matching on. Put in the coordinates of the cell that contains the first Student ID in the PRS Gradebook.

For "Table_array": The table the value will be looked up from. In this case; select all values in the UMASS_ID and User ID columns in the SPARK gradebook.

"Col_index_num" Type “2” into this field. This is because the column the values are coming from(the User ID column) is the second column of the Table_array.

Range_lookup should be "False", which simply means the match has to be exact (in this case, the student IDs have to match exactly on lookup). Once this last column is filled, click “OK.”

Copy this VLOOKUP formula to all other cells in the User ID column in the PRS gradebook. You should see the correct UserID in the cell where you entered the VLOOKUP function. Copy the cell selecting all the other cells in that column. Click Edit -> Paste Special -> Formulas

Your gradebook is now ready to merge into SPARK. Save the file in PRS format and go back into SPARK. Go to your gradebook and click the "Import from Spreadsheet" option.

Locate the PRS gradebook file and select it. Make sure you have separator set to “comma.” When you're done, click “Upload.”

You will be presented with some options of how to match the columns from the file with the columns in SPARK (or if to create new ones). Select the appropriate choices for your situation.
Click to finish the transfer and check the data to be sure everything matched correctly.

NOTE: Once you have merged the USERID data into your PRS Roster, you will be able to export and upload your PRS data into SPARK without repeating the VLOOKUP process.