Kaltura MediaSpace: Manually transfer grades to D2L Brightspace

Overview

D2L Brightspace and Kaltura allow individuals to perform bulk actions that can be used to transfer grade data between the services.  The information below is designed to help you manually export grade scores from Kaltura and import them into D2L Brightspace.

Backing up your D2L Brightspace grade book

Before performing bulk actions in Brightspace, you may want to create a backup of the current grade book.  To perform an export, review Import or Export Grades in D2L Brightspace.

Identify the Video Quiz Grade Column in Brightspace

1. Within your Brightspace course, to the Grades tool.
2. Select the drop-down for the column and select Edit.
   
3. Save the grade book column Name for later. This will be used later in your import file.  You may want to copy and paste this value to a temporary document or file.
4. Save the Maximum Points value for later.  This needs to be aligned with the raw score returned from Kaltura’s exported results. 
   
5. The Association information in the grade center properties can be helpful to verify the correct Kaltura video.

Retrieve Quiz Scores from Kaltura

1. Access the analytics for the specific Quiz in your My Media.
2. Select Analytics from the Actions drop-down menu of the video.
   
3. Click Quiz Users.
   
4. The Quiz Users tab shows all attempts, and the results can be exported to CSV.Export to CSV.
   
5. The Quiz results will be exported with the following headers:
   • User ID
   • User First Name
   • Users Last Name
   • Answered Right
   • Answered Wrong
   • Final Score Percentage

Format the Export to Match D2L

Brightspace requires unique formatting of the headers to import successfully. These steps can be automated through a script if desired.  The required changes include:

1. Change User ID to Username in Column A
2. Delete the entire User First Name column and the entire User Last Name column
3. Delete the Answered Right and Answered Wrong column
4. Change the Final Score column header to the Name of the D2L column header and then add Points Grade
   • If your D2L gradebook item is called “Video Quiz 1” the column header should be “Video Quiz 1 Points Grade”
   • Ensure the value is representative of the column settings in D2L
     • The Final Score from Kaltura is a Percentage
     • D2L requires the Raw Points for Import
     • The value from Kaltura should be divided by 100 and then multiplied by the maximum number of points for the specific column.
     • If the column is worth 1 point in D2L and the individual earned 100 percent in the Kaltura export the value needs to be changed to 1 instead of 100 for an accurate import.
5. Add an additional column with the header End-of-Line Indicator, and for each record add a # in this column. A quick way to add the hashtag/pound symbol to all records is to add it in the top row (row 2) and then double click on the autofill square on the bottom right corner of the active cell border to autofill the same data in the contiguous records.
   

The finished formatting adjustments should look like this. 

Save the file as a csv to a location you can easily access later.

Automating the conversion process needed and saving a new CSV file in the download location on the local device can be automated through a Macro Enabled Excel Workbook, or from PowerShell with the following script:

# Specify the path to the default downloads directory

$downloadsPath = (New-Object -ComObject Shell.Application).NameSpace('shell:Downloads').Self.Path

# Search for the most recent CSV file starting with "quiz_users"

$csvFile = Get-ChildItem -Path $downloadsPath -Filter "quiz_users*.csv" | Sort-Object LastWriteTime -Descending | Select-Object -First 1

# Check if a matching CSV file was found

if ($csvFile -ne $null) {

    # Get user input for D2L column name and point grade

    $d2lColumnName = Read-Host "Paste the exact D2L column name where the scores will be imported:"

    # Load the CSV file

    $originalData = Import-Csv -Path $csvFile.FullName

    # Identify the name of the last column

    $lastColumnName = ($originalData[0].PSObject.Properties | Select-Object -Last 1).Name

     # Get user input for D2L column point grade

    $d2lColumnPoints = Read-Host "Paste the max points for the column where the scores will be imported:"

    # Load the CSV file and select only the "User ID" and "Percent Right" columns

    $data = Import-Csv -Path $csvFile.FullName | Select-Object "User ID", "$lastColumnName"

        # Divide Final Score by 100 and multiple by column points to get raw score

foreach ($row in $data) {

    $row.$lastColumnName = [decimal]($row.$lastColumnName / 100)*($d2lColumnPoints)}

    # Create a new object with modified headers and values

    $modifiedCsvData = $data | Select-Object @{

    Name = "Username"

    Expression = { $_.'User ID' }

    },

    @{

    Name = "$d2lColumnName Points Grade"

    Expression = { $_.$lastColumnName }

    }

    # Add a new column with the line-break indicator needed

    $modifiedCsvData | ForEach-Object {

    $_ | Add-Member -MemberType NoteProperty -Name "End-of-Line Indicator" -Value "#"}

    # Specify the path for the new CSV file with the max points appended to the file name

    $newCsvPath = Join-Path -Path $downloadsPath -ChildPath "Quiz Users D2L max points $d2lColumnPoints.csv"

    # Export the selected data to the new CSV file

    $modifiedCsvData | Export-Csv -Path $newCsvPath -NoTypeInformation

    set-content $newCsvPath ((get-content $newCsvPath) -replace '"')

    Write-Host "CSV file 'Quiz Users D2L max points $d2lColumnPoints created to allow D2L import, make sure the max points value matches in the gradebook and that the column name input was accurate."

} else {

    Write-Host "No matching CSV file found, make sure your Kaltura export is in the default download directory."

}

Importing Formatted CSV file to D2L Brightspace

D2L Brightspace can now import the scores to the matching column with the three formatting changes to the required headers. Username, [BrightspaceColumnName] Points Grade, End-of-Line Indicator are the only required columns needed to get scores added in Brightspace.

Importing Steps

When you are ready to add the Video Quiz grades, follow these steps:

1. to the Grades tool in your Brightspace course.
2. Import.
3. Choose the customized file you formatted with the Kaltura video scores.
   NOTE: Do not check Create new grade item when an unrecognized item is referenced.
4. Continue.
5. Brightspace will process the file and validate the content. If there are errors or unrecognized users, they will be listed in Step 2. Any unrecognized users will not be imported, you can continue to the next step to preview the Import results and any changes that will take place.
6. Import to proceed, or you can click Go Back to adjust the file if needed.
7. After Importing you will be taken to the Grades tool again where you will see the imported scores.

Importing again with a different file with the same column headers will overwrite the previously imported scores and add any new entries. More details on importing can be found in Import or Export Grades in D2L Brightspace.