Data Integration Guide version 1
Data Retention
GradesFirst allows clients to determine and manage their own data retention policy. Clients have complete control over their data. The application includes a user interface and data import mechanisms that allow clients to alter and remove information. When information is deleted from the GradesFirst application, it is completely removed from our databases.
Populating Student Data in GradesFirst
Student data is exported from the school's information system and is uploaded directly into the GradesFirst application by a user or agent of the institution via automation. After successful authentication into the GradesFirst system, the file is uploaded, the data is validated, and then persisted to the GradesFirst database. The institution does not experience any down time during this process, and the uploaded file is encrypted and kept on the GradesFirst servers in a secured location for 5 days in case they are needed for customer support issues. After 5 days, the uploaded file is automatically deleted and cannot be recovered. As with any interaction with GradesFirst, this data is transmitted via an SSL encrypted connection. Each institution decides how often data is refreshed from the student information system. Most institutions update once daily.
There are a variety of ways to import your data into GradesFirst, and your institution can use whichever one(s) fit your needs best. Non-technical users can easily run one-time imports using the Admin panel inside GradesFirst, or university IT staff can run imports via the GradesFirst API. API imports may use either cURL or SFTP, and they can be run as one-time imports or as automatically recurring imports. Automatic imports offer the advantage of keeping your data in GradesFirst up-to-date, and they reduce the hassle of manually running imports as the semester progresses. University IT staff can set up the automated imports or outsource that work to GradesFirst. Once the automated import is in place, very little if any maintenance is required going forward.
For added security, import files can be encrypted before they are transferred. GradesFirst provides full documentation and support on setting up encrypted uploads. Once the file is encrypted, you can upload it to GradesFirst just as you would any other file, and GradesFirst will automatically handle decrypting and processing of the file as normal.
Testing Data Uploads
GradesFirst allows clients to test their data import files on a test system before going to production. A valid user account will be provided to personnel responsible for testing the data import. Please contact support@gradesfirst.com for more information on getting your test account set up.
CSV Data Uploads
GradesFirst data uploads use the CSV file format. Please follow this section to ensure data integrity.
Things to Consider:
- The file must be a plain text or flat file with values separated by commas.
- All columns should be accounted for when creating your file. In other words, even if you do not plan on importing a field (e.g. Date of Birth, Race), those fields will still need “place holders” in your file. So it is very important to ensure your import file contains all fields, even if they are simply placeholders.
- The first record or row of the file is a Header Record and must contain column headers for the rest of the data. Please see the Header Row descriptions in each file layout definition.
- Please enclose every field in double quotes. If any field value contains a comma or apostrophe, and the field value is not enclosed in double quotes, the format of the CSV file will be corrupted. This is a standard CSV file format rule. Although it is not an absolute requirement to include all fields in double quotes, since Names, Majors, Courses, and Addresses often contain apostrophes or commas, we highly recommended enclosing every field in double quotes to ensure data integrity.
Incorrect:
Correct:
- Editing your file in spreadsheet software like Microsoft Excel can automatically reformat and corrupt the data by stripping out leading zero’s, changing numbers to letters, and inserting carriage returns. It is not recommended to edit your data files with Excel unless you are an expert user, because it can cause data corruption and/or failed uploads. For this reason, we recommend editing all CSV files with a plain text editor, such as Notepad on Windows, TextEdit on Mac, or vi on Unix.
- If any field contains double quotes as part of the actual data, such as a course named: “An In-Depth Study of Shakespeare’s “Hamlet””, you can insert these double quote characters into your data file by replacing each double quote with two consecutive double quote characters. So, the correct field would be: “An In-Depth Study of Shakespeare’s ““Hamlet”””.
Here is an example of enclosing the field in double quotes by concatenating double quote characters to the beginning and ending of the returned string value and checking for double quotes in the field value:
SELECT ‘”’ || replace(field_name, ‘”’, ‘””’) || ‘”’
AS my_field_name
FROM table_name
- If you want to insert the required Header Record or Column Headers into the import file using SQL, one quick way is to use a UNION statement to accomplish this. (Be sure to account for the syntax of the SQL you are using)
- Below is another example.
SELECT “Student ID”, “Student First Name”, “Student Last Name”
UNION
SELECT StudentId, StudentFirstName, StudentLastName
FROM Student_Table;
This will result in the first row to contain the required header record (column headers).
NOTE: The SQL above is an example and the Header Record in the actual import files would need to contain all the columns for each file.
GradesFirst Data Layouts
Student Data File
Summary: | This file contains the primary information needed by GradesFirst. This includes general student information, student’s class schedules, and the faculty members teaching those classes along with their contact information. NOTE: User will receive notification via email that upload of this file’s data has completed. Please do NOT upload the Category Data File until this completion email is received. |
Frequency: | Each institution decides how often data is refreshed from the student information system into the GradesFirst system. See the Frequency of Data Import section of this document. |
Data Selection Criteria: | Upload data for one term at a time. For example, upload data for the fall semester separate from data for the spring semester. The students to be included in this file are determined by each institution department using the GradesFirst. For instance, if athletics is using it, they’ll probably want to load student athletes. Or maybe the TRIO program only wants to load students in the TRIO program. |
Filename and Format: | This file is first required to be in an 8-bit or standard ASCII file format, then a comma separated values (CSV) file format. The filename for the exported file may contain letters and numbers and must end with the file extension “.csv”. The maximum length of the filename is 100 characters. |
Field Delimiter: | A single comma. |
Header Row: | All data imports must have a header row on the first line. Ex. Student ID,Student First Name,Student Last Name, Automated Imports: header must contain specific values. See Automated Uploads for info. Ex. GradesFirst Import Version=1,Term=sp_2011,Group=Iowa,Type=StudentClass,Master List, |
Single Sign-On | If your university is using Single Sign-On, you will need to coordinate with the IT staff who handle the SSO setup to ensure that you are both using the same user identifier. This ensures that GradesFirst and your university SSO can correctly identify users when they log in. This user identifier must be unique, so the most common examples are: University ID, Login, CWID, UMID, RAM ID, C number, G number, SKG number, ONLID, No photo ID, Peoplesoft Empl ID, etc. We do not recommend using e-mail because some institutions may allow duplicate e-mail addresses over time. Whatever identifier you end up using, you have two different fields where can choose to insert this identifier in your upload – the Primary ID or the Alternate ID. After you add this field to your upload, coordinate with the university IT staff member who is handling the SSO, let them know which identifier you are using, and let them know if you placed it in the primary ID field or the alternate ID field. Be sure the SSO server can return this value for each user when they attempt to login to GradesFirst. |
Additional Notes: | In the following table, the Required column shows required fields for the data export. While you can choose not to import the non-required fields, doing so will greatly limit the effectiveness of GradesFirst. |
Example of what a Student Data File might look like:
Student File Data Elements and Layout:
Required Value | Field Name | Format | Max Length | Field Description and Notes |
Y | Primary Student ID | Text | 255 | Unique institution identifier for each student. This field is encrypted with 256 bit Blowfish encryption. |
Y | Student First Name | Text | 255 | |
Y | Student Last Name | Text | 255 | |
Y | Student Email | Text | 255 | Please provide a unique email address for each student. |
N | Student Address1 | Text | 255 | |
N | Student Address2 | Text | 255 | |
N | Student City | Text | 255 | |
N | Student State | Text | 255 | |
N | Student Zip | Text | 255 | |
N | Student Home Phone | Text | 255 | Format should be: 555-123-9876 |
N | Student Cell Phone | Text | 255 | Format should be: 555-123-9876 |
N | Student Classification | Text | 255 | Valid values are as follows: |
N | Overall GPA | #.#### | Valid values include: 3.12, 0.1234 | |
Y | Class ID | Text | 255 | Also known as the Course Number. Valid example is "MA101". |
Y | Class Section | Text | 255 | Valid examples would be "01" or “12”. |
Y | Class Title | Text | 255 | Valid example would be "Beginning Algebra". |
N | Class Credit Hours | ### | Must be a number. Other values are ignored and it sets to zero. | |
Y | Class Start Time | Text | 255 | Time may be expressed in 12-hour or 24-hour format. 12:00 AM and 0000 denote midnight. If the colon is omitted, you must use the 4 digit format. Ex. 9:00 AM, 0900 AM, 1700, 0900. |
Y | Class End Time | Text | 255 | See Class Start Time |
Y | Class Meeting Days | Text | 255 | Denoted by a series of letters. No characters except spaces. |
N | Class Location | Text | 255 | Example would be "Harris Hall - Room 213-A". |
Y | Primary Instructor Id | Text | 255 | Unique identifier for the instructor. |
Y | Instructor First Name | Text | 255 | |
Y | Instructor Last Name | Text | 255 | |
N | Instructor Email | Text | 255 | Please provide a unique email address for each instructor. While instructor emails are not required, not providing them will greatly limit the functionality in GradesFirst (e.g. Progress Reports/Early Alerts). Therefore we strongly urge you to include these emails in your import file. |
N | Instructor Phone | Text | 255 | |
N | Class Meeting Type | Text | 255 | Supported values: “Lecture”, “Lab”, “Online”, “Independent Studies”, “Distance Learning”, “Recitation”, “Seminar”, “Other”. |
N | Midterm Grade | Text | 255 | The midterm grade for that student and class. Examples: "A+", "A", "A-", "B+", "B", "B-", "C+", "C", "C-", "D+", "D", "D-", "F". |
N | Final Grade | Text | 255 | See Midterm Grade |
N | Student Major Code | Text | 255 | Valid example would be “CS” |
N | Student Major Name | Text | 255 | Valid example would be “Computer Science” |
N | Student Total Credit Hours Attempted | ###.## | Must be a number. Other values are ignored and it sets to zero. | |
N | Student Total Credit Hours Earned | ###.## | Must be a number. Other values are ignored and it sets as zero. | |
N | Student Sex | Text | 255 | Supported values: “M”, “MALE”, “F”, “FEMALE”, “O”, “OTHER” |
N | Student Race | Text | 255 | This field is user-definable and may contain any text as long as it is less than 255 characters. |
N | Student Date Of Birth | Text | 255 | Must be in the format MM/DD/YYYY. |
N | Alternate Student Id | Text | 255 | This can be used as an alternate identifier for the student. Some institutions do not encode the official Student ID on the Student ID card but rather use another “number” for identification purposes. This field can be used for this number. |
N | Course Section Begin Date | Text | 255 | Must be in the format MM/DD/YYYY. If no date is provided, the Term’s start date will be used. |
N | Course Section End Date | Text | 255 | Must be in the format MM/DD/YYYY. If no date is provided, the Term’s end date will be used. |
N | Student Enrollment Status | Text | 255 | This field is user-definable and may contain any text as long as it is less than 255 characters. |
N | Student Enrollment Goal | Text | 255 | This field is user-definable and may contain any text as long as it is less than 255 characters. |
N | PIN | Text | 255 | A number used as a digital signature mechanism for staff members who take attendance. |
N | SSO ID | Text | 255 | A unique user identifier that can be used with Single Sign-On. |
N | Course External ID | Text | 255 | A unique identifier for a course. This field was added to facilitate transitions from Version 1 to Version 4 data imports. Please contact your Client Success Manager for more information. |
N | Section External ID | Text | 255 | A unique identifier for a section. This field was added to facilitate transitions from Version 1 to Version 4 data imports. |
N | ACT Total | ### | 255 | This should be a numeric value |
N | ACT English | ### | 255 | This should be a numeric value |
N | ACT Reading | ### | 255 | This should be a numeric value |
N | ACT Math | ### | 255 | This should be a numeric value |
N | ACT Science | ### | 255 | This should be a numeric value |
N | SAT Total | ### | 255 | This should be a numeric value |
N | SAT Verbal | ### | 255 | This should be a numeric value |
N | SAT Math | ### | 255 | This should be a numeric value |
N | High School GPA | #.### | Valid values include: 3.12, 0.1234 | |
N | First Generation College Student | Text | Supported values: “Y” or “N” | |
N | Father’s Education Level | Text | 255 | Supported values: "None", "High School Diploma", "2 Year College Degree", "4 Year College Degree", "Graduate Degree" |
N | Mother’s Education Level | Text | 255 | Supported values: "None", "High School Diploma", "2 Year College Degree", "4 Year College Degree", "Graduate Degree" |
N | High School Zip | ##### | This should be a numeric value | |
N | Household Income | Integer | This should ONLY be a numeric value, excluding commas and symbols (e.g. “$”). Valid examples are: 100000, 85000, 63500 | |
N | Single Parent Family | Text | Supported values: “Y” or “N” | |
N | Transfer GPA | #.### | Valid values include: 3.12, 1.1234 | |
N | Home College | Text | 255 | This field is user-definable and may contain any text as long as it is less than 255 characters. |
N | Term GPA | #.### | Valid values include: 3.12, 1.1234 | |
N | Receive Text Messages | Y/N | This value defines whether a student will, by default, receive text messages. |
Category File Data Elements and Layout:
Summary: | This file is used to associate students to categories within the GradesFirst system. Some examples of categories would be the sports they play, TRIO, Conditionally Admits, or any other designation the institution applies to students being monitored with GradesFirst. |
Frequency: | This is determined by each institution as to how often this data is refreshed in the GradesFirst system. Ideally, this file should be uploaded each time the Student Data File is uploaded. See the Frequency of Data Import section of this document. |
Data Selection Criteria: | Upload data for one term at a time. For instance, you would upload data for the fall semester separate from the data for the spring semester. |
Filename and Format: | This file is first required to be in an 8-bit or standard ASCII file format, then a comma separated values (CSV) file format. |
Field Delimiter: | A single comma. |
Header Row: | All data imports must have a header row on the first line. Ex. Student ID,Category Code,Category Name
Ex. GradesFirst Import Version=1,Term=sp_2011,Group=Iowa,Type=StudentSport,Master List, |
Example of what a Category Data File might look like:
Category Data Elements and Layout:
The category is used as an identifier of sorts for students in GradesFirst. You will see each student’s category listed on their Home page for ease of identification. Users will also be able to print any report or perform any searches for students of a particular category. A valid example of how our athletic clients use categories would be to identify which team for which their students belong (e.g. Football, Baseball, Softball).
Required Value | Field Name | Format | Max Length | Field Description |
Y | Primary Student ID | Text | 255 | Unique identifier for the student. This is the "Student's Id". This field is encrypted in the GradesFirst database with 256 bit Blowfish encryption. |
Y | Category Code | Text | 255 | The code or id that identifies this category at your institution. |
Y | Category Name | Text | 255 | Name of the category. Example: "Baseball" |
Importing Your Data
There are a variety of ways to import your data into GradesFirst, and your institution can use whichever one(s) fit your needs best. Non-technical users can easily run one-time imports using the Admin panel inside GradesFirst, or university IT staff can run imports via the GradesFirst API. API imports may use either cURL or SFTP, and they can be run as one-time imports or as automatically recurring imports. Recurring imports offer the advantage of keeping your data in GradesFirst up-to-date, and they reduce the hassle of manually running imports as the semester progresses. To set up recurring imports, your university IT staff will need to write a script to export the data from your student information system, format the data according to this guide, and then upload the data file via the GradesFirst API. For recurring data uploads, institutions may upload at their discretion: nightly, weekly, monthly. We ask that institutions schedule recurring imports after 7pm CST, but they must be finished by 5am CST.
API Imports
You can upload API imports using cURL or SFTP. To be sure your systems allow traffic to GradesFirst servers, please see Firewall Exceptions.
All API imports use the header row to let GradesFirst know about your import details. The header should be on the first line of your file, is case-sensitive, and should look like this:
Format:
GradesFirst Import Version=1,Term=<term_id>,Group=<group_id>,Type=<import_type>,<Master List>
Example:
GradesFirst Import Version=1,Term=sp_2011,Group=Iowa_state,Type=StudentClass,Master List
<term_id>: The “Term ID” set within GradesFirst for a term. Set at Admin -> Academic Terms.
<group_id>: The “Group ID” set within GradesFirst for the group. Set at Admin -> Group Settings.
<import_type>: If it is a student upload, this should be “StudentClass” or If it is a category upload, this should be "StudentSport"
<Master List>: Optional field. Should only be included in your header IF students excluded from your file should be dropped from all previously enrolled courses for the specific term. For instance, if student Jim Johnson was enrolled in five courses at the start of the term, then dropped out of institution, your next student upload file will most likely not contain any records about Jim. So adding this header field to will direct GradesFirst to drop all enrollments for any students not contained in the upload.
Important Note: When selecting a time to schedule your automated upload, please do not schedule your file to import between 5:00am – 6:00am CST. This is our deployment window and all services are stopped during this time. So please ensure all uploads are finished by 5:00am CST.
API Imports with SFTP
You can set up SFTP imports with the help of your GradesFirst trainer during your institution’s implementation of GradesFirst. If your implementation has already been completed, simply contact support and we’ll be glad to help out. Here’s the process:
- Add GradesFirst’s IP addresses to your institution’s firewall whitelist as outlined above.
- E-mail us the SSH RSA Public Key(s) for the computer(s) that will be sending the import files via SFTP.
- We’ll assign you a username and Internet address where you can send the imports.
- (Optional) If you wish to receive import status e-mails after each import, log into GradesFirst, click on the Admin tab, click on School Settings, and enter your e-mail address under “API Imports E-mail Address”. Click the Update Settings button to save.
- Upload the import file with SFTP.
API Imports with cURL
You can also run API imports using an open-source command line tool named cURL. (Although we recommend cURL, GradesFirst is not responsible for it, and cannot be held liable for damages due to installing or using it.)
Installing cURL on Windows
1. Download the appropriate version of cURL for your version of Windows.
2. Unzip the file.
3. Copy the curl.exe file into C:\Windows\System32
4. To test cURL:
1. Go to Start Menu > Run
2. Type cmd and hit Enter.
3. Type curl --help and hit Enter.
You should see information about the various options cURL provides.
Basic Automated Upload Example for Windows
1. Create a folder at C:\gradesfirst
2. In the C:\gradesfirst folder, create a folder named current and another folder named archive so that you have a folder structure like this:
C:\gradesfirst\current
C:\gradesfirst\archive
In this example, the current folder is where new files are placed each day by some automated process. The archive folder is where files will be placed after they are uploaded.
3. In the C:\gradesfirst folder, create a file called upload.bat
In this file, you'll want to include your script for uploads. An example of what this script might look like is:
CD C:\gradesfirst\current
for %%f in (*.csv) do @curl -u <username>:<api-key> -F "data=@%%f" https://<environment>.gradesfirst.com/api/imports
move \-y "C:\gradesfirst\current\*.csv" "C:\gradesfirst\archive\"
Be sure to replace the tokens above in your script:
<username> - the username of the GradesFirst user you'll be using for imports.
<api-key> - the GradesFirst import user's API Key. You can see this key by logging in as the import user and navigating to Admin > View My User's API Key)
<environment> - Your institution 's GradesFirst subdomain for either the training or production site, depending on the site to which you'd like to send information.
Training Site Example: coastalcollege-training.gradesfirst.com
Production Site Example: coastalcollege.gradesfirst.com
So, the curl command in your script might look like:
curl -u johndoe:7rjto38f0swk4j598 -F "data=@%%f" https://coastalcollege-training.gradesfirst.com/api/imports
To test your script:
1. Place one of the sample import test .csv files in the C:\gradesfirst\current folder
2. Go to Start Menu > Run
3. Type cmd and hit Enter.
4. Run your upload.bat file by typing C:\gradesfirst\upload.bat. Within a minute or two, your file should show up in GradesFirst on the Data Import screen (Admin > Data Import).
5. Create a Windows scheduled task so that the upload occurs automatically on a schedule of your choosing. We recommend uploading once a day between the hours of 10 PM and 5 AM central time.
How to create a scheduled task on Windows 7:
1. Open Task Scheduler by clicking the Start button, clicking Control Panel, clicking System and Security, clicking Administrative Tools, and then double-clicking Task Scheduler. If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
2. Click the Action menu, and then click Create Basic Task.
3. Type a name for the task and an optional description, and then click Next.
4. Select a schedule based on the calendar by clicking Daily, Weekly, Monthly, or One time, click Next; specify the schedule you want to use, and then click Next.
5. To schedule a program to start automatically, click Start a program, and then click Next.
6. Click Browse to find the program you want to start (your upload.bat file at C:\gradesfirst\upload.bat), and then click Next.
7. Click Finish.
Once you’ve installed cURL, you need to create an API key. (If you are already running automated uploads, you already have an API key. Do not change your API key without good reason, because you must also change the cURL command accordingly.)
- Download cURL. (see instructions for Windows machines above)
- Add GradesFirst’s IP addresses to your institution’s firewall whitelist as outlined above.
- (Optional) If you wish to receive import status e-mails, log into GradesFirst, click on the Admin tab, click on School Settings, and enter your e-mail address under “API Imports E-mail Address”. Click the Update Settings button to save.
- After logging into GradesFirst, click the Admin tab, and click "Create your API Key" (on the right).
- Copy the key provided; it will be referred to as <api-key>
- Use the following cURL statement for uploading:
curl -u <username>:<api-key> -F "data=@<file>" https://<environment>.gradesfirst.com/api/imports
<username> The user login of the user you want to do the import.
<api-key> The key that you received in step 3.
<file> The full local path and name of the file you want to upload.
<environment> The GradesFirst environment you are uploading to. "app" and "training" are the most frequent options with "app" being the production environment.
7. Run your command to upload the file.
Manual Imports inside GradesFirst
Perhaps the easiest way to import into GradesFirst is to simply upload your import file using the Admin panel.
- Log into GradesFirst, click the Admin Tab, and click on Data Import.
- Select the Term, choose the CSV file, and click Import My Selected File.
- Verify that the data appears correct, then click Data Sample is Valid.
- You will receive an email confirmation with the results of your import.
Imports Dashboard
The Imports Dashboard allows you to view all your current imports and a 7-day history of your imports. It displays imports that are currently running, imports that are waiting on the server queue, recent imports that were successful, and recent imports that had errors.
To open the imports dashboard:
- Log into GradesFirst
- Click on the Admin Tab
- Click the “Data Import” link in the Import/Data API section
Frequently Asked Questions
- Who owns the data that an institution uploads and inputs into the GradesFirst system?
Clients own their data contained within GradesFirst. This is clearly stated in the service agreement as well.
2. When classes are dropped from a student’s schedule, either through the data upload or GradesFirst interface, does GradesFirst mark classes as dropped or delete them from the student’s schedule entirely?
GradesFirst will mark the class as dropped so the user can tell which classes have been dropped during the term.
3. If a student that is currently in the GradesFirst system and is not included in a subsequent file upload, will the student’s record be affected in any way?
No. If a student is not included in the data import file, their record in GradesFirst will not be affected. Removing a student from GradesFirst requires action from a user.