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:

1. The pre-import process.
2. 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:

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. Remove 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.

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:

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:

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.

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.