Data Integration from SFTP - As Data Source

The Course Evaluations & Surveys SFTP data import as data source feature allows you to SFTP files of course information and corresponding student and instructor enrollments directly to Course Evaluations & Surveys, which can then be used to search and select for inclusion in desired Projects. Additionally, the Enrollment Refresh feature can be used to automate the update of student, instructor, and teaching assistant enrollments into courses that have been already imported into a project.

OPERATIONAL STEPS

SFTP Folder

1. A SFTP Folder will be setup by Course Evaluations & Surveys where the customer can deposit files for this process.
2. Connection to this folder will be via Secure File Transfer Protocol, SFTP.

For more information and assistance with this setup, please contact our Support Team by logging into Help center.

Once SFTP setup is complete, the following directories will be in your SFTP root directory:

1. DataSourceFilesToProcess: Drop files in this directory to upload course and corresponding enrollment data.
2. DataSourceFilesProcessed: After the course and corresponding enrollment file(s) has processed, it will be moved to this directory.

SFTP File

1. Format: Required file format is Comma Delimited, with CSV file extension (.csv)
2. File Name: Name the file CourseUsers-mmddyyyyhhmm.csv, where MMDDYYYYHHMM is a date/time identifier to further identify the file.
3. Header Row: The file must contain a header row which includes the field names described below, separated by commas.
4. Contents Rows: Each row represents an enrollment into a course, either performing an Add or Drop process for that enrollment, and aligned contents with the Header Row fields, separated by commas.

SFTP Fields and Field Characteristics

* Fields indicated with an asterisk are optional, and can be omitted from the SFTP file. When omitting fields from the file do not include the field name in the Header Row and omit data from Contents Rows.

1. NodePath: The path value where the course can be assigned to a specific area in the report Hierarchy.*
2. Code: The course code as displayed to users (Maximum 512 characters).
3. Title: The course title (Maximum 1024 characters).
4. UniqueID: The unique identifier for the course (Maximum 32 characters, and this field needs to be unique over time and project).
5. SurveyStart: The defined start date for Survey Access for this course.*
6. SurveyEnd: The defined end date for Survey Access for this course.*
7. AdminStart: The defined start date for administrator reporting access for this course.*
8. AdminEnd: The defined end date for administrator reporting for this course.*
9. InstructorStart: The defined start date for instructor reporting access for this course.*
10. InstructorEnd: The defined end date for instructor reporting access for this course.*
11. TAStart: The defined start date for teaching assistant reporting access for this course.*
12. TAEnd: The defined end date for teaching assistant reporting access for this course.*
13. UserTypeId: The code for user type, Instructor Code = 3, Students = 4, and Teaching Assistant = 6.
14. FirstName: The user’s first name (Maximum 128 characters).
15. LastName: The user’s last name (Maximum 128 characters).
16. Email: The user’s email address (Maximum 256 characters).
17. Username: The user’s Course Evaluations & Surveys username (Maximum 60 characters).
18. Password: The user’s Course Evaluations & Surveys password (Maximum 16 characters).*

NOTE

You can upload up to 440 characters for a Course Code and Course Unique ID as part of the data source. These values may be truncated throughout your PDF Reporting.

SFTP Fields and Field Characteristics

1. File Header row includes the following fields:
   1. Code, Title, UniqueId, UserTypeId, Firstname, Lastname, Email, Username.
2. Each additional row in the file indicates either a student, instructor or teaching assistant enrollment into a course following the fields indicated in the Header.

SFTP File with Optional Fields

1. File Header row includes the following fields:
   1. Code, Title, UniqueId, SurveyStart, SurveyEnd, AdminStart, AdminEnd, InstructorStart, InstructorEnd, TAStart, TAEnd, UserTypeId, Firstname, Lastname, Email, Username, Password.
2. Each additional row in file indicates either a student, instructor or teaching assistant enrollment into a course following the fields indicated in the Header.

File Processing

1. Once a file is deposited in the SFTP folder, the system/service will detect a new file has been added and immediately process the file. The enrollments contained in the file will accordingly get added or dropped from the specified course.
2. Dropping enrollments is no different than the process described above, with the exception that the AddDrop code in the row would be set to “0” to drop the enrollment.
3. The SFTP files should only contain an enrollment needing to be added or dropped. Enrollments contained in the file to be added, which are already in the specified course will be ignored if no changes are detected in any of the row contents for that enrollment.

File Confirmation Email and Error Messaging/Handling

1. Once a file is deposited into the SFTP folder and processed, a confirmation email will be sent to the email address you designated under Account > SFTP.
2. Confirmation Email Subject Line
   1. Includes – Filename
   2. Includes – File Status (Successful, Some Failures, Failed)
3. Confirmation Email Contents of Email
   1. Includes – Filename
   2. Includes – File Status (Successful, Some Failures, Failed)
   3. Summary Metrics Organized by Project ID from the file
      1. Courses Added
      2. Student Enrollments Added and Dropped
      3. Instructor or Teaching Assistant Enrollments Added and Dropped
      4. Failed Enrollments
      5. Specific Rows in File That Failed
4. Additional Error Handling
   1. In the event a file is deposited in the SFTP folder and the Course Evaluations & Surveys service to process the file is halted for any reason (e.g., System Maintenance, etc.), SFTP files will accumulate in the SFTP Folder until the service resumes processing, at which time the service will process any/all files in the folder beginning with the oldest file.
   2. Any files added to the SFTP Folder not conforming to the required format will generate a Confirmation Email indicating a Failure to process the file.

Removing Courses for the Data Source

Once courses have been added to the data source they remain there indefinitely.  If you wish to remove courses from the data source because they are no longer needed (i.e. - you are starting a new Fall term and no longer need the Spring courses that were uploaded into the data source), you can navigate to Account > FTP and delete the courses from the data source.  

This feature allows you to clear your entire data source or delete specific courses via the search tool. Once the process is complete, you’ll receive a confirmation email and the deletion will appear under Notifications.

NOTE: This does not remove any courses that have already been imported into a CES project.  It only removes the courses from the list of courses that is used to select and import courses into as CES project.

FILE REQUIREMENTS AND SAMPLE

1. Required file format is Comma Delimited, with CSV file extension (.csv)
2. Name file CourseUsers-mmddyyyyhhmm.csv
3. File contains a header row and each additional row represents an enrollment into a course.

ATTACHMENTS