Difference between revisions of "SAM V2:import"

From CIDI Wiki
Jump to navigation Jump to search
Line 1: Line 1:
In order to import data into SAM V2 a Banner data extract/script will have to be created.  There are two SAM tables that need to be submitted to the Banner IT professional:  [[SAM_V2:Students|students]] and [[SAM_V2:Class_Schedules|class schedules]].  The Banner data extract format can be either a [[Wikipedia:Comma-separated_values|CSV]]  file or a tab-delimited text file.  Ideally the IT professional should be able to run the Banner extract on demand and save the data to a local file.  This local file would then be loaded/updated by the IT professional using SQL/MySQL into SAM.
==Overview==


The Banner Student extract should contain a single row for each student.  The student extract would not contain a row for every student in Banner, so a mechanism must be in place within Banner to identify or flag disability services students, as only those students should be included in the extract.  
In order to import data into SAM V2 a Banner data extract/script will have to be created.  There are two tables in SAM for which data can be imported from Banner: [[SAM_V2:Students|students]] and [[SAM_V2:Class_Schedules|class schedules]].  The Banner data extract format can be either a CSV or a tab-delimited text file.  The service provider should be able to run or receive the Banner extracts and save the data to a local file.  This local file would then be loaded into SAM using a SQL procedure.


The Banner class schedule extract should contain a single row for each class of a student.  For example, if a student is taking three classes, there would be three rows of data, one for each class with the same student identifier.  The class schedule extract would not contain a row for every student class in Banner, so a mechanism must be in place within Banner to identify or flag disability services students, as only the class schedules of those students should be included in the extract.
==Students==
 
* It is the responsibility of the Banner programmer to write an extract of student data from Banner to be loaded into SAM. 
* This extract should be formatted as either a CSV or tab-delimited text file.
* Only tagged or specified Banner students should be included in the student extract, not all students.  It is up to the Banner 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 Banner 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 Banner student extract.  By using this method, the service provider can identify in Banner which students to extract, but the identifier within Banner is meaningless and does not identify the student as receiving disability services.  The number input in this denoted field on the student record in Banner is the student record ID # from SAM.  So, for example, if in SAM student John Doe has student record ID # 123, the service provider would input 123 in the denoted field on the student record in Banner.
* The first row of the student extract file should be a list of column labels.  The column labels should be exactly as follows:
 
{| class="wikitable" border="1"
|-
! Column !! Label
|-
| A || Student ID#
|-
| B || ID
|-
| C || First Name
|-
| D || Last Name
|-
| E || Middle Name
|-
| F || Academic Level
|-
| G || Address
|-
| H || City
|-
| I || State
|-
| J || Zip Code
|-
| K || Phone
|-
| L || Email
|-
| M || Gender
|-
| N || Ethnicity
|-
| O || Date of Birth
|-
| P || US Citizen
|}
 
* The extract should contain a row of data for each student record extracted.  For each row of student data, the following data should be exported in the following order adhering to the following specifications:
 
{| class="wikitable" border="1"
|-
! Column !! Label
|-
| A || Student ID# || The SAM student record # which is input by the service provider into the denoted field on the Banner student record.
|-
| B || ID || The student's institutional ID number.
|-
| C || First Name || The student's first name.
|-
| D || Last Name || The student's last name.
|-
| E || Middle Name || The student's middle name.
|-
| F || Academic Level || The student's academic level as Fr, So, Jr, or Sr.
|-
| G || Address || The student's address.
|-
| H || City || The student's city.
|-
| I || State || The student's state formatted as the 2 digit state code.
|-
| J || Zip Code || The student's zip code formatted either as 5 digits or 5 digits - XXXX.
|-
| K || Phone || The student's phone number formatted as 10 digits without the characters ()- or white spaces.
|-
| L || Email || The student's email address.
|-
| M || Gender || The student's gender formatted as either Male or Female.
|-
| N || Ethnicity || The student's ethnicity.
|-
| O || Date of Birth || The student' date of birth formatted as MM-DD-YYYY.
|-
| P || US Citizen || A yes or no value denoting if the student is or is not a US citizen.
|}
 
