Moodle Monday: Excel tips for uploading users

1053
We’ve now finished helping out our schools get the new student data into Moodle installations so I thought I would finish the posts related to the new year by sharing a few quick little Excel tips. This will vary from school to school but in general the data we get out of the MIS looks a little bit like this.

And we need it to look a bit more like this.

WIRIS

This can be a fairly daunting task if you’re not that familiar with Excel, so below I’ve made a quick list of things we’ve found to speed the process up.

Find and Replace
This can be used for so many little jobs- great for changing large amounts of text. In our example above we did a find and replace on the word Drama to turn it into 10/Dr (which is the correct short name for the course).

Text to Columns
In the example the student name appears in one cell, Moodle needs it to be in two. Select the column, go Data, Text to Columns. From here you can tell the wizard that a space separates the data and it will neatly split into two columns for you.

Lower Case
The surname in our example above is in upper case, I want it to be lower. Simply putting LOWER(cell ref) sorts this job for you. But, I would prefer it if the first letter was still a capital. More on that later…

Joining Cells
Got Dave in one cell and Smith in the next? Good.. Now I want to turn this into his username and his email address. Username is easy, it works out as firstname.lastname. To do this you use a formula similar to =C2&”.”&D2. Want to add the email bit to this as well? How about =C2&”.”&D2&”@someschool.com”

Remove Duplicates
Got lots of duplicate rows you don’t need? Try Data- Advanced filter- unique records only. Bit of a pain but there’s a little guide here if you need this.

Making that surname Smith rather than smith
For this one you’re after the CONCATENATE function. Something a little like this
=CONCATENATE(UPPER(LEFT(A1,1)),RIGHT(A1,LEN(A1)-1))
will reproduce whatever you have in cell A1 with the first letter in upper case. This one was courtesy of the lovely people at Mrexcel.com.

Once you’ve got there, remember to save a version as a csv and Moodle should be happy to update all your data. More than happy to support people in this process as we’ve probably encountered most of the things you could do wrong by now! Feel free to ask in the comments, or you can find more of my work over at tdalton.co.uk.