Moodle in English: Can i auto enroll from Excel?

Sorry, I'm new to Moodle and am in the process of setting up my first course or two. I have an Excel spreadsheet with logins/passwords for all of our First Year pupils. Is there an easy way to enrol them all at once as an admin?

If there is do i put the list in as potential students or as enrolled students.?The email authentication isn't vital to me.

Thanks for your help. I'm quite excited by the potential of this

You can't directly enroll the students from an .XLS file, but you can if you first save the sheet in CSV format, making sure that you have 1) the proper headings in row 1 and, 2) only the fields that are either required or optional represented on the sheet.

You can see which fields are required and/or optional by clicking on the help "?" under admin/users/upload users.

Here are the required fields, though:
username,lastname,firstname,password,email

If you want to enroll them in one or more courses, then just add ",course1,course2" (etc.) to the list of fields in row one, and then place the "short name" of the course (being careful of the case of the letters) all the way down the column.

Below, I've probably given more help than you need, but someone in the future might find this info useful. If you don't have your excel spreadsheet in exactly the correct format, here are some perhaps useful tips.

To create a spreadsheet with just the correct data

Move just the columns that you need to a separate tab. Excel normally creates a file with three separate sheets and the 2nd and 3rd often go unused, so click on the "Sheet2", click inside cell "A1", type and "=" sign, and then go back to the original sheet and click on the fieldname that you want to go in column 1 and hit "enter". Now, go back to "sheet2" and copy cell "A1" down so that all data from that column is copied over. Repeat this for all other required fields.

When you are done (have all required fields listed in "sheet2", save the sheet as CSV and you are ready to import.

Splitting a single name field into separate first & last names

If you currently have only a single field for names that reads "Doe, John", here is how to split it into two separate fields:

a) assuming that "Doe, John" is in cell "A2" and the next vacant column further to the right is "G", place the label "lastname" in "G2" and then in "G2" place this formula: =left(A2,find(",",A2)-1)

This formula will peel off the leftmost characters from the start to one place before the comma.

b) Now for the first name, place "firstname" in cell "H1" and then in cell "H2" place this formula: =mid(A2,find(",",A2)+1,40)

This will capture the characters from one place after the comma to the end of the name. ("40" could be any number that is longer than the longest first name.)

Creating usernames

A quick way to create user names is to combine the first letter of the first name with the last name. This works as long as you don't have a huge list of names, in which case you might end up with some duplicates. You could, of course, eyeball the list and suitably modify any of those that do happen to occur.

Here is how to automatically create them:

Assuming the same spreadsheet as above, in the next column, place "username" in cell, "I1" and then in cell "I2" place this formula:

=left(H2,1)&G2

This will concatentate the first letter of the first name with the full last name.

Password

