Getting Started with Dataloads

Introduction

In order to use Tk20 and its reports to the fullest, your institution will provide data from your student information system on a regular basis. We recommend uploading data every other week to take advantage of our automated dataload process. (If files are provided less frequently, we perform a more manual dataload.) Uploading data at least twice a month ensures that you have the most up-to-data student information in your Tk20 system. This document explains exactly what data the Tk20 system will need from your student information system.

Mechanism for Import – Upload to Secure FTP Server

We recommend that you deposit your files on a secure FTP server hosted at Tk20’s Data Center. A Tk20 representative will work with you to set up this connection information and provide you with an account.

Data Format for Import

Files should be pipe-delimited format with each attribute delimited by the pipe (|) character. All attributes, regardless of whether or not data is present, will have the pipe character to delineate the attribute. The first line on each file will define the attribute corresponding to each column. All column headings must remain the same in subsequent files.

Example:

FirstName | LastName | PID | Gender | Race | Ethnicity

Tanya | Clemons | 253799499 | F | African American | Non Hispanic

Camille | Wooten | 252799598 | F | Caucasian | Hispanic

Marcie | Collins | 998775882 | | Asian | Non Hispanic

• Note: In the last record in the example above, the pipe demarking the gender field still exists, even though the gender field for that record is not provided.

In the file samples below, we’ve included required, recommended, and additional attribute fields that will be helpful in you assessment and accreditation reporting.

• Required fields: *
• Recommended Fields: +
• Additional attributes: For each file, we have tried to identify the most common attributes your institution might want to track in Tk20. You may include additional attributes in your extracts if your institution wishes to store and track that information in Tk20. Additional attributes may reside in the same file as long as there is a column heading for the attribute and the data in each row satisfies formatting requirements.
• Column Headings: We recommend using the same column headings and order as you see in this document, however, the titles of the column headings can be anything you need. For example, you can call the ‘First Name’ attribute ‘First’ or ‘Firstname’ or ‘First Name,’ etc. The headings should remain consistent in capitalization and formatting in each subsequent file.
• Unique Identifiers: The course section ID number and the personal ID number in data files should remain consistent in capitalization and formatting.
• File Extension: Please provide the files in pipe-delimited plain txt or unicode UTF-8 format. The following extensions are acceptable: .csv, .dat, and .txt.
• Consistent Data Formatting: We assume that the format specifications of any of these extracts will not change over time. In the event that you need to add or remove fields from these extracts in subsequent data import requests, we request that you let us know at least three weeks in advance so that we modify the scripts accordingly. Our internal processes for testing and validation are also applied to subsequent data imports to ensure that the quality of this service is maintained.
• Date Formats: For dates in your files, the following formats are acceptable: YYYYMMDD, MM/DD/YYYY, or MM-DD-YYYY. Please note, date cannot contain ‘0’ values, i.e. 00/00/1998.
• File Naming Conventions: Production-ready files should be named using the file names used by Tk20, followed by an underscore, followed by the date in yyyymmdd format. The date for the filename is the date the file is uploaded onto the server, for example: Transcripts_20031125.csv

Automated Dataloads

We are pleased to introduce a fully automated dataload process of your SIS files. This will ensure that your dataload turn around time is reduced and no manual intervention is needed. In order to take advantage of this timesaving service, we ask that you abide by the following:

• Files must be provided over SFTP
• Files must be provided at least two times a month
• Each set of files has unique individual files (i.e. only one student file, only one faculty etc.)
• File names are consistent without spaces. The automation won’t pick up the new files that do not conform to agreed upon naming convention
• File must have correct data and consistent data, including column headings
• Files extensions must be .txt, .csv or no extension
• Only place files on the FTP server that should be loaded
• Only one dataset should be provided at a time; there should be a gap of at least one day between two datasets

Dataload Process

Initial Data Analysis

Once you make the initial set of SIS extracts available to us, Tk20’s Dataload Team will analyze all the extracts to ensure that they conform to our guidelines and required fields are provided. Once we have your initial set of SIS extracts, we will map your data to the appropriate fields in Tk20. During this process, Tk20’s Services and Implementation Teams may request follow-up meetings with you as needed until all the data or format related issues are resolved. Please allow a minimum of three weeks<>after providing files for Initial Data Analysis and Scripting. During this process, extensive validation and testing takes place to ensure that all data elements are loaded and displayed correctly in the application.

Unique Identifier

A “unique identifier” is an element, such as an ID number, that is only used for one person, course, or site within the system. The unique identifier is key to Tk20’s ability to accurately import data and ensures that each record in a file is unique. It is imperative that the same unique identifier is used for the record each time the file is provided for dataload. The same unique identifier must be used for referencing the record in other files. For example, if student records are identified by Campus ID in the student file, the student record should be identified by the same Campus ID in the student schedule file.

When the Tk20 dataload utility loads the file, it processes the file line by line. During the process, it searches for the unique identifier in the system. If it finds a record with that unique ID, it updates the remaining attribute information for that record. If it does not find a record with that unique ID, it creates a new record with that ID and imports all the other attributes provided in the file.

Example:

FirstName | LastName | PID | Gender | Ethnicity

Tewanna | Clemons | 253799499 | F | African American

Camille | Wooten | 252799598 | F | Caucasian

Marcie | Collins | 998775882 | F | Asian

For example, when the data is loaded from the example file above, the dataload utility will check to see if a record with ID 253799499 exists in the system. If it finds that record, it updates other attributes for that record. If it does not find one, it creates a new record with ID 253799499 and imports all other attributes.

Files to be Provided

For the below files, * = required and + = strongly recommended

File 1 – Student Data

Example File Name: Student_Data

First Name *

Last Name *

