Banner Student to Millennium patron record conversion
Banner Student data is supplied in a file containing one line per record (person).
The data is in a fixed format as follows [field name(field length)]:
dart_id(6), name(32), SSN(9), hinman_box(4), local_phone(13), home_street1(30), home_street2(30), home_street3(30), home_city(20), home_state(2), home_zip(10), country(28), home_phone(13), local_street1(30), local_street2(30), local_street3(30), local_city(20), local_state(2), local_zip(10), major(4), dplan(1), class_year(2), grad_term(3), stype(1), statcat(1), ptype(3), descript(1)
The SSN field only contains a Social Security Number if the dart_id is less than '85000'. Otherwise it contains the dart_id with the check digit mapped to a two-digit number (A=01, B=02, etc) and in either case it goes into the Alternate ID field in Innopac.
These are defaults which may be reset by data either read from the record or set during the record processing.
Set the home library to 'dart'.
Set the ptype to '000'.
Set the descript to 'z'.
Set the stat_cat to 'u'.
Set the dept/affil (pcode3) to '000'.
Set the pmessage to ' ' (space).
Valid dart_id's are looked up in the DND to get an email address for notices.
Ptype and stat_cat are set from the values in the record.
If the descript is blank, set it to '-' otherwise set it to the value in the record.
If the class_year is a 2-digit number this is either an undergrad or a Tuckie. If the descript is 't' then set the classification field to 'TU' (Tuck) otherwise set it to 'UG'. If the class_year is not a 2-digit number then the code maps to an entry on the class_codes table (which is defined in the filter).
If the class year is 'DM' (Med student) construct a class_year from the first 2 digits of the grad_term field and turn it into a 4-digit year.
Set the dept_affil field by looking up the major code in the majors table (in the filter).
If the dplan is 'L' and the ptype is '001' (grad student on leave term) set the mblock to '-'.
If the dplan is 'A', 'D' or 'N' (admin withdrawal, deceased, not enrolled) set the mblock to 'a', 'd' or 'n' respectively.
Otherwise set the mblock to '-' (not blocked).
Set the expiration date to either the date they expect to get their degree (if they are an undergrad and if they have a valid grad_term set) or 1 year from 'today', whichever is earliest.
Create a local address from the Hinman box if one was supplied.
If the student is not in residence this term use the home address and telephone, otherwise use the local address and telephone.
If this is a grad student the major department info doesn't determine dept_affil so set that to '000'.
If there's no dart_id, set pcode1 (descript) to 'q'.
If there's no ptype, set the ptype to '255'.
If there's no descript, set the descript to '-'.
If there's no stat_cat, set the stat_cat to '-'.
If there's no dept_affil, set the dept_affil to '000'
Records with no name are skipped.
Records whose stype is 'S' (special) or 'X' (transient) are skipped.
Records whose dart_id is empty or invalid are skipped.
BANNER Export format
The fields are fixed length as follows:
START END SIZE DESCRIPTION Format/Comments 1 6 6 Dartmouth ID 7 38 32 Name Last, First Middle Suffix 39 47 9 SSN (if available) 48 51 4 Hinman Box 52 64 13 Local phone (xxx)xxx-xxxx 65 94 30 Home address street line 1 95 124 30 street line 2 125 154 30 street line 3 155 174 20 city 175 176 2 state code 177 186 10 zip 187 214 28 country (if not USA) 215 227 13 Home phone (xxx)xxx-xxxx 228 257 30 Local address street line 1 258 287 30 street line 2 288 317 30 street line 3 318 337 20 city 338 339 2 state code 340 349 10 zip 350 353 4 Primary major (if available) 354 354 1 Dplan for current term (for undergrads) 355 356 2 Class year (see below) 357 359 3 Expected grad term YYT (term values below) 360 360 1 Banner student type (list below) 361 361 1 stat-cat (derivation below) 362 364 3 ptype (derivation below) 365 365 1 descript (derivation below)
Class year codes:
YR DESCRIPTION 00 Class of 2000 01 Class of 2001 02 Class of 2002 87 Class of 1987 91 Class of 1991 92 Class of 1992 94 Class of 1994 95 Class of 1995 96 Class of 1996 97 Class of 1997 98 Class of 1998 99 Class of 1999 DM Dartmouth Medical School EC ECS Program (GR) GR College of Arts & Science-Gr GS Graduate Special (GR) LS MALS Program (GR) SC Special Community (UG) SD UCSD Exchange (UG) SX Summer Special (UG) TH Thayer School of Engineering TU Amos Tuck School of Business UG College of Arts & Science-UG
Expected graduation terms:
T = 1 winter 3 spring 6 summer 9 fall
Student type codes:
S DESCRIPTION 0 Undeclared C Continuing G New Graduate Student N New First Time Freshman P New Professional Student S Special T Transfer X Transient
stat-cat field definition:
if student is dead 'x' else if student level is 'UG' 'u' else 'g'
ptype field definition:
if student is dead '000' else if student type is 'S' '000' else if student level is 'UG' '000' else '001'
descript field definition:
if student is dead 'd' else if student type is 'X' 'v' else if student level is 'UG' 'x' 'GR' 'x' 'DM' 'm' 'TU' 't' 'TH' 'e' else blank
Code Description - Finished, but not graduated ? To be determined A Administratively Withdrawn D Deceased L Leave N Not Enrolled NS Not Coming O On Off-Campus program O AF FREN LSA Arles O BA SPAN FS Baires O BF FREN LSA Blois O BG GERM FS Berlin O BS SPAN LSA Barcelona O CB BIOL FS O CP PORT LSA Curitiba O DE ENGL FS Dublin O EP PHIL FS Edinburgh O ER REL FS Edinburgh O FA ARTH FS Florence O FZ ASIA FS Fez O GC CLAS FS Greece O GM GEOG FS Mexico O GP GEOG FS Prague O GS SPAN LSA Granada O GW ENGL FS Glasgow O HE ENVS FS Harare O JK ASIA FS Kanazawa O LD DRAM FS London O LE ENGL FS London O LF FREN LSA Lyon O LG GOVT FS London O LH HIST FS London O LM MUS FS London O LR RUSS FS Leningrad O MD SPAN FS Madrid O MG GERM LSA Mainz O MR RUSS FS Moscow O MS SPAN LSA Morelia O NE ENGS FS Nairobi O NG GERM LSA Berlin O PA AMEL FS Beijing O PI ITAL FS Padua O PM SPAN LSA Puebla O PR RUSS FS St. Petersburg O PS SPAN FS Puebla O QS SPAN LSA Queretaro O RC CLAS FS Italy O RI ITAL FS Rome O RP PORT LSA Rio de Janeiro O SI ITAL LSA Siena O SS SPAN FS Salamanca O TA ASTR FS Tucson O TD ENGL FS St. Augustine O TF FREN FS Toulouse O TK AMEL FS Tokyo O UP PORT FS Curitiba O WE EARS FS O WG GOVT FS Washington DC O WZ EARS FS West US & New Zealand P Provisional R Resident T Transfer term elsewhere X On College exchange X AM Amherst College 12-coll. exch X BO Bowdoin College 12-coll. exch X CA Univ. of Calif. at San Diego X CO Connecticut College 12-coll. X GE German Exchange (Fed. G.Am Cl) X HU Hungary Exchange (Karl MarX U) X JA Japanese Exchange (Keio Univ.) X KA Kazan State Univ. X KB Keble Exchange (Oxford, Eng.) X MA Univ. of Mass. (Beijing,China) X MC McGill Univ. Montreal, Canada X MO Morehouse College (Atlanta,GA) X MT Mt. Holyoke Coll 12-coll. exch X MY Mystic Seaport Exchange X NT National Theatre Inst 12-coll. X SM Smith College 12-coll. exch X SP Spelman College (Atlanta, GA) X ST Stanford Exchange X TR Trinity College 12-coll. exch X VA Vassar College 12-coll. exch X WH Wheaton College 12-coll. exch X WI Williams College 12-coll. exch X WL Wellesley College 12-coll. exc X WS Wesleyan University 12-coll.exc
Notes pertaining to the conversion from SIS to Banner (back in 1998)
1. There's no longer a classification field. This was the field that held values like FR (Freshman), GE (Thayer), GEG (Thayer-Special), GM1, (Med-1YR), SC (Spec-Comm).
This field was used to set the Classific, ptype, descript and stat_cat fields in Millennium.
For ptype my program was only setting the values 000 (undergrad) or 001 (grad student), so they're the only values relevant to this discussion.
Banner will output the ptype, descript and stat_cat fields all pre-set based on the above criteria (which matches what my program was doing):
The Classific variable length field will have slightly different values because the data is less-specific from Banner. These are the codes I'll be loading:
DM MED EC ECS Program G GRAD-A&S GS GRAD-A&S-SPEC LS MALS SC SPEC-COMM SD UG-UCSD SX GRAD-A&S-SPEC TH THAYER TU TUCK UG UNDERGRAD
2. Right now, a DPlan is guaranteed to be set in Banner only for undergrads. The various graduate schools are talking about using it, but haven't decided yet. In the filter program the DPlan was used to determine whether the student was in residence or not. If they were not in residence, then their record was loaded with a mblock set to block privileges. Their in residence status also told us which address and phone to list as ADDRESS1 and PHONE1.
As I read the program, it looks like we defaulted to granting privileges for grad students if there was no DPlan assigned in SIS. I have kept the same logic here.
3. Social Security numbers are optional in Banner records. DartID is the only mandatory unique identifier. I have set up the program to reject any record that does not have a DartID. If the SSN is present in the Banner record, I'll load it into Innopac. But it won't be an error if it is missing.