Trial #37: Parsing and Transforming ALIS grade export data

Problem:

At the school at which I work, we had no way of importing our baseline ALIS 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 MidYIS 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, open and save as xlsx. The OpenOfficeSDK used in the ImportExcel module cannot open xls files. You may need to install the module

Install-Module -Name ImportExcel 

Now load the file up in PowerShell.

$import = Open-ExcelPackage -Path ".\AlisExport.xlsx"

The data is flat, many lines per pupil per subject. I find a graph structure more convenient and so populate a HashTable as I iterate over each line, skipping the first 5. I use a complex key for my HashTable using all the information I will need to match the pupils against my MIS.

Although I iterate over Column B, I use each cells row ID to jump to other cells in the row to pick out other data of interest.

$PredictedGradeHash = @{}

$import.Workbook.Worksheets["Sheet1"].Cells["B:B"] | 
    ForEach-Object {
        $row = $_.Start.Row
        if($row -gt 5){
            $PupilName = $_.Value
            $Gender = $import.Workbook.Worksheets["Sheet1"].Cells["C$row"].Value
            $DOB = $import.Workbook.Worksheets["Sheet1"].Cells["D$row"].Value
            $pupilId = $PupilName + "::" + $Gender + "::" + $DOB
            if(-not $PredictedGradeHash.ContainsKey($pupilId )){
                $PredictedGradeHash.Add($pupilId , @{})
            }
            $Subject = $import.Workbook.Worksheets["Sheet1"].Cells["G$row"].Value
            $AlisPredictedGrade = $import.Workbook.Worksheets["Sheet1"].Cells["N$row"].Value
            $PredictedGradeHash[$pupilId].Add($Subject, $AlisPredictedGrade)
        }
    }

You may now export this or process further to match your pupils to another 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 Alis portal using your school’s credentials.

Drill down into the Reports -> Predictive Data page

Navigate to Original Excel/PDF Reports

Look for Predictions - Spreadsheet

Download