I usually make the password the same as the username and then ask the students to select a new password the first time they log on. (There doesn't seem to be a way to force a new password on first log-on, if the accounts are created in batch mode. There is when the admin creates accounts manually. It could be great if there was a query for this as part of the batch upload process.)

Have fun!

I have a list of students with their names written in Hebrew characters. Is there any reason why I should use English characters instead? I don't want to run into problems later, but I know Moodle is supposed to be international.

Of course, you can use Hebrew characters. The only small problem is that the convenient user look-up system consisting of two rows of the alphabet, one for given name and one for surname won't be usable. If you only have a few users, then this might not be an inconvenience.

Here in Japan, we face a similar problem with the Kanji for names. What my school did to solve the problem is to put the roman alphabet given name in the given name field, and then in the surname field, the roman surname FOLLOWED BY a space and then the Kanji name. That way, the two rows of alphabet still worked for look-up purposes (and for native English speakers who can't read the Kanji) but the Kanji was still there, too.

Why wouldn't it work for looking up by alphabet? Hebrew has an alphabet and as long as the language of the site was set to Hebrew, I can't imagine it would be a problem.

I am under the impression that the alphabetic look-up function isn't implemented for other alphabets. Do you know for sure that it is for Hebrew? I just set this site to Hebrew and tried a look up (as adminstrator) and the alphabetic look-up function was not displayed.

Maybe not in 1.5.3. But I would presume it is coming in 1.6 since it is supposed to be unicode compliant. Any unicode compliant program should be able to sort any alphabet in proper order.

Okay, I managed to create the text file and upload to enroll them in the correct class, but look at how the Hebrew names appear, nonsense characters like: "ùçø áåøåðåáñ÷é"

ùøåï áï îìëé
Email address: y@gmail.com
Location: town, Israel
Last access: Never
Activity
Unenrol
Login as
Full profile...

ùçø áåøåðåáñ÷é
Email address: y@gmail.com
Location: town, Israel
Last access: Never
Activity
Unenrol
Login as
Full profile...

ùçø ðöø
Email address: y@gmail.com
Location: town, Israel
Last access: Never

It sounds to me like you need a Hebrew language pack, probably the UTF-8 one. You can get them here:

http://download.moodle.org/lang/

Gavin

i can't upload users using CSV files format with arabic names for users any ideas how to solve this (usnig moodle version 1.6) ,

Note : when i save the excel sheet with CSV format it save the files with seperated coloms (;) so i get an editor to replace the (;) with (,) to let moodle accept it

thanks

Okay, the problem was Firefox not displaying Hebrew characters. If I use IE and select Encoding Hebrew the usernames appear correctly (and they're not even backwards). But now I can't login. The student's login is fname-lname in Hebrew characters. But the login doesn't work:

Username:
Password:

Hi Thomas; Thanks for your tips, are really good. I can see that I can manage all my users using CVS file format (from Excell). But I'm not sure what happend if I like to add a new users, just will be necessary to put this new users into the same file and upload it again (with all the old users)??, what about with the old users information ?? and what about if I have a username twice??.

If I like to delete some users, Can I use the same file??

I'm using Moodle for create Intranets. I have 5 intranets, and I have users working in just one of them, but some users are working in more than one. I receive new users every time, and I like to manage all users using an external file (add, delete, changes, courses). Can I??

Sorry for my english and Thanks a lot for your answers
Alejandro

You don't need to re-upload the old users along with the new ones - in fact you can't, because you'll be trying to re-create the existing users and Moodle will object to the duplicate usernames.

At the moment (at least in 1.5) you can't do a bulk delete, even from a CSV file - users have to be deleted individually.

Chris

I'm not sure what you mean by "Intranets". Are all of the students accessing the same Moodle server, or do you have 5 Moodle servers?

I'm also not sure why you need to enroll them yourself. Doesn't the email-based registration method work in your case? In many Moodle systems, ANYONE can get a username via the e-mail/confirmation process, but the username is useless because the courses themselves have an enrollment key which they don't know. This is given to them by the instructor.

At any rate, the Excel batch upload process only works for 1) creating new users and 2) adding new and old users to courses. Deletion must be done manually or by setting the "longtimenosee" value in the site settings to a short period of time so that students who have not accessed the site in say, 30 days, are automatically deleted.

Another approach would be to use "database authentication" where all of the data is kept in an external database. You can create your own user table as an mysql file, for example, and have multiple Moodles all point to the same database. Deleting someone from this database would then prevent access to any of the Moodles that were connected to it.

I hope this helps.

THANKS,

Any chance I could speak with you to get a couple of questions answered.

abriteguy@aol.com

Bellmore, NY, USA1 1710

Hi,

Where do I click to do the actual Enrol? I'm at the Enrol Students screen but I don't see an import list button.

Tom

Hello,

I followed your procedure but I got trouble with Names or Surnames with special French caracters (é,ç,ï...) : Moodle did not create an account for those students. Is there anything to specify somewhere in Moodle in order to avoid that problem?
Thanks
Philippe

>Password

>I usually make the password the same as the username and then ask the
>students to select a new password the first time they log on. (There
>doesn't seem to be a way to force a new password on first log-on, if the
>accounts are created in batch mode. There is when the admin creates
>accounts manually. It could be great if there was a query for this as part
>of the batch upload process.)

just a pointer - if you set the password in the text file to "changeme", this will force the new user to change their password - I just found this, and used it - it's cool :')

.h

