Introduction About Site Map

XML
RSS 2 Feed RSS 2 Feed
Navigation

Main Page | Blog Index

Monday, March 24th, 2014, 5:17 pm

Moodle Import of Users and Courses, Automatic Enrollment

Adapted from a work project in order for a more general audience to benefit

744px-Moodle-logo.svg

Provided one stores data in a format which is importable into Moodle (comma-separated list with specific data fields), a lot can be achieved very rapidly, making a migration to this powerful Free/libre software VLE rather painless. After some hours of work on a test site I decided to share my lessons so that others can benefit. In the spirit of sharing I will also take note of common errors that may come up and how to overcome them. If data export from an old VLE system is not consistent with the requirements of Moodle, then the migration may not be so simple. It may require several iterations.

Using a test site, which was recently upgraded to the latest version of Moodle (2.5.x), I read some test files and encountered issues. The process did not work as smoothly as I had expected and I really needed debugging mode enabled, as we shall see later.

Importing Users

Debugging mode yielded the following error for example:

 

Debug info: Incorrect integer value: ‘yes’ for column ‘autosubscribe’ at row 1
INSERT INTO vle_user (username,firstname,lastname,email,password,institution,idnumber,phone1,phone2,address,autosubscribe,mnethostid,city,country,lang,timezone,mailformat,maildisplay,maildigest,htmleditor,department,url,description,descriptionformat,auth,confirmed,timemodified,timecreated,suspended) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array ( [redacted]
)]
Error code: dmlwriteexception

 

 

Stack trace:

 

  • line 426 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1089 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1131 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 793 of /admin/tool/uploaduser/index.php: call to mysqli_native_moodle_database->insert_record()

 

 

Replacing “,yes” with “,1″ (replace all, using sed or a text editor) yields a properly formatted file that has integers for booleans, overcoming the first barrier.

The next issue is field length:

Debug info: Data too long for column ‘address’ at row 1
INSERT INTO vle_user (username,firstname,lastname,email,password,institution,idnumber,phone1,phone2,address,autosubscribe,mnethostid,city,country,lang,timezone,mailformat,maildisplay,maildigest,htmleditor,department,url,description,descriptionformat,auth,confirmed,timemodified,timecreated,suspended) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array ( [redacted]
)]
Error code: dmlwriteexception

 

 

Stack trace:

 

  • line 426 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1089 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1131 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 793 of /admin/tool/uploaduser/index.php: call to mysqli_native_moodle_database->insert_record()

 

 

I have abbreviated this by truncation to overcome the issue, repeating for a few other users (relatively speaking, as it’s almost 10% of all users in my test case), resulting in an issue with long phone numbers (data needs to be constrained further):

Debug info: Data too long for column ‘phone1′ at row 1
INSERT INTO vle_user (username,firstname,lastname,email,password,institution,idnumber,phone1,phone2,address,autosubscribe,mnethostid,city,country,lang,timezone,mailformat,maildisplay,maildigest,htmleditor,department,url,description,descriptionformat,auth,confirmed,timemodified,timecreated,suspended) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array ( [redacted]
)]
Error code: dmlwriteexception

 

Stack trace:

  • line 426 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1089 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1131 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 793 of /admin/tool/uploaduser/index.php: call to mysqli_native_moodle_database->insert_record()

 

 

A few other phone number data fields contained alphabetic characters in them, not just a lot of spurious characters that make the length exceed the limits.

The address (and other) field length constraints are noted in here.

To summarise, three issues needed to be addressed:

  • Boolean fields should be encoded in binary (0 or 1), not in words
  • Phone numbers should be made compact, with no irregular elements in them
  • The address length must be limited, or Moodle’s database schema altered to accommodate long addresses

Here is a visual illustration of the steps involved.

The following image shows the page facilitating users import.

upload1

 

The data file can be either dragged or added using the following file selector.

upload1-selector

Preview of a specified length is then displayed, just before data import starts.

upload2

Further options are made available to better control the import process.

upload2-settings

Errors may be encountered, in which case debugging mode needs to be enabled (making the output verbose).

debug

If no errors are encountered, users will be added and a summary shown at the end to highlight weaker alerts (such as weak passwords).

upload3

Congratulations. The users are now imported and are searchable, modifiable, and manageable too.

