Uploading CSV files containing student data to various companies

[David44]

What do you do with requests to create and upload CSV files of student (and sometimes staff) data to various companies?

 

This is usually so that students can log in to the companies websites.

 

We are getting so many of these kind of requests recently, they are all going through the GDPR checks and we are happy that the data we are uploading is proportionate to the purpose of the website but each website invariably makes it very easy to upload all student data but then makes it difficult or least very time consuming to make changes to it or remove it.

 

For example, we upload data to My Maths as a CSV file containing data for 1500+ students. The CSV file looks something like this...

 

"Class","Forename","Surname"

"Ma/123","Shaun","Wallace"

"Ma/999","Mark","Labbett"

 

Imagine that but with 1500+ lines of data. Pretty easy to create the CSV file, we have a script that pulls out the correct fields from SIMS for each company. The problems come when a student changes their name or Maths class. As you can see from the data that My Maths expects, there is no unique identifier included in the data. If I upload a

new file containing...

 

"Class","Forename","Surname"

"Ma/123","Sean","Wallace"

 

the system is just going to add a new student called Sean Wallace rather then update the spelling of the existing student Shaun Wallace. The only way to amend this data is to sign in to the My Maths website and use their front end to do it. This is an extremely time consuming process and means the data these companies hold can very quickly end up incorrect and out of date if we don't devote a lot of time to each of them.

 

I have used My Maths as an example here. There are others of course. Kerboodle is another one that expects a CSV file and it actually does use a unique identifier column (we supply the admission number IIRC). However, if you upload a CSV file containing a line with a unique identifier that is already in their system, rather than update the existing record that it has identified, it rejects the whole CSV file.

 

I've just been asked to create a CSV file for Hegaty Maths. This is another one that doesn't use a unique identifier field so it's very easy to upload a file with 1500+ lines of data but it's then not as easy to keep this up to date when names change or students leave.

 

Why can no one come up with a workable CSV system? I want to upload a CSV file (ideally via an API, but I am happy to click browse on a website and do it that way), if the record exists update it with data from the CSV file and if the 'delete record' column in the CSV file is set to true, delete the record. It can't be that hard, can it?

 

I suppose the problem is that these systems are sold to teachers on the strength of the learning resources and not the strength of the user administration, the companies then make a claim that they are GDPR compliant because, at least on paper, we are able to amend and delete the data so that means SLT are happy too but when it comes to the actual practicalities of managing this data, it's a nightmare of different web front ends which all work in different ways.

 

I don't suppose there really is an answer to this but I just wanted to know I am not the only one going through this process.

[dhicks]

I don't suppose there really is an answer to this but I just wanted to know I am not the only one going through this process.

 

Two issues, subtly different: logins and timetable (or "rostering") data. Logins should, ideally, be handled from a central database of some kind - OAuth (easiest), or via SAML, or some kind of system that doesn't involve having a separate username / password for each site. I'm seeing more sites offer GSuite integrations now, with a few offering O365.

 

After a user has logged in, in a school setting you generally need some way of knowing what class / group / etc they are in. GSuite (and O365) can hold this information in Google Classroom (Microsoft Classroom for O365), but I can understand some applications wanting a wider set of data about a pupil. Actually, you could potentially use wildcard matching on usernames to decide which users see which features / exercises / etc, but that probably only works in some cases.

 

Like yourself, I'd be quite happy with a solution that gave us a nice secure HTTPS endpoint (encrypted, plus a unique API key to identify our school) to periodically upload a CSV file to. Then I can have a script run from our MIS server overnight (or maybe even trigger on updates), generate an up-to-date list of users and upload it to the various services as needed. As you point out above, a decent system should support unique identifiers for users and accept amendments to records (name changes, etc).

 

Rather than each site support API access to individual schools (with each individual school making mistakes in setups and so forth and needing support), it makes sense for sites to use trusted services that provide the data from schools in a consistent manner. This might mean integrating directly with the school's MIS, or using a service such as Clever.com (seems to be mostly in the US) or Wonde / Groupcall / etc in the UK. Wonde seems the most impressive I've seen so far from a technical point of view, offering a properly-documented GraphQL-based API.