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.

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 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-processing 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. 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:

1. Import the import_file to SQL.
2. Check column list (Makes sure all columns are present).
3. Update data with CSI (CollegeSource, Inc.) internal IDs.
4. Groom data (Removing duplicate spaces, blank characters, etc.).
5. Check data for Receive Courses that don't match current Receive Courses on file.
6. Notify Provider of non-matching Receive Courses with instructions.
   1. Have Provider add non-matching Receive Courses as User Added Courses (in the case of generic elective courses/placeholder courses/dummy courses).
   2. Discuss with Provider any other non-matching Receive Courses, if any exist.
7. Create a list of all non-matching Send Institutions.
8. Notify Provider of non-matching Send Institutions with instructions on how to find the remaining Send Institutions.
9. Provider returns the list with remaining Send Institutions identified, exceptions discussed.
10. Courses referenced in the data are looked up.
11. Notify Provider of non-matching Send Institution Courses via a list with statistics. Discuss with Provider Send Institution Course exceptions.
12. Nearly ready to import. Two options:
    1. Delete existing EQs and import (overwrite) or...
    2. 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.