Trial #38: Parsing and Transforming MidYIS grade export data

Problem:

At the school at which I work, we had no way of importing our baseline MidYIS scores into our MIS and other custom reporting tools. At times this information was simply input by hand into forms in the MIS. I decided to work out how to parse exports so that this was not necessary. I have a partner article on the ALIS baseline system.

Solution:

I will add an appendix with instructions on how I acquired the data but will dive straight into the implementation of how I chose to parse and transform

Once you have your file for example MidYIS Year9 Predictions Feedback.xls you may need to unblock a windows security lock and enable content:

Unblock

Enable Content

Choose the Appropriate Tab e.g. GCSE (9-1) Preds and then select Generate File for MIS Import.

Choose the Appropriate Tab

Generate XML

Interpreting the file with PowerShell

You can use the .NET XML type to get a structured object from a valid XML document e.g. If you are working with a huge file you could be more careful and scan through a memory stream for the content of interest.

$import = [XML](Get-Content ".\PredIE9XML_000XXXXX.xml")

If we explore the data we can traverse to a Node containing all of our predicted results.

$import["ResultFile"]["ResultNode"]

A single child Node of this contains all the data associated to a particular pupil in the result node.

$import["ResultFile"]["ResultNode"][0]["Result"]

However, many of the fields have values that I wish to tidy up. Chief amongst this is the subject title which is normalised across another node which must be referenced by a foreign key, the AspectExternalId.

The subjects are stored in the AspectNode and the content of a single subject in the child node Aspect.

["ResultFile"]["AspectNode"]
["ResultFile"]["AspectNode"][0]["Aspect"]

One could find the relevant Aspect using a Where-Object but this is inefficient and will require excessive enumeration.

A more efficient way of doing this is to enumerate the Aspects once and assemble a HashTable table keyed on the primary key.

I take the opportunity while doing this of cleaning the Title too which is prefixed with the text “GCSE 9-1 “ and suffixed with the text “ I Year9 Pred” e.g. “GCSE 9-1 Mathematics I Year9 Pred”. I strip this out with a little regex.

$Name = @{
            Name = "Name"; 
            Expression = { 
                if($_.Name -Match "GCSE 9-1 (?<Subject>[a-zA-Z ]+) I Year9 Pred"){
                    $Matches.Subject
                }
            }
        }

$subjectHash = @{}

$import["ResultFile"]["AspectNode"] | 
    Select-Object -ExpandProperty "Aspect" | 
    Select-Object -Property $Name, ExternalId |
    ForEach-Object {
        $subjectHash.Add($_.ExternalId, $_.Name)
    }

We can now iterate over the pupils’ results forming up the data in a clean and more friendly format.

$subjectFromId = @{
    Name = "Subject";
    Expression = {
        $subjectHash[$_.AspectExternalId]
    }
}

$Forename = @{
    Name = "Forename";
    Expression = {
        $_.Forename.Trim()
    }
}

$Surname = @{
    Name = "Surname";
    Expression = {
        $_.Surname.Trim()
    }
}

$Attainment = @{Name="Attainment"; Expression = {$_."ResultValue"}}

$data = $import["ResultFile"]["ResultNode"] | 
    Select-Object -ExpandProperty "Result" |
    Select-Object -Property DOB, $Forename, $Surname, Gender, $subjectFromId, $Attainment 

The data can now be exported onto a JSON file or flattened into a csv or even back into XML. However, before I export I needed to add a foreign key to match the pupils with our MIS and custom reporting system.

If I cover this in another article I shall post a link here.

Acquire Data from CEM

Head over to Cambridge University Press & Assessment’s Centre for Evaluation Monitoring and log into the MidYIS portal using your school’s credentials.

Drill down into the Reports -> Data and Reports page

Navigate to Original Excel/PDF Reports

Drill down into Original Excel/PDF Reports and download your file.

Download

For example I use the Independent Schools’ Predictions and Chances Graphs (including MIS import files) you might use a similar one.