Ever have the need to download a large number of student submission files for the purposes of accreditation or other academic assessment reviews? While it is possible to go to each course assignment and click the 'download submissions' button to generate a zip file that downloads after it is generated, doing so is a repetitive and time consuming task. Especially if you have many courses and assignments to collect files from.
In case you have not been following the Canvas Data release notes, as of this past month we have a new table: submission_file_fact, which fixed a bug with the file_dim table. After seeing this (thank you, Canvas Data gurus), I needed to explore.
It turns out this new table is very helpful as new approaches to accessing and collecting submission files can be set up. The method detailed here works quite well when you are downloading submission files for a single assignment across course sections within a given term, and also across terms for the academic year. It can of course be used other ways, but I prefer to collect submission files for one assignment at a time so I can appropriately label the folder I place them in.
The Nitty Gritty Details
The example below uses an sql file and also Tableau to provide a visual. Within we can filter by term, course and assignment. Of course such filters can alternatively be set up within your sql file. Portrayed here are links to over one thousand student file submissions to a single course assignment for just part of an academic year. How long would it take us to go to each of the 50 course sections to manually collect the files from each assignment? Too long. ;o)
Data Source Filters
Calculation for the Download File URL column
Link to download individual student file submissions
'h ttps:// xyz.instructure.com/files/' + STR([canvas_id _file_dim_]) + '/download?download_frd=1'
h ttps:// xyz.instructure.com/files/12345678/download?download_frd=1
Steps to run the report using Tableau:
- Review your default Downloads folder on your local hard drive. Consider deleting old content so there is no confusion with the files about to be downloaded.
- Run the report ‘Student File Submissions’; apply desired filters, select the term(s), course, & assignment name
- Export the output to csv and open into Excel.
- Copy the list of links and paste into a Chrome web browser extension that enables bulk downloading. Example: https://melanto.com/apps/bulk-url-opener/ **
- The files are then downloaded. Perhaps do this in batch such as 50 at a time until you are comfortable and then experiment. I don't suggest downloading a very large number at one time. As you progress, count the number of rows on the spreadsheet and verify the number of files downloaded match. Below are the settings for the above tool that I've experimented with.
- Create a new folder and name the folder for the course and assignment. Move the files from the download directory to the new folder ahead of downloading additional files.
** Note: writing an Excel macro was considered, however I didn’t want to over complicate this.
- I am sharing this as a ‘work-in-progress’ and I hope it is built upon. Note the sql has a few extra fields that are not being used. Feel free to remix and reuse any or all of this.
- Upon opening the .twb file in Tableau, you will be prompted to provide a password to access the data source. Because you don't have access to our data source, you'll need to click on the "Edit Connection" button. Once the connection information is updated to access your Redshift data source you should be able to begin editing the workbook. See attached files for more info on connecting to other data sources.
Please feel free to add your ideas. Enjoy ~!