Data File Review Guide

Introduction

This article will guide you through the art of reviewing your institution’s data files to ensure that everything is in the correct format and contains the proper data.

Data Specifications Sheet

Here is the link to the AEFIS Data Integration Specification version 4 which is provided to your institution as the guide to create the files. You can use this to make sure that all required fields are included in the files among other things.

File List

The following is a list of the files that we import.

Required:

1. Faculty Information
2. Student Information
3. Course Catalog
4. Course Sections
5. Registration

Optional:

1. Student Degrees
2. Term (Coming Soon)

File Format

The first step of the file review is open each file in your favorite text editor and make sure the following requirements are met.

1. The header row is at the top of the file
2. Every field is surrounded by double quotes i.e. “Subject Code”
   1. If a field is blank it contains double quotes i.e. ““
3. Each field is separated by a comma
4. Scroll through the file and make sure that there are no line breaks for what should be a single row

File Content Review

Next you should open each file in Excel and review the file content to make sure that they will import successfully.

For all files make sure that at least the required fields are included in the files.
Also check that the data in the field matches up with the header it belongs to. For example, email addresses are in the email address field.

All the examples below are done using Excel but you can use your favorite spreadsheet editor to accomplish the below.

Faculty and Student Files

Check that all users have all the required fields filled out.

Course Catalog File

This file provides AEFIS the institution’s Course Catalog.

1. Make sure that there are no duplicate Course Unique IDs
   1. Highlight the entire CourseUniqueID column
   2. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values > Click Ok
   3. Select the entire sheet > Click Sort & Filter > Custom Sort
      1. Click My data has headers
      2. Sort by: CourseUniqueID
      3. Sort on: Cell Color
      4. Order: Click on the red block
         1. If there is no red block you can stop and click Cancel because there are no duplicates
      5. Click OK
   4. Record the duplicates that you have found if any
2. Make sure that there are not departments in multiple colleges
   1. Highlight the entire DeptCode column
   2. Click Sort & Filter > Custom Sort
      1. Click My data has headers
      2. Sort by: DeptCode
      3. Click Ok
   3. Scroll through the file and make sure that you do not see any DeptCode that exist in multiple Colleges

Course Section File

This file provides AEFIS the Course Sections within one term.

The Course Section file is a term based file so it should only contain one term code and partial terms that belong to the one parent term. If there is more than one term in the file the client should create a file for each parent term.

1. Make sure that there are no duplicate Course Unique IDs
   1. Highlight the entire CourseSectionUniqueID column
   2. Click Conditional Formatting > Highlight Cells Rules > Duplicate Values > Click Ok
   3. Select the entire sheet > Click Sort & Filter > Custom Sort
      1. Click My data has headers
      2. Sort by: CourseSectionUniqueID
      3. Sort on: Cell Color
      4. Order: Click on the red block
         1. If there is no red block you can stop and click Cancel because there are no duplicates
      5. Click OK
   4. Record the duplicates that you have found if any
2. Choose a small set of CourseUniqueIDs and verify that they exist in the Course Catalog file
3. Make sure that at least one Instructor field is present in the file
   1. This can be PrimaryInstrUnivid and/or SecondaryInstrUnivid field
   2. These fields can contain multiple instructor university IDs separated by a comma
4. If the you are is going to connect an LMS with AEFIS verify that the ExternalLMSID is included and populated.
5. If the you plan to cross-list sections automatically through the feed the client should have included the following fields
   • ParentSubjectCode
   • ParentCourseNumber
   • ParentSectionNumber
   1. In order for the cross-listing to work the follow rules must apply
      1. The parent section must refer to itself in the fields above
      2. The child sections must refer to the parent section in the fields above
      3. See the table below for an example

Registration File

This file provides AEFIS the registration information for the Course Sections within one term.

The Registration file is a term based file so it should only contain one term code and partial terms that belong to the one parent term. If there is more than one term in the file the client should create a file for each parent term.

1. Choose a small set of CourseSectionUniqueIDs and verify that they exist in the Course Section file
2. Choose a small set of STUDENTUnivIds and verify that they exist in the Student file

Student Degree File

This file provides AEFIS the degree information per student within one term.

The Student Degree file is a term based file so it should only contain one term code and partial terms that belong to the one parent term. If there is more than one term in the file the client should create a file for each parent term.

1. Choose a small set of CollCodes and verify that they exist in the Course Catalog file
2. Make sure that there are no duplicate Major Descriptions across multiple Major Codes
   1. Select the entire MajorCode and MajorDesc columns and copy them
   2. Create a new sheet and paste the columns
   3. Remove the duplicates
   4. Check for an MajorDesc that exist in multiple MajorCodes (see the example below)

Staff File

This file is optional and can be used to add additional staff that are not included in the Faculty or Student Files.

1. Verify that the RoleCodes match the Roles code listed the Data Specs Sheet
2. Choose a small set of BusinessObjectSeparators and verify that they match up with their business objects. For example, if the RoleCode is COLLADMIN make sure that the code in BusinessObjectSeparator exists in CollCode in the Course Catalog file

Term File

Coming Soon

When your first set of files is sent via SFTP please email Erica at [email protected] to initiate the file review process.