Username *

Student ID * (Unique Identifier)

Term Code *

Email *+

Date of Birth +

Major Code ^{1 2}+

Major Name ^{1 2}+

Program Code ^{1 2}+

Program Name ^{1 2}+

Campus +

GPA

Classification

Year

Race

Ethnicity

Gender

Attending Status

Advisor PID ³

Example:

First Name | Last Name | Username | Student ID | Term Code | Email | Program Code | Program Name

Tewanna | Clemons | tclemons | 253799499 | 201410 | tclemons@school.edu | ECED | Early Childhood

Camille | Wooten | cwooten | 252799598 | 201410 | cwooten@school.edu | ELED | Elementary Ed

Marcie | Collins | mcollins | 998775882 | 201410 | mcollins@school.edu | GERMFLED | K-12 German Ed

¹Tk20 can accommodate single or multiple programs and major associations. If multiple programs and majors exist for one student, please create a new column for each applicable program/major code and name (i.e., Program Code 1 | Program Name 1 | Program Code 2 | Program Name 2, etc.).

²Tk20 will concatenate the code to the name for Major and Program (eg Early Childhood Education ECE). This is necessary to avoid data load errors due to duplicate values. If this will be an issue kindly let us know.

³This PID must be a user in the system or included in the faculty file for a successful import.

File 2 – Course Data

Example File Name: Course_Data

Please note that the section ID must be unique across terms.

Section ID * (Unique Identifier)^{1 4}

Section Title *

Course Number *

Section Number *

Term Code *

Term Name * ²

Term End Date *²

Delivery Method

Campus

Organization Code³

Example:

Section ID | Section Title | Course Number | Section Number | Term Code | Term Name | Term Start |

Term End | Campus

11321201410 | World Archeology | ARCH3131 | 01 |201410 | Spring 2014 | 01/15/2014 | 05/23/2014 | MAIN

¹If your section IDs repeat in subsequent terms, please append the term code to section ID to make the section IDs unique as shown in the example.

²Each term may only have one start date and one end date in Tk20. If you have multiple start and end dates for one term name, please include the earliest start date and latest end date.

³Tk20 can accommodate single or multiple organization associations. If multiple organization associations exist, please create a new column for each organization (i.e., Organization 1 | Organization 2 | Organization 3, etc.).

⁴If you intend to implement LTI (integration with your compliant LMS), please provide the course section ID that is passed by your LMS so that you can establish a seamless integration. This same section ID should be included in the faculty and student schedule files.

File 3 – Faculty Data

Example File Name: Faculty_Data

First Name *

Last Name *

Username *

Faculty ID Number * (Unique Identifier)

Email Address *

Term Code

Gender

Race

Ethnicity

Campus

Organization Code¹

Example:

First Name | Last Name | Username | Faculty ID Number | Email |Gender | Ethnicity | Term Code Catherine | Woody | cwoody | 010320447 | cwoody@school.edu | F | White (Non-Hispanic Origin) | 201410

¹Tk20 can accommodate single or multiple organization associations. If multiple organization associations exist, please create a new column for each organization (i.e., Organization 1 | Organization 2 | Organization 3, etc.).

File 4 – Student Course Schedule Data

Example File Name: Student_Schedule

This file will contain the course schedule for the entire student population. For each student identified in the course schedule, the SectionID will identify the courses in which the student is registered. For a student registered for six courses, the student’s ID number will be repeated six times in this file.

Student Identification Number *

SectionID*

Activity Type (add or drop) *

Example:

Student ID Number | SectionID | Activity Type

253799499 | 11321201410 | A

253799499 | 11322201410 | A

253799499 | 11327201410 | D

Note: When processing this file, we will perform all drops first and then will perform all adds from the file.

File 5 – Faculty Course Schedule Data

Example File Name: Faculty_Schedule

This file will contain the teaching schedule for all instructors. For each instructor identified, the SectionID will identify the section that the instructor is teaching. For an instructor teaching six courses, the faculty ID number will be repeated six times in this file.

Faculty Identification Number *

SectionID *

Activity Type (add or drop) *

Example:

Faculty ID Number | SectionID | Activity Type

010320447 | 11321201410 | A

109407848 | 11322201410 | A

109407848 | 45344201410 | D

Note: When processing this file, we will perform all drops first and then will perform all adds from the file.

File 6 – Transcript Data

Example File Name: Transcripts

This file is optional. Please provide transcripts with the following information for each student. Align each transcript with the corresponding student ID number. Please note that you will want to include transcripts from the previous term (not current term).

Student Identification Number *

Section Name +

Course Number *¹

Section Number *

Term Code *

Term Name*

Term Start Date*²

Term End Date*²

Grade*

Course GPA +

Credits +

Example:

Student ID Number | Section Name | Course Number | Section | Term Code| Term Name | Term Start Date | Term End Date | Grade | Credits

252632222 | World History | HIS305 | 01 | 201340 | Fall 2013 | 08/27/2013 | 12/04/2013 | B | 3.0

¹Please include course number with subject included (i.e. ENGL315 instead of 315)

²Each term may only have one start date and one end date in Tk20. If you have multiple start and end dates for one term name, please include the earliest start date and latest end date.

File 7 – Test Data

File Name: Test

This file is optional. Please provide the following test information for each student. If a student has multiple test scores, you will need to repeat the student ID number for each test score.

Student ID Number *

Test Date *¹

Test Name *

Test Code *

Test Score *

Example:

Student ID Number | Test Date | Test Name | Test Code | Test Score 252632222 | 20121120 | SAT | SAT1 | 1780

¹Please indicate the date taken for each test to record a test history when the same test was taken multiple times.

The sample SIS data files are attached to this article.