Powershell Kerboodle Reconcilliation

[pcstru]

Oxford University Press provides the most abysmally awful user management features in their Kerboodle product and they obviously want to squeeze as much profit from that little baby because despite complaints, they have failed to address their issues. This script looks to avoid the situation where the entire import of students from a csv fails if any one student is already present on the system. If you find this script useful, please do not send me thanks, money or cake but DO call up OUP and spend a few minutes complaining about their inadequate management tools and persuade your schools to spend their money on better products.

 

# ---------------------------------------------------------------------------
# Script : KerboodleReconcile.ps1
# Author : P.C.Structures
# Date   : Sept 2016
# Notes  : Reconcile MIS and Kerboodle student data and generate a file which
#          the idiotic, imbecillic, utterly appauling Oxford University Press
#          were too flumping moronic to provide.
#
#          Input files are CSV and expected to contain header columns named 
#          as:
#
#          Surname	
#          First name	
#          Admission Number	
#          Year	
#          Email address (optional)
#
# Process: 1. Produce export from kerboodle.
#          2. Produce export from MIS
#          3. Run this.
#          4. Upload OUT_File to kerboodle.    
# ---------------------------------------------------------------------------

$MIS_File = "c:\tmp\bromcomkerboodle.csv"
$KER_File = "C:\tmp\Kerboodle\KerbExport.csv"
$OUT_file = "c:\tmp\KB_Upload.csv"



#----------------------------------------------------------------------------
# Generic Multi level hash builder. Pass hash and array of node keys. Leaf
# nodes are created as simple markers (integer = 1). All array elements are
# created as hash keys. 
#----------------------------------------------------------------------------
function AddToHash {
   
   Param ($TheHash, $Keys, $level=0)
   
   $level=$level+1
       
   $key=$Keys[0]
   $sle=$Keys.length - 1
   
   if( $key ) {
       
       if( ! $TheHash.ContainsKey($key) ) {        
           if ($Keys.length -gt 1) {
              $tmpHash = @{}
              $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
              $Thehash.Add($key,$tmpHash)
           } else {
              $TheHash.Add($key,1)
           }
       } else {
           if ($Keys.length -gt 1) {        
              $tmpHash = $TheHash.Get_Item($key)                      
              $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
           } else {
              $TheHash.Set_Item($Key,$TheHash.Get_Item($key) + 1 )
           }
       }
       
   }
   
   $TheHash
              
}
#----------------------------------------------------------------------------





# ===========================================================================
# Main 
# ===========================================================================
$MIS_Hash = @{}

$MIS_Data = import-csv $MIS_File
$KER_Data = import-csv $KER_File

$OutArr = @()

# Build a hash of students in Kerboodle export with Admission number as key
foreach( $Student in $KER_Data ) {
   $MIS_Hash = AddToHash -TheHash $MIS_Hash -Keys @($Student.("Admission Number"))
}

# Check if student is already present in kerboodle, if not we want it for the out
# file
foreach( $Student in $MIS_Data ) {
  
  if( ! $MIS_Hash.ContainsKey( $Student.("Admission Number") ) ) {
       $OutArr += $Student
  }
  
}

# Spit out the CSV
$OutArr | export-csv $OUT_File -notype

# ===========================================================================

[kevin_lane]

yea there system is dreadful I don't get why they cant just integrate with SIMS like normal companies

[pcstru]

yea there system is dreadful I don't get why they cant just integrate with SIMS like normal companies

That wouldn't be much help to people like us who use a different MIS.

[pcstru]

It is that time of year again and this thread might just be useful to anyone grappling with Kerboodle's utterly abysmal bulk user management.

[pcstru]

Sorry for a second bump. Your export file will need to contain an additional column after the email address with the heading : "Username (optional)". The code itself is functionally unchanged but I've updated the comments to reflect the new requirement. Notable that OUP chose to do work on the upload but not actually address the bigger issue - why anyone would pay for this rubbish is beyond me.

 

# ---------------------------------------------------------------------------
# Script : KerboodleReconcile.ps1
# Author : P.C.Structures
# Date   : Sept 2016 (Updated Sept 2017)
# Notes  : Reconcile MIS and Kerboodle student data and generate a file which
#          the idiotic, imbecillic, utterly appauling Oxford University Press
#          were too flumping moronic to provide.
#
#          Input files are CSV and expected to contain header columns named 
#          as:
#
#          Surname	
#          First name	
#          Admission Number	
#          Year	
#          Email address (optional)
#          Username (optional)
#
# Process: 1. Produce export from kerboodle.
#          2. Produce export from MIS
#          3. Run this.
#          4. Upload OUT_File to kerboodle.    
# ---------------------------------------------------------------------------

$MIS_File = "c:\tmp\bromcomkerboodle.csv"
$KER_File = "C:\tmp\KerbExport.csv"
$OUT_file = "c:\tmp\KB_Upload.csv"



#----------------------------------------------------------------------------
# Generic Multi level hash builder. Pass hash and array of node keys. Leaf
# nodes are created as simple markers (integer = 1). All array elements are
# created as hash keys. 
#----------------------------------------------------------------------------
function AddToHash {
   
   Param ($TheHash, $Keys, $level=0)
   
   $level=$level+1
       
   $key=$Keys[0]
   $sle=$Keys.length - 1
   
   if( $key ) {
       
       if( ! $TheHash.ContainsKey($key) ) {        
           if ($Keys.length -gt 1) {
              $tmpHash = @{}
              $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
              $Thehash.Add($key,$tmpHash)
           } else {
              $TheHash.Add($key,1)
           }
       } else {
           if ($Keys.length -gt 1) {        
              $tmpHash = $TheHash.Get_Item($key)                      
              $tmpHash = AddToHash -TheHash $tmpHash -Keys $Keys[1..$sle] -level $level
           } else {
              $TheHash.Set_Item($Key,$TheHash.Get_Item($key) + 1 )
           }
       }
       
   }
   
   $TheHash
              
}
#----------------------------------------------------------------------------





# ===========================================================================
# Main 
# ===========================================================================
$MIS_Hash = @{}

$MIS_Data = import-csv $MIS_File
$KER_Data = import-csv $KER_File

$OutArr = @()

# Build a hash of students in Kerboodle export with Admission number as key
foreach( $Student in $KER_Data ) {
   $MIS_Hash = AddToHash -TheHash $MIS_Hash -Keys @($Student.("Admission Number"))
}

# Check if student is already present in kerboodle, if not we want it for the out
# file
foreach( $Student in $MIS_Data ) {
  
  if( ! $MIS_Hash.ContainsKey( $Student.("Admission Number") ) ) {
       $OutArr += $Student
  }
  
}

# Spit out the CSV
$OutArr | export-csv $OUT_File -notype

# ===========================================================================