HOW TO: Import grades into D2L from an external spreadsheet This guide will show you how to set up student grades in Excel to import into the D2L gradebook. NOTE: This guide assumes you have a basic working understanding of gradebook and D2L. Step 1: Gradebook setup Is your gradebook already set up? Do you have your categories and items? If so, proceed to step 2. If you do not have your gradebook set up, you may either do so now, or you have the option to have D2L create new grade items from the columns in the Excel file. The upload does not automatically create categories so if you want advanced settings such as adding and dropping the lowest grades, you will need to manually create the categories after import. It is also recommended set your gradebook to the weighted scheme before the upload. Step 2: Formatting your spreadsheet: Lead and End Column Setup A) Have your FIRST column header entered as OrgDefinedId and populated with the students UCID numbers. Important: Be sure that you keep leading 0’s in the students ID. Change the field to a text field if excel automatically removes them. B) Have your LAST column header entered as End-of-Line Indicator and have each field populated with the pound symbol #. See Figure 1 for an exemplar. Step 3: Grade Item Columns Each column will be an individual gradebook item. Any unnecessary columns with formulas or used for calculations should be omitted. However, be careful to not lose the values you require. This can be achieved by copying the entire spreadsheet, then pasting it in a new spreadsheet as “values only”. This will allow you to delete unnecessary columns without losing calculations. See Appendix 1 for details. The naming of each column header needs to follow the format: “NAME-OF-GRADEBOOK-ITEM” followed by “TYPEOF-GRADEBOOK-ITEM”. For example, Exam 1 Points Grade. Important: If you have already created gradebook items, the item NAME and TYPES must match what is in D2L. The Type of Gradebook Item needs to be named as follows: What to use in the header Points Grade Type of Gradebook Item Numeric Points Grade Pass/Fail Grade Symbol Selectbox Text Grade Text Grade Description Users grades should be the points they received. The total max points will be assigned later. Users grades should be the points they received. The total max points will be assigned later. Users’ grades should be a pre-existing grade scheme symbol. Can be any text. Does not count towards calculated final grades Example 4 96 Very Good OR B OR 3.5 Perfect Attendance! Adjusted Final Grades: You can also insert adjusted final grades by importing a file (with or without other grade items) that has the standard lead and end columns (from Step 2). You then need the following two columns: Adjusted Final Grade Numerator and Adjusted Final Grade Denominator with the corresponding values filled in for each student. See Figure 1 for an exemplar. Step 4: Check Grades Ensure that all grades are correct. You will be able to make modifications in D2L, however, for mass edits it’s better to check that all grades were copied correctly. Step 5: Export Export the spreadsheet as a CSV file. To do so in Excel, choose File > Save As > File type (Comma Separated Value .CSV). Save the file to a convenient location. Step 6: Importing the spreadsheet into D2L In D2L gradebook choose Enter Grades, then click on . Choose the file by clicking on the Browse button. NOTE: If you are wanting to create new grade items, be sure that the box is checked. If you have new grade items, you will see the following screen. Uncheck any items that are not wanted. Be sure that the grade type is correct. Then click on . The next screen allows you to modify the settings for each new gradebook item. Here you can assign it to a Category, set the Max Points (and Weight if required), and if it “Can Exceed” or set as a “Bonus” item. Scroll down to see all the new items. When you are done, click . If all has gone well, you will not have any errors. Sometimes if you had students in your spreadsheet that have dropped the course or have an incorrect ID number, you will see an error that those students have not been added. Check to ensure that it is not a mistake with the actual ID entered. If they have dropped the class it is ok to ignore that warning. Lastly, you will have a chancce to preview the fields before clicking on once again to finailze the process. You should now be able to edit and manage grades in d2L manually. Please note that any imports will override previous grades if the fields are the same. Figure 1: Grade book import sample. USER IDENTIFIER: These need to match the official list NOTE: Do not import other information such as first name, email, etc. MUST HAVE: The last column must be labeled “End-of-Line Indicator” to separate data sets. The end of each users’ line should be indicated with the pound key #. For grade items, the name of the item (as listed in your grade book) must be followed by the text that describes the type of grade entry (see list in Table 1). OrgDefinedId Assignment 1 Points Grade Assignment 2 Points Grade Test 1 Points Grade Discussion Grade Symbol Presentation Points Grade Participation Text Grade Adjusted Final Grade Numerator Adjusted Final Grade Denominator End-ofLine Indicator 56987425 42 38 89 Excellent 76 81 100 # 45689521 44 37 85 Very Good 84 84 100 # 13456987 36 29 68 Very Good 70 71 100 # 65247896 34 37 78 Good 79 Your comments were focused and insightful Perfect Attendance! You had great ideas when you attended class You need to join more group discussions 76 100 #