SAM V1:import

From AMACWiki
Jump to: navigation, search

Overview

In order to import data into SAM a data extract/script will have to be created from the Institution Student Information or ERP (Enterprise Resource Planning) System.

Common Higher Education Student Information or ERP systems are:

  • Banner
  • PeopleSoft
  • DataTel
  • Colleague
  • Jenzabar

Please Note: For the remainder of this page, the Student Information or ERP System will be referred to as the ERP system.

There are two tables in SAM for which data can be imported from the ERP system: students and class schedules. The ERP system data extract should be a CSV file. The service provider should be able to run or receive the ERP system extracts and save the data to a local file. This local file would then be loaded by the service provider into SAM using the QuickBase import data feature.

Students

  • It is the responsibility of the ERP system programmer (or designated party) to write an extract of student demographic data from the ERP system to be loaded into SAM.
  • This extract should be formatted as either a CSV or tab-delimited text file.
  • Each row must contain a key field which makes each row unique. Most often, for the import of the student's demographic information, it is the student's ID number.
  • Only tagged or specified ERP system students should be included in the student extract, not all students. It is up to the ERP system programmer to determine how the individual student records will be tagged or specified. AMAC’s suggestion is to denote a new or unused field on the student record in the ERP system in which the service provider will input a numeric code. Only students with a value in this field greater than or equal to 0 would be included in the ERP system student extract. By using this method, the service provider can identify in the ERP system which students to extract, but the identifier within the ERP system is meaningless and does not identify the student as receiving disability services.
  • The following list is intended to only serve as a guide. AMAC will provide customized adjustments to allow customers to dictate fields and column names identified for importing.
  • Row 1 of the student extract file should be a list of column labels (header row). Below please find an example of what the header row could represent for an import of student demographics:
    • Please note, as expressed above, for the import of student demographics, most often the key field in this import is the student's ID#.
Column Label
A Student ID
B First Name
C Last Name
D Middle Name
E Academic Level
F Address
G City
H State
I Zip Code
J Phone
K Email
L Gender
M Ethnicity
N Date of Birth
O US Citizen
  • The extract should contain a row of data for each student record extracted. For each row of student data, the following data could be exported in the following order adhering to the following specifications:
Column Label Specifications
A Student ID The student's institutional ID number.
B First Name The student's first name.
C Last Name The student's last name.
D Middle Name The student's middle name.
E Academic Level The student's academic level as Fr, So, Jr, or Sr.
F Address The student's address.
G City The student's city.
H State The student's state formatted as the 2 digit state code.
I Zip Code The student's zip code formatted either as 5 digits or 5 digits - XXXX.
J Phone The student's phone number formatted as 10 digits without the characters ()- or white spaces.
K Email The student's email address.
L Gender The student's gender formatted as either Male or Female.
M Ethnicity The student's ethnicity.
N Date of Birth The student' date of birth formatted as MM-DD-YYYY.
O US Citizen A yes or no value denoting if the student is or is not a US citizen.
  • It is up to the ERP system programmer and service provider to determine how and when the student extract is run and delivered. It could be run on a weekly (or other established) schedule and the resulting file placed on a share drive accessible to the service provider, or it could be run by the service provider on demand.
  • AMAC will teach an institution representative how to import the generated ERP system CSV file into SAM using the secure web-based import utility within the SAM application. Using column A (Student ID), SAM will link the ERP system student row to a SAM student record and update the SAM student record. If no match is found then a student record is created in SAM from the ERP system student row data. For an overview of importing data into a QuickBase application, see Importing Data from Outside QuickBase.

Class Schedules

  • It is the responsibility of the ERP system programmer (or designated party) to write an extract of class schedule data from the ERP system to be loaded into SAM.
  • This extract should be formatted as either a CSV or tab-delimited text file.
  • Each row must contain a key field making each row unique. This requirement may involve creating a concatenated field combining the values of more than one field.
  • Only class schedule data for tagged or specified ERP system students should be included in the class schedule extract, not all class schedules. Additionally, only class schedule data for a specified term/year should be extracted. It is up to the ERP system programmer to determine how the individual student records will be tagged or specified and how to determine which term/year class schedules will be included. AMAC’s suggestion is to denote a new or unused field on the student record in the ERP system in which the service provider will input a numeric code. Only class schedules for students with a value in this field greater than or equal to 0 would be included in the ERP system class schedule extract. By using this method, the service provider can identify in the ERP system which students’ class schedules to extract, but the identifier within the ERP system is meaningless and does not identify the student as receiving disability services.
  • The following list is intended to only serve as a guide. AMAC will provide customized adjustments to allow customers to dictate fields and column names for importing.
  • Row 1 of the class schedules extract file should be a list of column labels (header row). Below please find an example of what the header row could look like for an import of student class schedules:
    • Please note, the column headings presented below DO NOT include a column for the unique key field. We will work with each institution to create this field and add it to each institutions instance of SAM.
Column Label
A Student ID
B Term
C Year
D Course
E Description
F Instructor
G Instructor Phone
H Instructor Email
I Location
J Monday
K Tuesday
L Wednesday
M Thursday
N Friday
O Saturday
P Sunday
Q Start Time
R End Time
S Credits
  • The values imported into each field are institution specific. AMAC will make adjustments in SAM to match each institutions preferences for each field. An import will run smoothly if the following order is followed as close as possible:
    • The fields, order and values are not set in stone as each institution may be different. AMAC will adjust as best possible.
Column Label Specifications
A Student ID The student's institutional ID number.
B Term The term of the class formatted as either Fall, Spring, Summer, etc.
C Year The 4 digit year of the course, i.e. 2009, 2010, etc.
D Course The name of the course.
E Description The description of the course.
F Instructor The instructor of the course.
G Instructor Phone The instructor's phone number formatted as 10 digits without the characters ()- and no white spaces.
H Instructor Email The instructor's email address.
I Location The course location.
J Monday Yes if the course meets on Monday else no.
K Tuesday Yes if the course meets on Tuesday else no.
L Wednesday Yes if the course meets on Wednesday else no.
M Thursday Yes if the course meets on Thursday else no.
N Friday Yes if the course meets on Friday else no.
O Saturday Yes if the course meets on Saturday else no.
P Sunday Yes if the course meets on Sunday else no.
Q Start Time The start time of the course formatted as HH:MM AM or PM.
R End Time The end time of the course formatted as HH:MM AM or PM.
S Credits The number of credits received for the course formatted as an integer.
  • It is up to the ERP system programmer and service provider to determine how and when the class schedule extract is run and delivered. It could be run at the beginning of the semester (or other established) schedule and the resulting file placed on a share drive accessible to the service provider, or it could be run by the service provider on demand.
  • AMAC will teach an institution representative how to import the generated ERP system class schedule CSV file into SAM using the secure web-based import utility within the SAM application. Using columns A (Student ID), B (Term), C (Year), and D (Course), SAM will link the ERP system class schedule row to a SAM class schedule record and update the SAM class schedule record. If no match is found then a class schedule record is created in SAM from the ERP system class schedule row data. For an overview of importing data into a QuickBase application, see Importing Data from Outside QuickBase.