Bulk Import of Gradebooks

Well, it doesn't exist yet and you're the first to ask, as impossible as both may seem. But of course, it is a necessary feature.

One reason it hasn't been done is that the import format is not standard. What does your data look like?

My data was originally in tiny SQLite files (one file per academic program
per academic class-year). I have used an export tool and VBScript to
produce "master" csv files--one for the students, one for the
courses/sections, and one for the grades/marks. Because I'm combining all
of the academic years, some of my fields are calculated in order to link
the corresponding data from the tables, and to differentiate between
programs and academic years.

More to the point, the csv export for the grades/marks has the following
fields:
studentID (unique, alphanumeric)
subjectID (unique, alphanumeric)
grade (alphanumeric)
preresit grade (alphanumeric)
resit1 grade (alphanumeric)
resit2 grade (alphanumeric)
Univ grade (alphanumeric)
UnivCA grade (alphanumeric)
UnivExam grade (alphanumeric)
UnivExpect grade (alphanumeric)

- I would be more specific about the data types, but I just have them as
CSV fields. One complication is that some types of courses only have letter
grades or Pass/Fail grades, and a few numeric grades are not integers.

- Another relevant complication is that the marking scheme has also changed
during the time period covered by the data--the passing numeric mark and
other boundaries changed. (I don't know if there is a way to accommodate
that shift for past data; i.e. whether each gradebook file should specify a
marking scheme that already exists in the SchoolTool data.)

- Based on what I know about SchoolTool gradebooks so far, it is
essentially a spreadsheet of studentIDs and graded assignments. In my case,
the assignments would be the 8 values I have for each student per course.
Most of these are blank, because they were unneeded or unrecorded. So
null/empty values must be acceptable and should be omitted from the overall
mark. When multiple fields are populated, I would need to add logic to use
one field for the final mark, in some type of if/then or "replace"
hierarchy.

- I'm already pretty far along on scripts to get my original data into the
form needed for SchoolTool's built-in Excel spreadsheet import. That
shouldn't take much longer.

Other notes:
Most of my programming/coding/scripting experience is unfortunately in a
Windows environment (VBA, VB.NET, VBScript), though I've also worked quite
a bit in SQL, javascript, and HTML. So while I would not call myself an
expert, I am by not a novice and [I hope!] I'm likely to understand if you
are able to offer suggestions or technical information.

A final note:
I've been thinking I should learn Python at some point soon, since in my
current role I'm becoming more familiar with Linux. (I'm dual booting
Windows 7 and Ubuntu 12.04.) If it would be simpler to implement some sort
of import script/module in Python, I would be willing to try if you point
me in the right direction with regard to how I could properly access and
modify SchoolTool's gradebook data. And once I had it working properly, I'd
be happy to donate my [beginner] Python code so that someone else could
improve it and make it available to others.
(But most likely it would be faster for someone with experience to just
code it themselves, I would guess, so I understand if my contribution might
not be that helpful.)
Of course I would try to generalize my script(s) so that anyone could
import using studentIDs, assignment names, marks for those assignments,
etc., but I assume the gradebook data would also need to contain the logic
for how to weight/use the individual marks.

Sorry for the long response...I'm pretty sure we're in quite different time
zones, so I'm erring on the side of giving too much information at once.

Thanks!
Jonathan

Hi Jonathan,

I'm in Providence, RI, USA, so we are somewhat out of sync.

The way this translates to SchoolTool is that we would create a "report sheet" with these activities:

grade (alphanumeric)
preresit grade (alphanumeric)
resit1 grade (alphanumeric)
resit2 grade (alphanumeric)
Univ grade (alphanumeric)
UnivCA grade (alphanumeric)
UnivExam grade (alphanumeric)
UnivExpect grade (alphanumeric)

Assuming those represent different scores.

The report sheet is deployed to each section in a year and/or term, so you basically have a uniform set of data in each section that you can pull for reports (as opposed to gradebook score sheets, which are unique to each section).

But basically, yes, we should be able to write a script to parse this data and dump it into report sheets. At this point, it would probably be best to do it more or less as a quick custom job so we can get some experience with the process quickly. So... I think you're on the right track as far as what you're trying to put together for us.

What's your timeline for getting this in production?

I would like to be able to do this by the first full week of August. But I
just need to know that I -will- be able to. Thank you for your help!
On Jul 5, 2013 7:31 PM, "Tom Hoffman" <email address hidden>
wrote:

