Import classes from Education Edge into Moodle

Here are notes on my successful import of 200 classes into Moodle. This is part of a project to create a major assignments calendar for our upper school that calculates the number of major assignments by student rather than by grade, providing a more accurate view of potential conflicts before they happen.

First, I wrote a Perl script to export classes from Education Edge into a text file. I used the following SQL query and then formatted the results pretty.

SELECT DISTINCT
ea7classes.ea7classesid,
ea7courses.coursename,
deptentries.description as coursedept,
ea7courses.ea7coursesid,
ea7courses.courseid,
ea7classes.classsection,
periodstable.entryid as period,
ea7faculty.userdefinedid as facultyid,
ea7records.nickname,
ea7records.lastname,
ea7records.passportnumber as facultyusername,
ea7rooms.roomid
FROM
ea7courses
inner join schools on (schools.schoolsid = ea7courses.schoolsid)
inner join ea7classes on (ea7classes.ea7coursesid = ea7courses.ea7coursesid)
inner join ea7sessions on (ea7sessions.ea7sessionsid = ea7classes.ea7sessionsid)
inner join ea7academicyears on (ea7academicyears.ea7academicyearsid = ea7sessions.ea7academicyearsid)
inner join ea7classterms on (ea7classterms.ea7classesid = ea7classes.ea7classesid)
inner join ea7terms on (ea7terms.ea7termsid = ea7classterms.ea7termsid)
inner join tableentries as termstable on (termstable.tableentriesid = ea7terms.termid)
left outer join ea7coursefilters on (ea7coursefilters.parentid = ea7courses.ea7coursesid and ea7coursefilters.filtertype=163)
left outer join filtervalues7 on (filtervalues7.parentid = ea7coursefilters.filtersid and filtervalues7.recordtype = 1098)
left outer join tableentries as deptentries on (deptentries.tableentriesid= filtervalues7.filteridvalue1)
left outer join ea7classtermmeetings on (ea7classtermmeetings.ea7classtermsid = ea7classterms.ea7classtermsid)
left outer join ea7timetableentries on (ea7timetableentries.ea7timetableentriesid = ea7classtermmeetings.ea7timetableentriesid)
left outer join tableentries as periodstable on (periodstable.tableentriesid = ea7timetableentries.period)
left outer join ea7facultyclasstermmeetings on (ea7facultyclasstermmeetings.ea7classtermmeetingsid = ea7classtermmeetings.ea7classtermmeetingsid)
left outer join ea7rooms on (ea7rooms.ea7roomsid = ea7classtermmeetings.ea7roomsid)
left outer join ea7faculty on (ea7faculty.ea7facultyid = ea7facultyclasstermmeetings.ea7facultyid)
left outer join ea7records on (ea7records.ea7recordsid = ea7faculty.ea7recordsid)
WHERE
schools.schoolid = 'US'
and ea7academicyears.description = '2008-2009'
and ea7courses.ea7coursesid <> 404

ORDER BY
ea7classes.ea7classesid,
ea7classes.classsection

Then I installed and ran Moodle's Upload Courses contributed module. What a nice job the author did with this. Once I cleared all of the ambiguous teacher names, the courses imported in a flash.

upload results

I decided to create one Moodle class for each course/teacher combination. In other words, if one teacher teaches one section of ninth grade English, and a second teacher teaches two sections, I would create two Moodle courses. Teachers are pretty autonomous at our school, and this structure parallels what most teachers did with Moodle when I created courses only upon request. In cases where the teachers actually co-teach the course, I will need to remove one course and double up the teachers in the other remaining.

I embedded teacher initials in course IDs to distinguish them and provide a key for subsequent Education Edge searches based on this information, but will have to go back and embed them in the course fullnames as well -- the students can't currently tell the courses apart, because they only see the full names!

Rather than importing enrollments as well, I will allow students to enroll themselves on the first day of classes.

I edited config.php to show a limited set of default blocks for all the new courses. Unfortunately, the major assignments block I am working on did not show up, so I will have to add that subsequently with a script.

I am still working on a major assignments block for our Moodle that borrows concepts and SQL queries from Gary Anderson and pulls course enrollments from Education Edge. More on that later.

4 comments

  1. Greg Lemoine says:

    Richard.
    Greetings from Cambodia. Just wanted to reach out and correspond about Education Edge with you. Sorry this isn’t much of a comment about the blog. But it IS the first comment of some sort.
    If you are interested, I’d like to correspond and get your opinions. You sound very fluent in the EE software and I need some help in that area.
    Thanks, Greg (ITC teacher at International School of Phnom Penh)

  2. Richard says:

    Greg,

    Great to hear from you. I would be happy to share our experiences with you. Send me your email address when you have the chance using the contact form.

    Richard

  3. Erik says:

    This is great work! It cuts out a lot of steps that we’re currently having to do: export users from EE and upload them to Moodle, export classes, clean it up and use the Course Upload module to create the courses and assign the teachers. I don’t understand how you can use a Perl script to export from EE. I have heard that to have access to EE’s data from the command line you can pay Blackbaud extra. Is that what you’re doing?

  4. Richard says:

    Hi, Erik. We pay for a a module called "Open SQL Query Tool" in EE. They may also call it "read-only database access (RODBA)." This gives one the ability and rights to query the MSSQL database correctly. Once you have those rights and the associated user, you may connect to the MSSQL database as you would connect to any database from Perl or PHP. In Perl, we use the DBI CPAN module to establish a connection to the database. In PHP, we use PHP’s built-in MSSQL connection library. You write SQL commands and execute them, and DBI or PHP returns the results to a variable which you can then use. If you want to write to Blackbaud products, you must purchase an additional module. We have chosen not to do that at this time.

    RODBA is also needed to use Crystal Reports or another external query tool, so there are multiple uses for that product.

    Richard

    Richard