Importing Course Enrollments From Senior Systems into Schoology

Example course enrollment import file

Here's an example of a file we would provide to Schoology in the Import, Course Enrollments area.

Exporting from Senior Systems

We can export data from Senior Systems ion a way that will make this process simplier.

From the terminal server, go to E:/SeniorSystems/Setup/SeniorExcel

Run SeniorExcel.exe

Enter the following SQL query

Copy the following text:

select faculty.entity_no, rg_student.entity_no, rg_stu_sched.* from rg_stu_sched

left join rg_student on rg_student.id = rg_stu_sched .id

left join faculty on faculty.id = rg_stu_sched.faculty_id

order by rg_stu_sched.id

Paste the text into the Advanced tab

Paste the text, then select Export.

Edit the resulting spreadsheet

The spreadsheet may save as Book1.xls, open it and then get ready to "persuade" the data so it's ready for Schoology.

Persuading the data to cooperate.

Let's return to the sample course enrollment spreadsheet and compare it to the export from Senior Systems:

Let's use red text for SeniorSystems fields and blue text for corresponding Schoology Fields.

The first column from Senior Systems, Entity_ID, is the Unique ID for the Faculty member in that row for the course. This data resides in Senior Systems "behind the scenes", but can be exported through this method.

The second column Entity_ID_No1 is the Unique ID for the student in that row.

From the STUDENT_NAME, we'll need to create the First Name and Last Name Columns

It would seem that the Email column is not required from Schoology.

COURSE_FULL_NAME corresponds with Course Name.

By using a formula to concatenate, we can combine the SCHOOL_ID, an underscore "_", and the COURSE_ID to create the Course Code.

It appears that by concatenating the Course Code with an underscore and the year, we can create the Section School Code.

Enrollment is either a 1 for Teacher or 2 for Student.

Schoology support claims that Status can be ignored.

Course Import Mappings in Schoology

Faculty Mappings in Schoology

Remember, In users upload twice, selecting roles as faculty, then student, using the same spreadsheet and changing the mappings as shown below.

Student Mappings

There are two ENTITY_NO columns - one for the student in the row and one for the faculty - be sure to get the right one.

Notes from 2019-2020

Spreadsheet here: https://docs.google.com/spreadsheets/d/1UXlUCm718S9AkaMoOC83iLIMKhU19puj5oiT0F1-26Y/edit?usp=sharing

Followed instructions, downloading 3 spreadsheets from Senior - one via SENIOREXCEL, and then two from Registrar by right clicking after showing columns we need. Pasted those over last year's data, then updated lookup formulas as needed.

Added screenshots of import settings in Schoology on the upload above.

Had an issue with double-named courses in Schoology. Schoology uses COURSE: SECTION. so select Section No as Section name

Notes from 2018-2019

2018-2019 import spreadsheet: https://docs.google.com/spreadsheets/d/1kYHnV0yBaQXoGUk0Yo8A9DB6FRHDXpvpoAI7ejOS8Gg/edit?usp=sharing

Working off of last year's spreadsheet went well. Note that when exporting from Senior Systems, the columns might not be in the same order. Ether adjust the vlookup formulas, or arrange the columns.

Had an issue with CUSH GILLEN being course instructor in senior systems. Reached out to Pam U to address - 2018-08-21.

Notes from 2017-2018

NOTES FROM 2017 IMPORT

See this import spreadsheet - https://docs.google.com/a/meadowbrook-ma.org/spreadsheets/d/1D5XEdf_DK0fxSV6C8bjoMWU-jVeMSped6ZxS_GB0fl4/edit?usp=sharing

Use the seniorexel export, then use registrar to display the columns you need, especially ID, first, middle, last, preferred, and email address. Save those as excel files and bring them into your document

Use VLOOKUP with the FALSE option to find exact matches of the IDs when bringing over the email addresses and names.

Then upload this one csv multiple times - first for the users, but only students. Then for users but select the teacher roll. Then upload the file for courses. Finally upload for course enrollments.

In the spreadsheet above, any column made through a formula is highlighted green.