* It is up to the Banner 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.
* The service provider will import the generated Banner student CSV or TXT file into SAM using a SQL procedure.  Using column A (Student ID#), the SQL procedure will link the Banner student row to a SAM student record and update the SAM student record.
 
==Class Schedules==
 
* It is the responsibility of the Banner programmer to write an extract of class schedule data from Banner to be loaded into SAM. 
* This extract should be formatted as either a CSV or tab-delimited text file.
* Only class schedule data for tagged or specified Banner 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 Banner 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 Banner 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 Banner class schedule extract.  By using this method, the service provider can identify in Banner which students’ class schedules to extract, but the identifier within Banner is meaningless and does not identify the student as receiving disability services.  The number input in this denoted field on the student record in Banner is the student record ID # from SAM.  So, for example, if in SAM student John Doe has student record ID # 123, the service provider would input 123 in the denoted field on the student record in Banner.
* The first row of the class schedule extract file should be a list of column labels.  The column labels should be exactly as follows:
 
{| class="wikitable" border="1"
|-
! 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 extract should contain a row of data for each class schedule record extracted.  For each row of class schedule data, the following data should be exported in the following order adhering to the following specifications:
 
{| class="wikitable" border="1"
|-
! Column !! Label
|-
| A || Student ID# || The SAM student record # which is input by the service provider into the denoted field on the Banner student record.
|-
| 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 Banner 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.
* The service provider will import the generated Banner class schedule CSV or TXT file into SAM using a SQL procedure.  Using column A (Student ID#), SAM will insert the Banner class schedule row as a new record for the SAM student linked to the Banner student in the row.

Revision as of 10:29, 10 August 2009

Overview

In order to import data into SAM V2 a Banner data extract/script will have to be created. There are two tables in SAM for which data can be imported from Banner: students and class schedules. The Banner data extract format can be either a CSV or a tab-delimited text file. The service provider should be able to run or receive the Banner extracts and save the data to a local file. This local file would then be loaded into SAM using a SQL procedure.

Students

  • It is the responsibility of the Banner programmer to write an extract of student data from Banner to be loaded into SAM.
  • This extract should be formatted as either a CSV or tab-delimited text file.
  • Only tagged or specified Banner students should be included in the student extract, not all students. It is up to the Banner 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 Banner 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 Banner student extract. By using this method, the service provider can identify in Banner which students to extract, but the identifier within Banner is meaningless and does not identify the student as receiving disability services. The number input in this denoted field on the student record in Banner is the student record ID # from SAM. So, for example, if in SAM student John Doe has student record ID # 123, the service provider would input 123 in the denoted field on the student record in Banner.
  • The first row of the student extract file should be a list of column labels. The column labels should be exactly as follows:
Column Label
A Student ID#
B ID
C First Name
D Last Name
E Middle Name
F Academic Level
G Address
H City
I State
J Zip Code
K Phone
L Email
M Gender
N Ethnicity
O Date of Birth
P US Citizen
  • The extract should contain a row of data for each student record extracted. For each row of student data, the following data should be exported in the following order adhering to the following specifications:
Column Label
A Student ID# The SAM student record # which is input by the service provider into the denoted field on the Banner student record.
B ID The student's institutional ID number.
C First Name The student's first name.
D Last Name The student's last name.
E Middle Name The student's middle name.
F Academic Level The student's academic level as Fr, So, Jr, or Sr.
G Address The student's address.
H City The student's city.
I State The student's state formatted as the 2 digit state code.
J Zip Code The student's zip code formatted either as 5 digits or 5 digits - XXXX.
K Phone The student's phone number formatted as 10 digits without the characters ()- or white spaces.
L Email The student's email address.
M Gender The student's gender formatted as either Male or Female.
N Ethnicity The student's ethnicity.
O Date of Birth The student' date of birth formatted as MM-DD-YYYY.
P US Citizen A yes or no value denoting if the student is or is not a US citizen.
  • It is up to the Banner 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.
  • The service provider will import the generated Banner student CSV or TXT file into SAM using a SQL procedure. Using column A (Student ID#), the SQL procedure will link the Banner student row to a SAM student record and update the SAM student record.

Class Schedules

  • It is the responsibility of the Banner programmer to write an extract of class schedule data from Banner to be loaded into SAM.
  • This extract should be formatted as either a CSV or tab-delimited text file.
  • Only class schedule data for tagged or specified Banner 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 Banner 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 Banner 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 Banner class schedule extract. By using this method, the service provider can identify in Banner which students’ class schedules to extract, but the identifier within Banner is meaningless and does not identify the student as receiving disability services. The number input in this denoted field on the student record in Banner is the student record ID # from SAM. So, for example, if in SAM student John Doe has student record ID # 123, the service provider would input 123 in the denoted field on the student record in Banner.
  • The first row of the class schedule extract file should be a list of column labels. The column labels should be exactly as follows:
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 extract should contain a row of data for each class schedule record extracted. For each row of class schedule data, the following data should be exported in the following order adhering to the following specifications:
Column Label
A Student ID# The SAM student record # which is input by the service provider into the denoted field on the Banner student record.
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 Banner 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.
  • The service provider will import the generated Banner class schedule CSV or TXT file into SAM using a SQL procedure. Using column A (Student ID#), SAM will insert the Banner class schedule row as a new record for the SAM student linked to the Banner student in the row.