users

It is worth noting that fields can be specified for City/town and Country rather than them being mass-handled, defaulting to whatever the installation process was defined to have.

Importing Courses

Course creation should be possible using a file with comma-separated values and a plug-in which will be part of Moodle core in version 2.6 (or later). It should be noted that the import feature is not yet included in the stable versions of Moodle and thus it needs to be added manually, unless one hops onto a test build (nightly) of Moodle, probably version 2.6.

As noted in here, “this is now in Moodle core as of 2.6. Moodle Admin Tools plugin for basic upload of course outlines, and applying templates using Moodle course backup files”

There are 315 installed plug-ins in the test environment, 60 of which are disabled. The plug-in to install is downloadable from github and it comes with a README.txt summary of steps to take. Here is the process summarised visually.

Place the uncompressed archive at the standard destination directory for plug-ins.

plugin0

Now, log out of Moodle and log in again, as “admin”, in order for the upgrade to be invoked.

plugin1

Continue as instructed.

plugin2

Ensure all the code is up to date and thus compatible.

plugin3

Permissions are very important here. Ensure that permissions of 644 and 755 (for sub-directories) get set/assigned by chmod to all files, otherwise there will be internal server errors (code 500). If all works correctly, a new option will appear.

plugin4

This can be accessed if the permissions on the files are correct.

plugin5

Upload form is similar to the one for “Users”.

plugin6

This part may be tricky, as I was getting the following common error with the existing data exported from the old system: “There is something wrong with the format of the CSV file – please check the number of headings and columns match, and that the delimiter and file encoding are correct (don\t use comma-quoted as Moodle does not support it): Invalid CSV file format – number of columns is not constant!”

I have tried many variations of this, reducing the data file to as little as two lines (several different lines with headers) to see if this helps overcome the above issue, but it always comes back with the same error message.

Oddly enough, even the examples at the official documentation page does not work.

For instance:

fullname,shortname,category,sortorder,idnumber,summary,format,showgrades,newsitems,teacher,teachers,student,students,startdate,numsections,maxbytes,visible,groupmode,timecreated,timemodified,password,enrolperiod,groupmodeforce,metacourse,lang,theme,cost,showreports,guest,enrollable,enrolstartdate,enrolenddate,notifystudents,expirynotify,expirythreshold,teacher1_role,teacher1_accountGreatest Course,GC101,Education Portfolios,1,,University Portfolio,topics,0,0,Owner,Owners,Visitor,Visitors,1/14/2008,10,15728640,0,0,1/12/2008,1/12/2008,portfolio,0,0,0,,,,0,2,1,1/14/2008,5/10/2008,0,0,10,editingteacher,lastname1.firstname@email.edu

do not work; basically, this is what the plug-in returns:

 

“sortorder” is not a valid field name

 

 

 

A similar error message is returned if much of the content from the original test file is watered down to just a few lines. This might be an issue with the importer, which is not yet included in the core program (not reliable enough for release). Without being able to automate course import it will be hard to automate its linking to users.

Upon further examination, I can import some data by removing the field “sortorder” and removing many of the fields after “summary” (some of them are not supported and trigger errors during interpretation/parsing).

Some more debugging and changes to the structural nature of the data would enable more data from the old system to be preserved and imported. Here is how the GUI is used to make up for missing information at import time:

plugin7

And further down there is more.

plugin8

In summary, it seems like the fields need changing before import can be achieved with maximal reuse/preservation of data. This needs some more trial and error. The plug-in is not so well documented and it is work in progress, as explained in the Moodle tracker (see comments).

To use a most simplified example to test import, consider the following input file:

fullname,shortname,category,idnumber,summary
Competitive Athlete,CA,”Miscellaneous”,1,”Test”

The corresponding import success notification would look as follows.

course1