> Your question #231867 on SchoolTool changed:
> https://answers.launchpad.net/schooltool/+question/231867
>
> Status: Open => Needs information
>
> Tom Hoffman requested more information:
> Hi Jonathan,
>
> I'm in Providence, RI, USA, so we are somewhat out of sync.
>
> The way this translates to SchoolTool is that we would create a "report
> sheet" with these activities:
>
> grade (alphanumeric)
> preresit grade (alphanumeric)
> resit1 grade (alphanumeric)
> resit2 grade (alphanumeric)
> Univ grade (alphanumeric)
> UnivCA grade (alphanumeric)
> UnivExam grade (alphanumeric)
> UnivExpect grade (alphanumeric)
>
> Assuming those represent different scores.
>
> The report sheet is deployed to each section in a year and/or term, so
> you basically have a uniform set of data in each section that you can
> pull for reports (as opposed to gradebook score sheets, which are unique
> to each section).
>
> But basically, yes, we should be able to write a script to parse this
> data and dump it into report sheets. At this point, it would probably
> be best to do it more or less as a quick custom job so we can get some
> experience with the process quickly. So... I think you're on the right
> track as far as what you're trying to put together for us.
>
> What's your timeline for getting this in production?
>
> --
> To answer this request for more information, you can either reply to
> this email or enter your reply at the following page:
> https://answers.launchpad.net/schooltool/+question/231867
>
> You received this question notification because you asked the question.
>

OK, it shouldn't be a problem for us to write a script for you, especially if you can assign SECTION id's used in your grade export. That is, you'll have your course import sheet, and your section import sheets (1 per term) which will assign ID's to the sections of each course in each term, and then in your new sheet, include the section ID that the grades should correspond to.

If you can do that, we should have no problem writing the script in a day or two. Obviously, the quicker you can get this ready, the bigger our margin for error.

Make sense?

What exactly do you want me to send to you? I'll definitely be able to
assign section IDs, etc. But at this point my data is per student per
course; I still need to compile it into sections that include all of the
students that took the course at a given time. (That is, I have a
table/spreadsheet of all marks for all courses, with student IDs and
course/section IDs.)
If I understand you correctly, would it be enough to send you data for two
or three sections, so that you can test your script? Are you expecting a
basic csv file or spreadsheet, or something with more structure than that?
I just want to make sure I'm sending it in the format you're expecting.
Thanks!

On Mon, Jul 8, 2013 at 10:36 PM, Tom Hoffman <
<email address hidden>> wrote:

> Your question #231867 on SchoolTool changed:
> https://answers.launchpad.net/schooltool/+question/231867
>
> Status: Open => Needs information
>
> Tom Hoffman requested more information:
> OK, it shouldn't be a problem for us to write a script for you,
> especially if you can assign SECTION id's used in your grade export.
> That is, you'll have your course import sheet, and your section import
> sheets (1 per term) which will assign ID's to the sections of each
> course in each term, and then in your new sheet, include the section ID
> that the grades should correspond to.
>
> If you can do that, we should have no problem writing the script in a
> day or two. Obviously, the quicker you can get this ready, the bigger
> our margin for error.
>
> Make sense?
>
> --
> To answer this request for more information, you can either reply to
> this email or enter your reply at the following page:
> https://answers.launchpad.net/schooltool/+question/231867
>
> You received this question notification because you asked the question.
>

If you can set up the standard import/export spreadsheets, and then some additional form that has the scores that needs to go in the report sheets, that would be great.

It isn't particularly important that the score data be in any specific form. Whatever is easy to parse problematically. We just use spreadsheets a lot because it is the only tool you can reasonably expect a secretary to use to manipulate data at all. We won't be turning this directly into a SchoolTool feature, and there is no existing form, so if you wanted to just export the student scores as something like JSON even, that might even be easier. Main point is, don't get hung up on the format, as long as everything is clear for processing by a script, and more structure is probably better than less.

Yes, doing a few test sections first would make sense.

I should have the test sections ready sometime tomorrow. I'll send the
standard import spreadsheet (still working on the section sheets), and then
a spreadsheet of all of the grade data, with each row containing the
following fields:
School Year, Term, (Section) ID, studentID, Grade, PreResit, Resit1,
Resit2, Univ, UnivCA, UnivExam, UnivExpect

In my test, no full names are included, and I listed only a few students
per year per program.
The individual grades to import are for the "assignment" fields Grade
through UnivExpect. Let me know if something about that format should
change.

OK. Looking forward to it!

I'm not sure of the best way to send you attachments with data. I've
changed some of the data for privacy reasons (shortening the field values,
etc). I finally have the spreadsheets to send, though. Let me know, and
I'll send them tomorrow. Thanks!

On Thu, Jul 11, 2013 at 8:56 PM, Tom Hoffman <
<email address hidden>> wrote:

> Your question #231867 on SchoolTool changed:
> https://answers.launchpad.net/schooltool/+question/231867
>
> Tom Hoffman posted a new comment:
> OK. Looking forward to it!
>
> --
> You received this question notification because you asked the question.
>

You can just send them to <email address hidden>, thanks!

This question was expired because it remained in the 'Open' state without activity for the last 15 days.