mas ,
It is possible -- sort of. You'll have to split the sortable_name on a comma to get their first name and last name.
If you have Canvas Data, you can write an SQL query to do this. This data is at least 18-36 hours old before you get it and there is an overhead with getting Canvas Data setup.
Let's say you don't have Canvas Data. That leaves you with the Canvas API. You would need to write a program that would get a list of all of the active courses and then fetch the enrollments for each of those courses.
You can fit up to 100 enrollments per course in a single API request. However, Canvas is making changes to the way the enrollments API returns data so that you can't make guesses about what the next page is going to be, which means that you'll have to load additional pages of enrollments within a course sequentially. That only affects you if you have courses with more than 100 enrollments in them.
You can also write this using graphQL, but you will definitely need to use pagination and you're not able to filter as much in Canvas as the graphQL spec allows. This will allow you to get all of the enrollments for all of the courses, but you'll definitely have to use pagination and you'll need to filter out the courses and enrollments you don't want since you can't specify just students or just a single term.
In other words, it may be quicker to join them. Excel may not be the most efficient way to do this, though. I use Excel -- a lot -- but I say it's a general purpose spreadsheet designed to do everything and when you try to do everything, you do nothing well.That may be a bit extreme, but the other side of the coin is that there are programs designed to do one thing, but do it very well. Many of the Unix / Linux command line tools are designed this way. As for joining files together, join comes to mind. The fact that the fields are quoted when there's a comma inside of them can be problematic, though.
There are programs used to join CSV files together and it may be easier to get and run one of those. One of those is q - Run SQL directoy on CSV or TSV files. If you have a Debian/Ubuntu-based Linux system available, this is contained in the python-q-text-as-data package. It supplies a command q that allows you to write SQL but operate on CSV files. I had a Linux box available to me and installed q and wrote the script below in less than 5 minutes (it helped that I was already familiar with SQL)
Assuming I've downloaded and extracted a provisioning report, this statement gives the four items you want (note, my provisioning file is from 2015, but it should still work). This is a single line, I just broke it to make it more readable.
q -H -O -d,
"SELECT u.first_name,u.last_name,e.status,c.long_name
FROM enrollments.csv e
JOIN courses.csv c USING (canvas_course_id)
JOIN users.csv u USING (canvas_user_id)"
You could then redirect the output to a file and have just that file. In Linux, you could redirect the output to a custom.csv filename by adding this to the end of the statement: > custom.csv
You really probably want more than just those four fields. Perhaps SIS IDs or Canvas IDs. You can modify the statement as needed.
If you don't have a Linux system available, don't let that stop you. When you look at the installation section on the q website, there is support for OSX and Windows as well. The Windows installer even includes Python itself so it is isolated from the rest of your system.
Excel will do it, but using the right tool will save you a lot of time and frustration.
.png "Chris_Munzo"){kind=avatar}
This discussion post is outdated and has been archived. Please use the Community question forums and official documentation for the most current and accurate information.