Creating Marks Spreadsheet (CS 116)

Near the beginning of the term, you will need to add the new students to the marks spreadsheet located in the marks folder. The steps for doing this follows.

In the course account there should be a hidden file (insert link on how to see hidden files) called .classlist.yymm where yy is the year of the current term and mm is the month in which the current term started. For example, the file for fall 2013 is ".classlist.1309". This file should contain a list of students as such:

#Id:Userid:Name:Lecture:Study:Plan:Group:Year:Degree:Initials:Family:Status:Time:Session:Sections
77777777:j1doe:Doe, John:001:stat:statistics:math:4:H:JD:Doe:R:F:1:lec=001,tut=101,tst=201
88888888:j2doe:Doe, Jane:002:fine:art studio practice:art:4:H:JD:Doe:E:F:1:lec=002,tut=101,tst=201
99999999:gasnicho:Nicholls, Gradon:003:stat:statistics for health:math:3:H:GN:Nicholls:R:F:1:lec=001,tut=101,tst=201

We need to split this data up in a spreadsheet format. To do this, open the .classlist file in Excel 2011. Highlight the header line and all the student lines. Then, go to data->text to columns in the menu. Using a delimiter of ":", this will split the data up into a nice table format. Keep this file open but do not save it (or if you do, save it as a new file and not as .classlist.yymm).

Now copy over the data (minus the header) into the CS116MasterStudentList spreadsheet located in the "CS116MarksFrom[term]" excel workbook which should be in the marks folder. The data in the two spreadsheets should follw the same coumn order, but if for some reason it doesn't you could copy and paste by column or use vlookup to transfer the data.

There is one final step. You still need to update the AssignmentMarkEntry spreadsheet (also located in "CS116MarksFrom[term]"). To do this, copy the username column from CS116MasterStudentList and use "paste special" to paste the values into the username column of AssignmentMarkEntry. The rest of the spreadsheet should sort itself out automatically (for example, the name column in AssignmentMarkEntry).

-- GradonNicholls - 2013-09-18