How do I get my course equivalencies which are currently in my system (Banner, DARwin/u.achieve, Datatel, Jenzabar, PeopleSoft, etc.) into TES?
You first export your equivalencies (EQs) from your system to a text file. Please see the File Specification layout shown under File Specification below.
You then email the file to us.
Summary: Imports will be discussed within the following subjects:
Background – Why we import
Terminology – Labels and phrases that we often use
Pre-import Process Overview – Briefly describe the process leading up to the import
Pre-import Process Steps – Describe each step of the Import process
Import Process – Outlines the actual Import
File Specification – Detail the file type, fields (columns), and layout/format required of the import file
You may need help from your IT department/technical resources for this step
Examples – Show examples of the data model
Data Checklist – Check off each item as you go through the process to guide you through the required steps
For CollegeSource customers interested in importing equivalencies into TES, or even into Transferology, we first import into CollegeSource's TES in order to leverage existing code and processes. Your ultimate goal or final destination may be to just get EQs into TES or it may be to get your EQs all the way into to Transferology. Either way, we want to complete this TES import.
CollegeSource has maintained a library of college catalogs since 1971. This collection is similar to a physical library housing college catalogs sitting on a shelf. Multiple institutions, multiple editions (2000-2001, 2001-2002, etc.), multiple titles (undergrad, grad, summer bulletin, etc.) exist in the database. We imported the courses as published in your course catalogs (pdf, printed hard copy, html, etc.). We store those courses in a database and they are related directly to the catalog editions (ex. 2012-2013 ACME Community College) that we have processed. The courses have all the appropriate information they included at the time they were active/existed/current/published.
For example, this is a sample (from a mock institution) of the catalog editions we currently have in our database.
2016-2017 ACME Community College
2015-2016 ACME Community College
2014-2015 ACME Community College
2013-2014 ACME Community College
2012-2013 ACME Community College
2011-2012 ACME Community College
2010-2011 ACME Community College
2009-2010 ACME Community College
2008-2009 ACME Community College
2007-2008 ACME Community College
2006-2007 ACME Community College
2005-2006 ACME Community College
2004-2005 ACME Community College
2003-2004 ACME Community College
2002-2003 ACME Community College
2001-2002 ACME Community College
2000-2001 ACME Community College
1999-2000 ACME Community College
1998-1999 ACME Community College
1997-1998 ACME Community College
1996-1997 ACME Community College
1994-1995 ACME Community College
When we run an import, we check each course included in the import file against the courses we have that were referenced in any of your published catalogs we have in our database. We store the relationship between the courses mentioned in your import file and the course information of the most recent catalog edition.
EQs: Equivalencies, rules, transfer credit courses, etc.
Import File: The file you are sending us that we are importing that contains your EQs.
Send Course(s): Courses from incoming students (feeder schools, etc.) are referred to as Send Courses.
Send Institutions: The related institutions are referred to as Send Institutions.
Receive Course(s): Courses at your institution are referred to as Receive Courses.
Receive Institution: Your institution is referred to as the Receive Institution.
Provider: You are referred to as the Provider.
Pre-import Process Overview
The import process consists of two parts; the pre-import process and the import process. The pre-import process is the process that takes the most time. On average the pre-import process takes 2 to 4 hours. The pre-process stage is all about getting your data in the right shape so that it can be imported. We groom data, match items, and generate exception lists. We generate the lists, we talk about them, and we handle them. This takes some time.
The grooming we do involves matching institutions in your file to the institution IDs we use internally. Institution exception lists are lists of institutions referenced in your file which we are unable to find a match for. Grooming also involves making sure that courses referenced in the file are matched to courses in actual catalogs we have on file or courses that have been input by you, the end user, in the case of user-added courses.
When the pre-import process has concluded, the actual import is very quick, sometimes just a few seconds.
Pre-import Process Steps
The Provider sends the Import File according to the specified format shown below (See: File Specification below). For each step in the pre-import process, we carry out that step and depending on the results we may have feedback and may contact you. The step may generate an exception list, for example a list of institutions that could not be matched, and so then we send those back to you with instructions.
The CSI internal steps in the process are:
- Import the import_file to SQL.
- Check column list (Makes sure all columns are present).
- Update data with CSI (CollegeSource, Inc.) internal IDs.
- Groom data (Removing duplicate spaces, blank characters, etc.).
- Check data for Receive Courses that don't match current Receive Courses on file.
- Notify Provider of non-matching Receive Courses with instructions.
- Have Provider add non-matching Receive Courses as User Added Courses (in the case of generic elective courses/placeholder courses/dummy courses).
- Discuss with Provider any other non-matching Receive Courses, if any exist.
- Create a list of all non-matching Send Institutions.
- Notify Provider of non-matching Send Institutions with instructions on how to find the remaining Send Institutions.
- Provider returns list with remaining Send Institutions identified, exceptions discussed.
- Courses referenced in the data are looked up.
- Notify Provider of non-matching Send Institution Courses via a list with statistics. Discuss with Provider Send Institution Course exceptions.
- Nearly ready to import. Two options:
- Delete existing EQs and import (overwrite) or...
- Leave existing EQs and import (append)
As long as the Provider is sure that the equivalencies that currently exist are not also in the import file, then we can leave them (typically there are no existing equivalencies).
Post discussions, we are ready to run the actual import script that imports the EQs.
The import script is run on the pre-import data. Usually seconds later the actual import is complete. Data as it appears in TES is reviewed.
The Import File should be a .txt file (vertical pipe "|" delimited) with column names in the first row. It should not contain any quoted text.
Name the file "tblABCD_yearmonthday.txt" (Ex. tblUCSD_20101118.txt)
We're using tbl for table, followed by the school acronym, "_" (the underscore character), 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 left NULL (i.e. empty) but still must be included.
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 (without the quotes):
"IPEDSID" (Note: also referred to as UNITID)
OPEIDs (usually) look like FICE codes but with two trailing zeroes.
OPEID or IPEDSID (UNITID) 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.
SendInstitutionName: This field is used to help match institutions when SendInstitutionCode fails (e.g. when it's wrong or ours doesn't match what you've got).
SendInstitutionCity: This field is used in conjunction with the field above to ascertain send institution matches in the event that SendInstitutionCode fails.
SendInstitutionCountry: This field is also used in conjunction with the field above to ascertain send institution matches in the event that SendInstitutionCode fails. "U.S.A." is fine for the United States of America.
EQValidDateStart and EQValidDateEnd: These are your EQ active begin dates and end dates. Filling these fields is optional. In TES 3.0, the date ranges you supply are the date ranges shown for your EQs in TES. Use mm/dd/yyyy format. For open ended EQs you can leave the end date blank. EQs with no begin date and no end date will be considered "active" when viewed in TES. If not blank, date ranges must be of the accepted date format above and be inclusively between '1/1/1753' and '12/31/9999'.
Active Date Backfilling:
For customers who would like it/need it, we can, upon request and after the import, dynamically determine EQ begin dates based on a few different things:
1) Begin date: Set to earliest course found (all Send Courses) while inst accredited.
2) Begin date: Set to earliest course found (all Send Courses) regardless of accreditation. 3) Begin date: Set to earliest duplicate (similar) EQ found.
4) Begin date: No begin dates.
MonthDay: Active dates will be either ‘1/1/’,‘8/1’ or ‘9/1’ appended to the year.
For more information on this, please contact us.
HideFlag: This field must be populated with the following value pairs: (no quotes)
"True" or "False"
"1" or "0"
The HideFlag field 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.
What does an actual example of an EQ look like as it sits in the table/file? Let's look at some examples of possible data:
Ex. 1. If you have BIO 101 from a Sending Institution transferring/equating-transfer(credit)-wise to your (Receive Institution) BIOL 1 course, that would show in the file as:
The other send courses and receive courses for that row/record will be blank. It takes one row/record in the import/export to display this relationship. In the example above, it only shows 4 columns of data of course but in the actual file, there are 33 columns.
If this EQ is just one course-to-one course then just the two course slots (cells) will be filled, the rest of that row will be empty. Most EQs are just one-to-one like this. Some Providers only have one-to-one EQs in their import files.
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 show in the file as:
The other send courses and receive courses for that record/line will be blank including "ReceiveCourseCode2".
It takes one row/record in the import/export to display this relationship.
Ex. 3. In the case where there is one course like send course BIO 101 transferring to your BIOL 1 course "OR" your OCHEM 1 course, that would show in the file as:
The other send courses and receive courses for that record/line will be blank including "ReceiveCourseCode2".
It takes two rows/records in the import/export to display this relationship.
You would need to create any pseudo- or placeholder-courses as "user-added" courses in TES prior to the import. For more info on "user-added" courses, watch the "user-added courses" movie in our movies section here.
My file is in the proper format.
Check each of the following):
__ All the columns in the file specification are present.
__ SendCourseCode1 is filled for every record. It is never NULL. It is never blank.
__ ReceiveCourseCode1 is filled for every record. It is never NULL. It is never blank.
__ No records are included that I don't want imported.
__ I have opened the file and I see the vertical pipe delimiters.
__ I have opened the file and I see that the data is readable (not gibberish).
You are ready to send your file!