Import works for me, but the users cannot login afterwards. Profiles are there, CSV contains the password field, which shows on the import preview page, but does not show in the password column of import results. Passwords comply with security policy. For the moment I am out of clues what could be the problem.

Hi.
Can anyone please tell me about the right format for csv file while uploading a list of users in moodle. I am getting the following error:
"User not added - error"

Also where can I check the error?

Regards,
Parwez

Thank you. Excel tips very useful!

Can someone digest these entries and create a "Now that moodle is installed, what's next? document.

Although once you've got it it's easy, I find setting up users to be an stumbling block to getting started with moodle.

How do you use template as described to make username for example:

%l%1f

Like does it say somewhere that you should set up a course BEFORE you upload users so the can be auto enrolled?

Have you read through the docs? http://docs.moodle.org/en/Upload_users

How do I code a field in my Excel file to upload student mobile phone numbers? The following don't work: mobilephone mobilephone1 Thanks.

I have created a VBA Macro to help me in the process of importing users from a Excel sheet to the Moodle. My macro creates a file that I use later into Moodle.
It could be used as a template for others that need this function and should be changed to fit the required fields for that environment. For me, firstname.lastname is required, so change accordingly.

'Author: Cleber P. de Souza
'E-mail: cleberps@gmail.com
Function MoodleImport(FullName As String, Password As String, email As String, _
        course As String, city As String) As String

Dim Arquivo As String
Dim Delimitador As String
Dim FilePtr As Integer
Dim VetorName() As String
Dim UserName As String
Dim FirstName As String
Dim LastName As String
Dim i As Integer
Dim ArquivoExiste As Integer
Dim Status As Integer

Status = 0
ArquivoExiste = 0
Arquivo = "mdl_import.txt"
Delimitador = ","
VetorName = Split(FullName, " ")
UserName = LCase(VetorName(0) & "." & VetorName(UBound(VetorName)))
FirstName = Trim(VetorName(0))
LastName = ""
For i = 1 To UBound(VetorName) Step 1
    LastName = LastName & VetorName(i) & " "
Next i
LastName = Trim(LastName)

If Len(Dir(Arquivo)) > 0 Then
    ArquivoExiste = 1
End If

FilePtr = FreeFile
Open Arquivo For Append As FilePtr
    If ArquivoExiste = 0 Then
        Print #FilePtr, "username" & Delimitador & " password" & Delimitador & _
            " firstname" & Delimitador & " lastname" & Delimitador & " email" & _
            Delimitador & " course1" & Delimitador & " city"
    End If
    If InStr(1, email, "@", vbTextCompare) > 0 Then
        Print #FilePtr, UserName & Delimitador & " " & Password & Delimitador & " " & _
            FirstName & Delimitador & " " & LastName & Delimitador & " " & email & _
            Delimitador & " " & course & Delimitador & " " & city
        Status = 1
    End If
Close #FilePtr

If Status > 0 Then
    MoodleImport = "OK"
Else
    MoodleImport = "BAD"
End If
End Function

i can't upload users using CSV files format with arabic names for users any ideas how to solve this (usnig moodle version 1.6) ,

Note : when i save the excel sheet with CSV format it save the files with seperated coloms (;) so i get an editor to replace the (;) with (,) to let moodle accept it

thanks

