Sakai T&Q to Assignments Converter

sakai-tandq-to-assignments-converter.py

# This merges Tests and Quizzes submissions for an assignment with
# Assignments submissions by producing a text file containing the answers
# submitted for any user that did not submit using Assignments.
#
# Run this in the downloaded Assignments folder.
#
# Convert the "Tests and Quizzes" submissions spreadsheet to CSV
# using OpenOffice, then name that CSV file on the command line.
#
# <scriptname> Assessment-<Assignment Name>-<date>.csv template.txt
# By Thomas Shanks
import sys, csv, os.path, re

debug = False
generated_submission_file_name='TandQsubmission.txt'

#Read in text of template for submissions to be created
template_file = open(sys.argv[2], "rb")
template = template_file.read()
if debug:
	print template



#Tests & Quizzes submission parse-in
TandQ_users = {}
num_TandQ_users = 0
num_TandQ_turnins = 0
roster_file = open(sys.argv[1], "rb")
TQreader = csv.DictReader(roster_file)
for row in TQreader:
	num_TandQ_turnins += 1
	userid = row['User Name']
	if (not userid in TandQ_users) or \
	   (TandQ_users[userid]['# of Submission'] < row['# of Submission']):
		if not userid in TandQ_users:
			num_TandQ_users += 1
		TandQ_users[userid] = row
if debug:
	print TandQ_users

print ''.join(["Users submitting with 'Tests & Quizzes' at all: ", \
						`num_TandQ_users`, \
		"\nTotal 'Tests & Quizzes' Turn-ins (including revisions): ", \
						`num_TandQ_turnins`])

#read in from grade spreadsheet to find info on users
num_users = 0
assignment_turnins = 0
TandQs_added = 0
roster_file = open("grades.csv", "rb")
header = ''.join([roster_file.next(), '\n', roster_file.next()])
reader = csv.DictReader(roster_file)
for row in reader:
	num_users += 1
	path = ''.join([row['Last Name'].strip("'"), ', ',\
			row['First Name'].strip("' "), '(',\
			row['Display ID'].strip('"'), ')'])
	if debug:
		print path
	assert os.path.exists(path)
	assignments_turnin_dir = ''.join([path, \
					"/Submission Attachment(s)"])
	if os.listdir(assignments_turnin_dir) != [] and \
			os.listdir(assignments_turnin_dir) != \
					[generated_submission_file_name]:
		assignment_turnins += 1
	elif row['ID'] in TandQ_users:
		TandQs_added += 1
		TandQ_submission = TandQ_users[row['ID']]

		# Now create the TandQsubmission.txt and insert values.
		
		submission = template
		for item in TandQ_submission.items():
			fromtag = ''.join(['$$$#', item[0] , '#$$$'])
			if debug:
				print fromtag, item[1]
			submission = submission.replace(fromtag, item[1])

		assert submission.find("$$$") == -1

		submission_file_path = ''.join([assignments_turnin_dir, '/', \
					generated_submission_file_name])
		print submission_file_path
		submission_file = open(submission_file_path, 'w')
		submission_file.write(submission)
		
		if (debug):
			print submission



print ''.join(["Users: ", `num_users`, \
		"\n'Assignments' Turn-ins: ", `assignment_turnins`, \
		"\n'Tests & Quizzes' Turn-ins used: ", \
						`TandQs_added`, \
		"\nTotal Users Submitting: ", \
				`assignment_turnins + TandQs_added`])


#writer = csv.writer(sys.stdout)
#writer.writerows(reader)


#
#Copyright 2011 Georgia Tech College of Computing; Licensed under the
#Educational Community License, Version 2.0 (the "License"); you may
#not use this file except in compliance with the License. You may
#obtain a copy of the License at
#
#http://www.osedu.org/licenses/ECL-2.0
#
#Unless required by applicable law or agreed to in writing,
#software distributed under the License is distributed on an "AS IS"
#BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
#or implied. See the License for the specific language governing
#permissions and limitations under the License.
#

Sample Template

Homework NUM Tests and Quizzes Submission 
Prof. NAME NAME NAME
SUBJ XXXX/YYYY – SEMESTER 20XX

Converted from Tests & Quizzes spreadsheet by tshanks@gatech.edu's Sakai scripts

Your name (as in Sakai):	$$$#First Name#$$$ $$$#Last Name#$$$

Your Sakai User ID Number:	$$$#User Name#$$$

Submission Number: $$$## of Submission#$$$



Question 1 (NUM Points) : Question Text.

Answer: $$$#Question 1 text#$$$

Question 2 (NUM Points): For each of the following, match the thingy with the doohickey it uses.
Each answer is to be used at most once.
A. Option 	B. Option		C. Option

1.	This 
2.	That 
3.	Something Else


Answer: $$$#Question 2 text#$$$


Question 4 (NUM Points): Which one? 
A.	AAA
B.	BBB
C.	CCC
D.	DDD


Answer: $$$#Question 4 text#$$$


Question 5 (NUM Points): Please enter only AAAA or BBBB in each blank.
Put AAAA here               , and put BBBB here               .
               should be BBBB.


Answer: $$$#Question 5 text#$$$
 

Question 6 (NUM Points): Type "two" then "words" in the boxes. 


Answer: $$$#Question 6 text#$$$
                                                          

Question 7 (NUM Points): Type 2.0 here. 


Answer: $$$#Question 7 text#$$$


Question 9 (NUM Points): Example(s) of something:
A.	AA
B.	BB
C.	CC
D.	DD


Answer: $$$#Question 9 text#$$$


Question 12 (NUM Points): The system put this one in a different place in the T and Q version's spreadsheet.
 
Circle/highlight one:		True	 	False 


Answer: $$$#Question 13 text#$$$


Rationale: $$$#Question 13 rationale#$$$

I doubt anyone will encounter this same specific situation. However, bits of this script may be useful for creating a tool to assist with grading in the future.

The situation:
An assignment was posted first using 'Tests and Quizzes', then was taken down and re-posted to 'Assignments', as 'Tests and Quizzes' was not functioning properly. The original 'Tests and Quizzes' submissions were to be used for any student who did not submit again via 'Assignments'.

The script figures out which to use, then creates a human-readable, 'Assignments'-style text document for any student who only submitted using 'Tests and Quizzes'.

This is something that needed automating, as the 'Tests and Quizzes' submissions are packed in a single file that is not human, or even Excel, readable. The spreadsheet places answers, whether numerical or paragraph-length, in 'cells' within a single non-compliant Excel file that Excel will not open. In the cells of the file, it inserts from user submissions un-escaped characters that are supposed to be escaped. Excel refuses to open this, or, alternatively, cuts off parts of the text occurring after forbidden characters in each cell. OpenOffice will open it and will save it as CSV without trouble.

The next issue is that the 'Tests and Quizzes' tool arranges questions in different fields of each record depending on the randomized question choice/ordering it offered each student. Each is labelled with the question number it corresponds to. Only the number/letter of the choice is provided for T/F, multiple choice, and matching questions. For essays, the entire answer is shoved into the cell. The script decodes this soup into something that looks like the original test and orders the questions in the same order each time so that the answers can be graded.

I am told that 'Tests and Quizzes' will soon be retired. Perhaps not soon enough. I strongly suggest using 'Assignments', not 'Tests and Quizzes', to test your students.