In Courses, field names that are properly coded and technically supported are not necessarily as documented (some documentation is well out of date). “teachers”, for example, is not a valid field name and “sortorder” is causing issues too. The data format is not ‘Moodle-ready’ unless all the fields are understood and the data can thus be parsed. It may be necessary to make many modifications for Moodle to absorb some data. There are some more undocumented fields in which are not supported, “students” for example. One can omit such fields manually (or using spreadsheets software) for purposes of testing imports until the code stablises and the suitable documentation brought into alignment. “category” is an important field name as it specifies what the courses is clustered with. This needs to be a string, not a number, e.g. “Miscellaneous” (default category in Moodle). If the data about courses can now be imported for testing purposes, then the next step may follow. I shall be documenting progress of importing content, which I found while I looked for the best solution for mass import of raw HTML data (which lacks some metadata/information relating to how it’s linked to a course). This needs to be done semi-manually with mass conversions although it depends on how the data being imported into Moodle actually was constructed/encoded. Some move away from Blackboard and some from privatly-crafted systems that act as a VLE.

Importing Course Content

In order to import static HTML content as pages into courses (a catch-all case because all pages are reducible to that) we need to hack around the existing framework or find a plug-in. In Moodle 2.5.x, three main components exist for retaining course information and resources that are textual. These are:

  • Forum posts
  • Blogs
  • Messages

The above can be typed in using easy-to-use WYSIWYG interfaces and everything is date/time-stamped. Resources and activities are also possible to import, as described in http://blogs.sussex.ac.uk/elearningteam/2013/01/15/improving-moodle-import-part-3-the-application/ (one of several such posts from Sussex, which facilitates importing).

 

 

Mass-enrolling and privilege/role setting

It is necessary to first add “participants” to the course, based upon some import process. Go to the Participants menu.

course2.png

Edit the enrolled users.

course3.png

Click on “Enrol users”.

course4.png

Self-enrol the system administrator. Then, elevate privileges.

course5.png

Adding content

While it is possible to register to external blogs, as described in
http://docs.moodle.org/25/en/Using_Blogs#External_blogs , it would be preferable to add HTML files or mass-import them.

Other institution sought to achieve this and there are threads about it in https://moodle.org/mod/forum/discuss.php?d=35581 and http://docs.moodle.org/23/en/Import_course_data

The problem is, the import is from one Moodle part to another, as shown below.

course6.png

There is a video demonstration of this whole process in http://www.youtube.com/watch?v=xQStUOfDe5w

The course can be configured to have peripheral files, which may also be HTML files. There are many options there as shown below.

course7.png

Here is the part where uploads are facilitated.

course8.png

Uploaded data needs to be referenced by the VLE and I cannot find plug-ins that enable mass upload of enclosures which also notify Moodle through the database (referencing and dereferencing). Summary files can not be HTML formatted, so an alternative route is required for import. Questions, for example, have their own versatile importer, which looks as follows.

course9.png

 

Automating Enrolment

Apart from the GUI that allows selecting multiple users (see Mass-enrolling and privilege/role setting) and performing actions on all (e.g. assign/enrol/give role), we could use SQL queries (in MySQL front ends/command line) to find and apply changes outside the framework, but this is more safely done with plug-ins, which we need to find (it’s hard to find any). If we import the users with some additional custom fields, then they can be filtered based on what course they are enrolled to and then modified accordingly in the Moodle-specific fields.

One Response to “Moodle Import of Users and Courses, Automatic Enrollment”

  1. Mark Simpson Says:

    This is probably old info for you but I found that in my CSV file in Excel if I delete the blank column after the last data column, and delete the blank row below the last data row, the CSV file works. I am using Moodle 2.9.

Technical Notes About Comments

Comments may include corrections, additions, citations, expressions of consent or even disagreements. They should preferably remain on topic.

Moderation: All genuine comments will be added. If your comment does not appear immediately (a rarity), it awaits moderation as it contained a sensitive word or a URI.

Trackbacks: The URI to TrackBack this entry is:

https://schestowitz.com/Weblog/archives/2014/03/24/uploading-data-into-moodle/trackback/

Syndication: RSS feed for comments on this post RSS 2

    See also: What are feeds?, Local Feeds

Comments format: Line and paragraph breaks are automatic, E-mail address never displayed, HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to top

Retrieval statistics: 21 queries taking a total of 0.410 seconds • Please report low bandwidth using the feedback form
Original styles created by Ian Main (all acknowledgements) • PHP scripts and styles later modified by Roy Schestowitz • Help yourself to a GPL'd copy
|— Proudly powered by W o r d P r e s s — based on a heavily-hacked version 1.2.1 (Mingus) installation —|