Data Integration from FTP - Direct into Projects

FTP Folder

1. A FTP 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.

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

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

FTP Fields and Field Characteristics

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

1. ProjectID: The unique project identifier that is system assigned every newly created project, and found within a specific project on the Project tab user interface.
2. NodePath: The path value where the course can be assigned to a specific area in the report Hierarchy.*
3. AddDrop: Used to either ADD an enrollment to a course in the project (AddDrop = 1) or DROP an enrollment from a course in the project (AddDrop =0). This column can also be used to remove a course from the Project (AddDrop = 2).
   
   

   NOTE

   A null value for this field will be treated as an ADD.
4. Code: The course code as displayed to users (Maximum 512 characters).
5. Title: The course title (Maximum 1024 characters).
6. UniqueID: The unique identifier for the course (Maximum 32 characters, and this field needs to be unique over time and project).
7. CrossListUniqueID: An optional field and provides support for cross-listed courses. Where one course is selected as the "parent" course where all survey responses would be available for the parent and all "children" courses, children courses would not display in reporting. To make a course a child course, simply insert the parent's course unique id into this field, which makes the course a child and crosslists with the specified parent.*
8. SurveyStart: The defined start date for Survey Access for this course.*
9. SurveyEnd: The defined end date for Survey Access for this course.*
10. AdminStart: The defined start date for administrator reporting access for this course.*
11. AdminEnd: The defined end date for administrator reporting for this course.*
12. InstructorStart: The defined start date for instructor reporting access for this course.*
13. InstructorEnd: The defined end date for instructor reporting access for this course.*
14. TAStart: The defined start date for teaching assistant reporting access for this course.*
15. TAEnd: The defined end date for teaching assistant reporting access for this course.*
16. UserTypeId: The code for user type, Instructor Code = 3, Students = 4, and Teaching Assistant = 6.
17. FirstName: The user’s first name (Maximum 128 characters).
18. LastName: The user’s last name (Maximum 128 characters).
19. Email: The user’s email address (Maximum 256 characters).
20. Username: The user’s Course Evaluations & Surveys username (Maximum 60 characters).
21. Password: The user’s Course Evaluations & Surveys password (Maximum 16 characters).*

FTP Fields and Field Characteristics

1. File Header row includes the following fields:
   1. ProjectID, AddDrop, Code, Title, UniqueId, UserTypeId, Firstname, Lastname, Email, Username.
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.

FTP File with Optional Fields

1. File Header row includes the following fields:
   1. ProjectID, AddDrop, 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 FTP 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 within the specified project.
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. For sake of clarity, the FTP file may contain enrollments from multiple projects all needing to be added/dropped.
4. The FTP 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 project for 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 FTP folder and processed, a confirmation email will be sent to the email address you have listed under Account > FTP
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 FTP folder and the Course Evaluations & Surveys service to process the file is halted for any reason (e.g., System Maintenance, etc.), FTP files will accumulate in the FTP 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 FTP Folder not conforming to the required format will generate a Confirmation Email indicating a Failure to process the file.

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.