“Hi Mr. Moodle. How can I get (download) the exported gradebooks of all the courses of a specific category? (As xlsx or csv) without going into each and every course? Thanks!”
Your request seems to be one of those unfortunate few that is regularly requested but for which no one-click solution yet exists.
However, this is not to say there is no solution available, it’s just that it takes a little bit of SQL code. To make it work, you need access to the database console. If you do not, you can ask the administrator to run an export periodically.
This solution is based on one posted by Andy Cravens on the Moodle Forum (in 2009) and is available for databases written in MySQL.
SELECT mdl_course.idnumber AS course_id, mdl_user.firstname, mdl_user.lastname, mdl_user.username, mdl_grade_items.itemname AS gradebookitem, mdl_grade_grades.rawgrade AS raw_grade, mdl_grade_grades.finalgrade AS final_grade, FROM_UNIXTIME(mdl_grade_grades.timecreated) AS date_created, FROM_UNIXTIME(mdl_grade_grades.timemodified) AS date_modified, mdl_grade_grades.feedback FROM mdl_grade_grades JOIN mdl_user ON mdl_grade_grades.userid = mdl_user.id JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.id JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id ORDER BY mdl_course.idnumber, mdl_user.username, mdl_grade_items.itemname, mdl_grade_grades.timemodified;
This will return more information that some of you asked for. It is never a good idea to identify your students by firstname, lastname only because it is common for a large user base to have two people with the same first/last name.
You may notice the SUBSTRING clause on the feedback column. I only included the first 15 characters of the feedback just so I could tell how many instructors were leaving feedback. You can remove the SUBSTRING clause to get all the feedback or just remove the entire line if you don’t want any feedback in the dump file.
This query may pound your database depending on how busy your server is. I only run this in the middle of the night.
One thing I forgot to mention is… this gradebook dump is not in the same format as a gradebook “export” and you cannot import my gradebook dump back into a course. However, I do have some scripts that will reformat this SQL gradebook dump into an XML file that can be imported back into Moodle on a course-by-course basis.
Let’s try to dissect a little what is going on. Using this code, you are grabbing your Moodle site’s entire database and asking SQL to:
- grab all courses
- from each course, grab activities
- from activities, grab grade data
- “join” the tables from different gradebooks, or put them together in one result
- add context and reference to the data to know where it belongs and where it was created
- export it to a single file
Certainly, a job for which many people would appreciate a plugin.
This Moodle Technology related post is made possible by: Moonami a company that provides a full range of Moodle services that combine the flexibility, scalability, and power of Amazon’s world-leading cloud platform (AWS) with fanatical Moodle support. Click here to learn more.