For arabic names i think you'll need implement some kind of conversion.
On the Moodle Docs (http://docs.moodle.org/en/admin/uploaduser) you can find out that special characters must be converted to the decimal representation from the char to be correctly interpreted. A full list of the decimal values for chars you could find here: http://www.neurophys.wisc.edu/comp/docs/ascii.html.
Something such as creating a sheet with all the translations and a vlookup function into vba can meet your needs.

Hi i want to upload students in to moodle nearly 500 students and enrole them into all groups of all the courses .
I tried to upload students and it works but , only one time for each group , and they are added as "editing teachers" !!!

Is there any good way to do that???

Thanks
Nada

I also have a question i'm hoping you can all help with...

I'm uploading the data for about 130students and I need them to be enrolled in 15 courses, I've added all the fields across the top, course 1-15 with all the appropriate field names, but I get an error after 'course5' stating that 'course6' is not a valid field name, is there a maximum?

Greetings, Chris!

I do believe that 5 might be the maximum. I can tell you that we run the upload users file multiple times (every night during the add drop period). You can have quite a large file (ours is some 1400 students x 4-5 courses each lines long). So just run it more than once after you split your data set. I think it should work.

atw

Thanks, I also figured that through some customising of the script you can add more fields, but I'm having unrelated problems anyway in that the script refuses to import any users from one document and the users but no course information from another, I think it's something to do with encoding, but I spent 5 hours trying to figure it out yesterday and drew a blank...

I then used open office spreadsheet instead and it worked fine... Hmm, Microsoft strikes!

Any help on expanding the amount of courses would be helpful though.

Possibly you had some empty records. I usually export the excel spreadsheet into csv format and then open it in notepad to check the end of the file. If you see lots of rows of ,,,,,,, you have a problem! But you won't know it unless you check in a text editor.

atw

please help me in this problem

i uploded users from a file and it worked good

now i want to enrol students i transferd to courses

i have a file csv with (add , student,idnumber,idnumber)

where must i put it ? and how can i fire it, i feel it is not just like users upload

it is not clear, i tried to search but i did not understand.

pleeeeeeease help me

There are two options.

You appear to be using the flat file enrollment formated file.

The location for this file is specified by the settings under
Site Administration -> Courses -> Enrollments -> Flat File Settings

I believe this file is automatically checked as part of the cron.php script run. So it would should run every time you have the cron.php run.

Alternately you could use the same user upload format that created the users and add columns for the courses and then upload the file again.

I'm trying to bulk update the Funding field by using a .csv, but are unable to work out what the field is called. Anyone know ?

I'm posting this as a help to others using the USER UPLOAD process.

I do a lot of enrollments from excell spreadsheets and, unfortunately, have a lot of experience fixing bugs in the data. Some frequent issues in data I've repaired: The error statement usually doest help enough but use the development mode anyway.

Unexpected binary or non alphanumeric characters. for example, a phone number like "+1-713-555-1212 (Houston Office)". Sometimes a binary or return character follows a correct looking field. I have Anglicize Spanish names to get my English language system to accept them.

Trailing spaces in fields not expecting them. This is particular bothersome, since you can't see the spaces. Use find and replace to locate them.

Cases letters spelling out entries normally made from drop-down lists. Double check that you are using contry codes and not names of countries, i.e., "CA" and not "Canada"

Some people put in "," Commas as in "Beazley, William" in a name field and it creates a missmatch on data fields. I've seen commas where periods should be in emails.

Sometime you can look at the text in notebook or other text editor and see the spaces or special character. Sometimes, changing the text type to ISO will jam the data into Moodle where you can edit the ones that issure error messages.

Try uploading one or a few lines at a time to find the line giving trouble.

Even with all the editing and force feeding and fixing, it's still faster than cut and pasting one item at a time.

Today, I uploaded 56 names with enrollments and group assignments. I had to create about 15 seperate CSV files to debug and track down each error in the data. Garbage in garbage out.

I hope this helps someone.

Bill Beazley

Thanks for the advice Bill. I would be interested to know where the data you are using for the spreadsheets is coming from. Do you use a data form in Excel for your users to complete? Do you have set fields for your users to type in the data? If you do, what fields are you using? Do you have access to Open Office as well as Excel?

Sorry for all the questions but I am building a spreadsheet with Open Office at the moment (which I shall duplicate in Excel subsequently) that hopefully will use data integrity to avoid some of the problems you mention, then filter and parse the raw input data to create a clean CSV output for upload to Moodle.

Paul

I hope this could help some people.

I was getting "User not added" error for some users, with no indication as to what the error could be.

After fiddling with all kind of syntax variations, I found that the name of the institution a user is affiliated with is limited (not sure of the exact limit, but it seems to be near 50 characters).

Hope that could help someone else!

General help

Can i auto enroll from Excel?

To create a spreadsheet with just the correct data

Splitting a single name field into separate first & last names

Creating usernames

Password

>Password