Perl Success Story: Managing Student Affairs with Perl at the University of Papua New Guinea

Perl, through Project Breeze, has made it possible for the School of Natural and Physical Sciences (SNPS) at the University of Papua New Guinea, to accommodate the new academic calendar while handling the afairs of its students with far more accuracy and efficiency. Here is the story of Project Breeze, sent to me by Alfred Vahau, Associate Dean of Students in the School of Natural and Physical Sciences (SNPS) at the University of Papua New Guinea from 1999 - 2001. He is currently the Perl advocate and leader of Project Breeze.

Managing Student Affairs with Perl at the University of Papua New Guinea

In 1999, the University of Papua New Guinea (UPNG) restructured its academic year from two semesters per year to three trimesters, with a week between each term. The change in the academic calendar led the School of Natural and Physical Sciences (SNPS) to re-evaluate the data entry process for student records, which was done largely by hand, using Excel.

Project Breeze

The Breeze project begun in SNPS in March 2001 was created to explore alternatives to the manual data entry process. The initial goal of Project Breeze was to find a way to populate Excel cells automatically, thereby ending years of manual data entry. The approach was to look at ways that Unix might be used, without excluding the use of Excel or other Windows applications.

The Excel problem

The University of Papua New Guinea's computing environment is centered around the Windows operating system. Excel and Word are the de facto standard on campus�nearly everyone uses them on a regular basis. However, when it came to data entry, no one knew enough about Excel to take advantage of all of its features. For most users, manual entry of data into cells and using cut, copy, and paste is what Excel is all about, which is fine for small quantities of data. In SNPS, we have to populate about 6000 Excel cells for processing information about 500 students, three times a year. The need to automate the process was evident when I assumed responsibility for managing student affairs in 1999, the year that UPNG introduced the new academic calendar.

From the beginning, I was very frustrated with our manual data entry process. I knew that Unix solutions existed, but was aware that there would be resistance to a Unix-only solution, as people at UPNG were more comfortable using Excel and Word. I began to search for a unified language capable of processing and producing Excel files. My search ended when I got a copy of Unix Power Tools, 2nd edition, and was first introduced to Perl.

An Outline of The Problem

As part of my duties in student administration in SNPS, at the end of each term, I asked that each discipline present the results of the courses taught in that area. These were submitted in Excel files in the format shown in Format 1:

ID Fname Sname CourseNo Grade

Format 1

ID is the student identification number, Fname is the student's first name, Sname is the student surname, CourseNo is the number in the form xx.nnnn which identifies a course uniquely in each discipline, and the Grade is the letter grade given to the student based on her performance. Each discipline provided two listings for each course - alphabetic and merit order.

From the results of each course (SNPS offered an average of 45 courses per term), I wanted a file constructed in the format shown in Format 2.

ID No. Fname Sname C1 G1 C2 G2 C3 G3 C4 G4 GPA

Format 2

This second Excel file contains the names of all 500 students along with their identification numbers, the four course numbers C1, C2, C3, C4, the respective grades for each course, G1, G2, G3, G4, and the grade point average (GPA). Historically, this file was constructed manually by administrative staff of SNPS with information derived from the alphabetical listing of each discipline results for each course. In theory, that required is populating 6000 Excel cells by hand, using information from the alpha listing of the course results. This translated to two to five days of manual work to ensure accuracy of the results, as the future of any student was clearly in the balance. The new structure introduced an additional constraint: the results had to be determined quickly, prior to commencement of a new term.

Before the change to the new academic calendar, there had been a break of about a month, so the lengthy manual data entry process was rarely an issue. However, with the new academic calendar, it became increasingly clear that the manual process, in addition to being immensely inefficient, could no longer meet our needs for timely data.

The Perl Approach

To produce the information in Excel spreadsheet in Format 2 for all 500 students of SNPS, the Perl script reads two input files. These are student and grades files. The student file contains the names of students along with their ID numbers. The data is stored in an Excel format, then converted into text for Perl to process. The grades file is constructed from the course results from each of six disciplines and holds the id, course number, and the grade for each student. Thus, the Excel file (Format 2) derives its information from six separate Excel files.

