|
Last updated: Apr 17 2012
|
|
|
|
How do I get my course equivalencies which are currently in my system (Banner, DARS, RedLantern, PeopleSoft, etc.) into TES?
|
| |
You export your equivalencies (EQs) from your system to a flat file, the file specification is shown below.
You email or ftp the file to us.
FTP instructions provided upon request.
*Note: Initial imports are $600. Subsequent imports are $150/hr.
Terminology
Courses from incoming students (feeder schools, etc.) are referred to as Send Courses.
The related institutions are referred to as Send Institutions.
Courses at your institution are referred to as Receive Courses.
Your institution is referred to as the Receive Institution.
You are referred to as the Provider.
Pre-import Process
The pre-import process includes matching institutions and matching courses.
The provider sends a file according to the spec shown below.
CSI imports the file into SQL (not into TES).
Let’s call this file import_file.
Assuming the import_file is good, proceed to next step, if not, send back comments to provider.
Matching Institutions
CSI builds a list (call it inst_list) of all institutions referenced in import_file.
Based on OPEID, match institutions in inst_list to CSO/TES institution internal identifiers.
For all institutions not yet identified in inst_list, try to match on name, city and state.
For all remaining inst_list insts, scan for obvious matches on name, city and state where inst_list names are truncated/abbreviated inst names.
For all institutions not yet identified in inst_list, send back list to provider (call it inst_list_left_overs).
Provider matches insts to CSO/TES institutions using the CSO interface providing CSO/TES inst ids (instructions provided at that time).
Provider sends back the now identified inst_list_left_overs.
Inst_list is updated with inst_list_left_over ids.
Any exceptions (unidentified insts) will not be imported.
The import_file is updated with the inst ids from inst_list.
Matching Courses
All receive course codes are compared to all course codes in CSO/TES for provider’s institution.
Those that have no match are sent to provider for review.
Call this list receive_course_exceptions.
Some courses will be unmatchable because they are no longer offered and provider doesn’t actually want them imported.
(They are in the import_file because the file is generated automatically by the provider and not scrutinized as is inevitable in this process).
Some courses will be typos. Provider corrects these.
Some courses will be omitted from catalogs and so provider can decide whether to include or not include them (as user added courses).
Some courses will be general elective courses like BIOxxx or ENGxxx or GendEdxxx or Mus Elect xxx, etc..
If their importation is desired, such courses are created as user added courses. (See bottom of page).
Receive_course_exceptions list is sent back to CSI with instructions.
Provider creates users added courses as needed.
A pre-import script is run on import_file.
It matches courses referenced in import_file to CSO/TES courses.
Exception counts are noted and reviewed if necessary.
Import Process
The import process usually occurs in a flush and fill manner.
Provider is reminded/warned.
When provider gives the green light, current EQ data is backed up in an immediately programmer accessible live side table.
Providers current EQs are deleted.
Import script is run on the pre-import, groomed, import_file.
The import is complete.
The specification:
The specification is a .txt file (vertical pipe "|" delimited) with column names in the first row.
Non quoted text.
Name the file "tblABCD_yearmonthday.txt" (Ex. tblUCSD_20101118.txt)
with tbl for table, followed by the school acronym, "_", year, month, day and text file extension file type of ".txt".
Here's the column list and which will be described in a little more detail below.
All columns are required to be present in the file.
Columns with the word NULL next to them may be NULL (i.e. empty).
InstitutionCodeType
SendInstitutionCode
SendInstitutionName
SendInstitutionCity
SendInstitutionState
SendInstitutionCountry
EQValidDateStart NULL
EQValidDateEnd NULL
SendCourseCode1
SendCourseCode2 NULL
SendCourseCode3 NULL
SendCourseCode4 NULL
SendCourseCode5 NULL
SendCourseCode6 NULL
SendCourseCode7 NULL
SendCourseCode8 NULL
SendCourseCode9 NULL
SendCourseCode10 NULL
ReceiveInstitutionCode
ReceiveInstitutionName
ReceiveCourseCode1
ReceiveCourseCode2 NULL
ReceiveCourseCode3 NULL
ReceiveCourseCode4 NULL
ReceiveCourseCode5 NULL
ReceiveCourseCode6 NULL
ReceiveCourseCode7 NULL
ReceiveCourseCode8 NULL
ReceiveCourseCode9 NULL
ReceiveCourseCode10 NULL
CommentsPrivate NULL
CommentsPublic NULL
HideFlag
Build an empty table in SQL using the code here.
(You fill it from your system, export as txt, and send us the txt file).
Build an empty table in Excel using the code here.
(You fill it from your system, export as txt, and send us the txt file).
InstitutionCodeType: This field describes the type of identifier used.
More than one institution code type may not be used in an import (i.e. every record in your file must have the same institution type code).
This field must be populated with one of the following values: (no quotes)
"UNITID" (i.e. IPEDS UnitID)
"OPEID"
"CEEB"
"ACT"
OPEIDs look like FICE codes but with two trailing zeroes.
OPEID codes or IPEDS UNITIDs are heavily preferred, saving time and money because they will match up the best with the least number of exceptions.
SendInstitutionCode: Whichever identifier you chose for InstitutionCodeType, you now identify all your send institutions with that coding scheme and input those IDs in this field.
EQValidDateStart and EQValidDateEnd: Filling these fields is optional.
You can populate the EQValidDateStart and EQValidDateEnd fields if you want your EQs to be placed in the appropriate date range.
Courses will be matched to catalog editions relative to the appropriate date range.
Use mm/dd/yyyy format.
For open ended EQs please put a date of 01/01/2099.
Date ranges must be of a valid date format meaning inclusively between '1/1/1753' and '12/31/9999'.
HideFlag: This field must be populated with the following value pairs: (no quotes)
"True" or "False"
"1" or "0"
The HideFlag field is relates to your Public View. If an EQ HideFlag is set to "1" or "True" then that EQ will not show in your Public View.
You can view more info on the Public View here.
Let's plot some points.
Ex. 1. If you have BIO 101 from another school coming in to your BIOL 1 course, that would be:
| RecordNumber |
SendCourseCode1 |
SendCourseCode2 |
ReceiveCourseCode1 |
ReceiveCourseCode2 |
| 1 |
BIO 101 |
|
BIOL 1 |
|
The other send courses and receive courses for that record/line will be blank.
It takes one record in the import/export to display this relationship.
Ex. 2. In the case where there is a group of courses like send courses BIO 101 and BIO 101L transferring to your BIOL 1 course only, that would be:
| RecordNumber |
SendCourseCode1 |
SendCourseCode2 |
ReceiveCourseCode1 |
ReceiveCourseCode2 |
| 1 |
BIO 101 |
BIO 101L |
BIOL 1 |
|
The other send courses and receive courses for that record/line will be blank including "ReceiveCourseCode2".
It takes one record in the import/export to display this relationship.
Ex. 3. In the case where there is a one course like send course BIO 101 transferring to your BIOL 1 course "OR" your OCHEM 1 course, that would be:
| RecordNumber |
SendCourseCode1 |
SendCourseCode2 |
ReceiveCourseCode1 |
ReceiveCourseCode2 |
| 1 |
BIO 101 |
|
BIOL 1 |
|
| 2 |
BIO 101 |
|
OCHEM 1 |
|
The other send courses and receive courses for that record/line will be blank including "ReceiveCourseCode2".
It takes one record in the import/export to display this relationship.
You would need to create any fictitious courses in TES in advance (generic electives) as "user added" courses. For more info on "user added" courses, watch the "user added courses" movie in our movies section here.
|
| |
