Bulk loading EPAFs using windEPAF

Despite what your Ellucian/SunGard Banner sales rep might have told you, bulk importing EPAFs from Excel is no easy process. Going straight from a spreadsheet into Banner is almost impossible to do. Actually multiple spreadsheets are involved because a single EPAF translates into dozens of records in three required tables (NOBTRAN, NORTRAN and NORROUT) and three optional tables (NORTERN, NORTLBD and NORCMNT). And the transaction number that ties all of these entries into a single record must be carefully maintained across all spreadsheets and must not conflict with EPAF users in INB and self-service that both write to these tables as well. And Banner allows users to define an infinite number of EPAF categories, each with its own definition of tables and columns it will write to and which fields are editable or defaulted in. It all adds up to a perfect configuration nightmare.

But you probably already knew that importing EPAFs from Excel was error prone and enormously difficult, otherwise you wouldn't be here reading this. So when my users told me they wanted to be able to import EPAFs from a single row on a spreadsheet, I knew I had an interesting challenge ahead. The first problem to address was the configuration issue. Rather than trying to configure a myriad of EPAF categories, we essentially eliminate configuration by using a reference EPAF as a template. So if you need faculty EPAFs with 1 informational, 1 apply and 2 approval records in NORROUT; 1 record in NORTERN, and up to 2 entries in NORTLBD then you just use an existing EPAF with those characteristics (or add a new one). The EPAFs you import will be just like your reference EPAF.

The next problem was how to look up values. When you are entering values on the spreadsheet, you need to know things like:

• are they already employees or do they need a current hire date?
• is the new record you are entering is their primary or secondary job?
• do they already have this job or is it a new job for them?

Making the users look up the value in Banner as they fill out the spreadsheet didn't feel like much of an improvement. So instead, users can simply enter special values when filling out the spreadsheet and the values will be looked up during import. Entering CV__ for any of the NORTRAN values will look up that employee's Current Values from either PEBEMPL, NBRBJOB or NBRJOBS. And if a value isn't found (the person is not a current employee or does not currently have that posn/suffix) then it tries to make a common sense decision about what the value should be. For instance if you enter __CV for pebempl_current_hire_date and the person is not currently an employee, then windEPAF will use the EPAF's effective date for the current hire date.

Users can also use DV__ to look up the Default Value that was set when the EPAF category was configured. The __CV and DV__ look-ups are only available in the NORTRAN section. A third translation, __RV (Reference Value) will look up the corresponding value in the reference EPAF.

WindEPAF has three executable programs that can either be run from the command line or called as a jobsub job. The goal is to import a spreadsheet and that is done using epaf_import.py. But in order to be able to import it, the spreadsheet has to be carefully formatted and must match the fields in the reference EPAF. Building the spreadsheets with the correct columns and formatting is the job of the other two programs.

One program, epaf_export.py creates a template where all of the editable import fields are empty. The export app simply takes the reference EPAF and the number of import records to create and generates a file like the one shown here.

The first thing you will notice is that records are in columns instead of rows more common for Excel. This is because the header names are very long and we wanted a complete record to be visible on one screen. If your data is already in rows, Excel has a nice transpose function that will convert between rows and columns. The first column contains all of the headers. Which columns you have and how many records you have in NORTERN and NORTLBD are determined by the reference EPAF. If your reference EPAF does not have entries for NORTERN or NORTLBD your exported spreadsheet will not have them either.

The second column are the details of your reference EPAF. It is simply for your information and will be ignored on import. The third column is titled Import 1 and is where users will begin entering data. Any fields with purple text are locked and should not be edited by the user. Any records that do not have the id, posn, suff and effective_date fields filled out will be skipped during import. For a complete breakdown of the sections and fields, see the comments in files/sample.xls.

The last executable, epaf_dump.py creates pre-populated spreadsheets. It will be covered in a later article.