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 3

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

1Tk20 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.).
2Tk20 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.
3This 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 Start Date * 2
Term End Date *2
Delivery Method
Campus
Organization Code3

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

1If 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.
2Each 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.
3Tk20 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.).
4If 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 Code1

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
1Tk20 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 *1
Section Number *
Term Code *
Term Name*
Term Start Date*2
Term End Date*2
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

1Please include course number with subject included (i.e. ENGL315 instead of 315)
2Each 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 *1
Test Name *
Test Code *
Test Score *

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

1Please 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.

Was this article helpful?
0 out of 0 found this helpful

Articles in this section

See more
How to Contact Support
Click the "Submit A Request" button at the top right or bottom of the screen to create a case.
Watermark Academy
Click to access the Watermark Academy for free webinars, workshops, certifications, and free on-demand training