Each of the Excel files is converted into an ASCII format using Takanori Kawai's Spreadsheet::ParseExcel module (available from CPAN.) This essentially bypasses Excel's guided menu on file conversion. The discipline files are then concatenated to produce a master results file called the "grades" file. This is the second input file. In the Perl script, I set up an array of course numbers:

@courses = qw(xx.nnnn yy.nnnn zz.nnnn);

and a hash for the courses:

%courses = (
'xx1'    =>  'xx.nnnn',
'yy1'  =>  'yy.nnnn',
'zz1 '     =>  'zz.nnnn');

The xx, yy and zz are discipline codes and nnnn are course numbers in each disciplines. The names are read into a hash keyed in by id numbers:

$name{$id} = $name;

The grades are referenced by both the student identification number and the course number:

$grade{$id, $courseno} = $grade;

Each Excel row is then built by using the Perl's push and foreach functions. First the name and id are inserted into the result array:

@result = ($id, $name{$id});

Then the course number and the grades are appended to the end of the row:

push (@result, $course{$courseid}, $grade{$gradeid});

Computing the Grade Point Average

Once all the information for each student has been entered into the array, the grades are then passed to a subroutine, which determines the weighting of each grade and then computes the grade point average for each student. This figure is then appended at the end of the @result array:

push (@result, $gpa);

Producing the Excel file

The resulting row is written to an Excel file, which is created using John McNamara's Spreadsheet::WriteExcel module (available from CPAN.)

Thus the result for each 500 students of SNPS is made available as an Excel file, which can be further, manipulated to produce any desired output.

How long does it take?

Breeze is so named because it makes data entry into Excel a breeze. The only requirement is that data must exist elsewhere in some format. The current program is written using Perl 5.6.1. It runs on Linux Red Hat 7.1 Pentium III PC at 1 GHz and 30 GB hard disk with 128 MB ram. The program returns all of the information in Format 2 in under five minutes. In contrast, the manual processing returned the same information after several hours of data entry.

Benefit to staff and students

There is a constraint associated with the term transitions of the new academic calendar. As the term breaks are short (1 week), the academic staff in SNPS is under pressure to finalize the results as quickly as possible. By creating a system capable of rapidly providing accurate results, the time saved becomes an additional day for staff to complete their grading. It also means that the students of SNPS can know their status well in advance of the beginning of a new term. Perl, through Project Breeze, has made it possible for SNPS to accommodate the new academic calendar, while handling the affairs of its students with far more accuracy and efficiency.

Further Goals

Breeze is now being developed to include a database that will provide information on any SNPS student from the time the student is admitted until graduation day. A generic filter is also being developed to extract data from Excel files directly from each discipline.

The Future

It is the Breeze mission statement to, �Harness the Power Tools of Contemporary IT to manage student affairs in SNPS in the new millenium.� While UPNG remains a Windows stronghold, the Breeze project has introduced Perl to the UPNG community. Although Breeze has not yet been introduced to the other schools at UPNG, the object-oriented version of Breeze soon to be developed in Perl will address university-wide problems with student administration.

-- Alfred Vahau

Alfred Vahau is a lecturer in Geophysics in the School of Natural and Physical Sciences in the University of Papua New Guinea. He holds an M. Sc. degree in theoretical Physics from the University of Sussex. He was a research student in seismology in the Research School of Earth Sciences, ANU, from 1991 to 1995. His thesis Seismic Tomography Across the Edge of the Australian Shield has remained unpublished.

Alfred was SNPS's Associate Dean of Students from 1999 - 2001. He is the Perl advocate and leader of Project Breeze in the School of Natural and Physical Sciences at the University of Papua New Guinea.

To learn how large and small companies are using Perl to meet their goals, check out Perl Success Stories.

If you have a Perl success story of your own that you'd like to share, please let me know. You can reach me at: betsy@oreilly.com