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

rovided 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.

 

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

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

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

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

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

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

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.

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

Continue as instructed.

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

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.

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

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

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:

And further down there is more.

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.

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.

Edit the enrolled users.

Click on “Enrol users”.

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

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.

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.

Here is the part where uploads are facilitated.

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.

 

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.

This entry was posted on Monday, March 24th, 2014 at 5:17 pm and is filed under CMS. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.