Importing Transfer Equivalencies
If your equivalencies are stored in your student system (Banner, uAchieve, Datatel, Jenzabar, PeopleSoft, etc.) and you would like to add them to TES, export them into a .txt file, and send to us to be imported. Imports are included with a Transferology subscription or can be done for a small fee.
Import specifications are below. Once you have your file, email it to tes-support@collegesource.com with the subject School Name - Equivalency Import.
In this article:
- 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, including file specifications (May require help from your IT department)
- 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 a video overview of the import process, see the Import and Export Equivalencies Demo.
Background
For CollegeSource customers interested in importing equivalencies into TES or Transferology, we first import into the CollegeSource database in order to leverage existing code and processes. Your ultimate goal or final destination may be to get equivalencies 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.
Terminology
EQs: Equivalencies, rules, transfer credit courses, etc.
Import File: The file you are sending us that we are importing 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.
- The import process.
The pre-import process is the process that takes the most time. On average, this phase takes approximately 2 to 4 hours. During this phase, the focus is structuring data and ensuring it meets the specifications and format needed for seamless import. Data is reviewed, matched, and an exceptions list is generated of data that needs further review. The list of exceptions will be discussed with the institution and revisions will be made to data until is it in acceptable state for import.
As part of the data review process, we will review the institutions in the import file and match them to the institution IDs used by TES. A list will be generated of all institutions matches cannot be found for. Additionally, the alignment process will ensure that courses within the file are linked to either courses within institution catalogs or User Added Courses created by the institution.
As noted, the pre-import process is the longest part of the process, as the steps involved can take significant time. The import process itself is remarkedly swift and can be completed sometimes in seconds.
Pre-import Process Steps
The Provider sends the Import File according to the specified format. 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 CollegeSource 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.
- Remove 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 the 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.
Import Process
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. All done!
File Specifications
The import file should be a pipe-delimited .txt file that includes the specified column headers in the first row. It should not contain any quoted text.
Name the file "tblABCD_yearmonthday.txt" but replace ABCD with the abbreviation/acronym of your institution (ex. tblUCSD_20101118.txt).
Extracting Equivalency Data for Import
Options for exporting equivalencies from the SIS:
- Build an empty table in SQL using this code.
- Build an empty table in Excel using this template.
You fill it from your system, export as txt, and send us the txt file. Again, once you have your file, email it to tes-support@collegesource.com with the subject School Name - Equivalency Import.
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.
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
Column header definitions
There are 33 column headers - all must be present for import. Download sample file above.
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)
OPEID
CEEB
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'.
Additional Options:
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:
- Begin date: Set to earliest course found (all Send Courses) while inst accredited.
- Begin date: Set to earliest course found (all Send Courses) regardless of accreditation.
- Begin date: Set to earliest duplicate (similar) EQ found.
- 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 or Transferology results. If an EQ HideFlag is set to "1" or "True" then that EQ will not show in your Public View or Transferology results. You can view more info on the Public View here.”
Please make sure that all your EQs that you want to show in the TES Public View or Transferology have a “0” in the HideFlag column.
Examples
Here are examples of what your import file may look like upon submission. Note, the first example, a one-to-one course equivalency, is the most common.
Example: 1:1 Course Equivalency
If you have BIO 101 from a Sending Institution transferring/equating-transfer(credit)-wise to your (Receive Institution) BIOL 1 course, the file would look like:
SendCourseCode1 | SendCourseCode2 | RecieveCourseCode1 | RecieveCourseCode2 |
BIO 101 | BIOL 1 |
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.
Example: 2:1 Course Equivalency
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:
SendCourseCode1 | SendCourseCode2 | ReceiveCourseCode1 | ReceiveCourseCode2 |
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 row/record in the import/export to display this relationship.
Example: "Or" Course Equivalency
Send course BIO 101 transfers to your BIOL 1 course "OR" your OCHEM 1 course.
SendCourseCode1 | SendCourseCode2 | ReceiveCourseCode1 | ReceiveCourseCode2 |
BIO 101 | BIOL 1 | ||
BIO 101 | OCHEM 1 |
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.
Important: Prior to the import, create any pseudo or placeholder courses as User Added courses in TES.
Data Checklist
Before sending your file, 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 to be 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.
You are ready to send your file! Email to tes-support@collegesource.com with the subject School Name - Equivalency Import.