Load Patrons

From Staffwiki

(Difference between revisions)
Jump to: navigation, search
m (To extract data from Banner® in SIF format)
Line 29: Line 29:
# Extract the data
# Extract the data
## '''sqlplus''' <sql_username>
## '''sqlplus''' <sql_username>
-
## '''start sif_student2.sql'''
+
## '''start sif_student.sql'''
-
## Enter value for file name: '''<output_file_name>'''
+
## Enter value for file name: '''<student_output_file_name>'''
## Wait until the extraction and creation of the SIF file is completed
## Wait until the extraction and creation of the SIF file is completed
## '''!''' Type a single exclamation point and press Enter
## '''!''' Type a single exclamation point and press Enter
-
# Retrieve the data. Use a Secure Shell&reg; program on Windows&reg; or Linux. The support staff can install this program for you.  You will use this to transmit files over a secure stream of data so that third parties cannot retrieve the information. The file will be placed at your current local attach point.
+
# Retrieve the student data  
 +
##Use a Secure Shell&reg; program on Windows&reg; or Linux to move data with privacy. The support staff can install this program for you.  You will use this to transmit files over a secure stream of data so that third parties cannot retrieve the information. The file will be placed at your current local attach point.
## Example if on Linux or UNIX
## Example if on Linux or UNIX
### '''sftp <username>@prod.csu.edu'''
### '''sftp <username>@prod.csu.edu'''
### Supply <password> when prompted
### Supply <password> when prompted
-
### '''get <output_file_name>'''
+
### '''get <student_output_file_name>'''
### '''exit'''
### '''exit'''
 +
# Log in to the UNIX system as before
 +
## Via a Windows Secure Shell Application, choose the proper profile.
 +
## Via a UNIX or Linux system, type &ldquo;'''ssh <username>@prod.csu.edu'''&rdquo;. You will be prompted for the UNIX access <password>
 +
# Log in to Banner
 +
## Type '''PROD'''
 +
## '''Login:''' <username>
 +
## '''Password:''' <password>
 +
## Optionally use '''ls''' to list files
 +
# Extract the data
 +
## '''sqlplus''' <sql_username>
 +
## '''start create_employees.sql'''
 +
## Enter value for file name: '''<student_output_file_name>'''
 +
## '''start employees_master.sql'''
 +
## ???
 +
## Wait until the extraction and creation of the SIF file is completed
 +
## '''!''' Type a single exclamation point and press Enter
 +
# Retrieve the non-student data
 +
These are records for staff and faculty employees of the university
 +
### ''' sftp <username>@prod.csu.edu'''
 +
### Supply <password> when prompted
 +
### '''get <employee_output_file_name>'''
 +
### '''exit'''
# Post-process the records
# Post-process the records
## Adjust patron codes as needed.  As of August 27, 2008, the PR, DR, and CP codes must be changed on each record to GR.  Use vi or EMACS (preferable) to do this
## Adjust patron codes as needed.  As of August 27, 2008, the PR, DR, and CP codes must be changed on each record to GR.  Use vi or EMACS (preferable) to do this

Revision as of 16:32, 15 September 2008

Contents

Loading Patron Records

Reference Documents

  1. Batch Loading Patron Data into I-Share Voyager Databases.
  2. Patron Record Standard Interface File (Annotated)

Introduction

The "patron load" is a recurring process which must take

expiration of records
expiration of patrons
change of patron status
avoidance of duplication

into account.

To extract data from Banner® in SIF format

You must have a Banner account appropriate to this task. Discuss the requirements with Helen Jackson of ITD.

  1. UNIX access username and password
  2. SQL access username and password. The sql_username may be the same as the UNIX login username.

User supplied data is indicated in bold.

  1. Log in to the UNIX system
    1. Via a Windows Secure Shell Application, choose the proper profile or use Quick Connect as shown here.Image:QuickConnectScreen.jpg
    2. Via a UNIX or Linux system, type “ssh <username>@prod.csu.edu”. You will be prompted for the UNIX access <password>
  2. Log in to Banner
    1. Type PROD
    2. Login: <username>
    3. Password: <password>
    4. Optionally use ls to list files
  3. Extract the data
    1. sqlplus <sql_username>
    2. start sif_student.sql
    3. Enter value for file name: <student_output_file_name>
    4. Wait until the extraction and creation of the SIF file is completed
    5. ! Type a single exclamation point and press Enter
  4. Retrieve the student data
    1. Use a Secure Shell® program on Windows® or Linux to move data with privacy. The support staff can install this program for you. You will use this to transmit files over a secure stream of data so that third parties cannot retrieve the information. The file will be placed at your current local attach point.
    2. Example if on Linux or UNIX
      1. sftp <username>@prod.csu.edu
      2. Supply <password> when prompted
      3. get <student_output_file_name>
      4. exit
  5. Log in to the UNIX system as before
    1. Via a Windows Secure Shell Application, choose the proper profile.
    2. Via a UNIX or Linux system, type “ssh <username>@prod.csu.edu”. You will be prompted for the UNIX access <password>
  6. Log in to Banner
    1. Type PROD
    2. Login: <username>
    3. Password: <password>
    4. Optionally use ls to list files
  7. Extract the data
    1. sqlplus <sql_username>
    2. start create_employees.sql
    3. Enter value for file name: <student_output_file_name>
    4. start employees_master.sql
    5.  ???
    6. Wait until the extraction and creation of the SIF file is completed
    7. ! Type a single exclamation point and press Enter
  8. Retrieve the non-student data

These are records for staff and faculty employees of the university

      1. sftp <username>@prod.csu.edu
      2. Supply <password> when prompted
      3. get <employee_output_file_name>
      4. exit
  1. Post-process the records
    1. Adjust patron codes as needed. As of August 27, 2008, the PR, DR, and CP codes must be changed on each record to GR. Use vi or EMACS (preferable) to do this
  2. Re-examine the file for proper structure
    1. Remove leading "garbage" record(s)
    2. Check the alignment of fields
      1. emacs <filename>
      2. M-x line-number-mode
      3. M-x column-number-mode
      4. Examine several records at the beginning, middle, and end of the file by placing the cursor on each field and making certain that the offset matches the EMACS offset as indicated in the SIF layout document on the right margin.
    3. It is useful to sort the file based on patron identification
      1. sort --key=21,45 <filename> > <filename.sorted>
    4. Remove duplicated records within the file
      1. uniq < <filename.sorted> > <filename.uniq>
    5. Use diff to compare the two (optional)
      1. diff <filename.uniq> <filename.sorted>
    6. Delete records with SSN data rather than UID data
      1. The sort will have placed SSNs in distinct places within the file and clustered according to the SSN value. This will be at the beginning of the file in most cases.
  3. Re-examine file <filename.uniq> for field-level issues using EMACS in column-number mode and checking http://libstaff.csu.edu/lists/web/howto/PRSIF.pdf
    1. Clear SSN's as needed
    2. Translate or re-map Patron Group Codes
      1. Use EMACS M-% to locate (without quotes) " DR ", " PR ", and " CP " and alter them to " GR ". Use ! to complete the replacements without reprompting.
  4. Transmit the file to CARLI, noting the file name for entry in the WRO.
      1. sftp csuftp@reports.carli.illinois.edu
      2. Enter <password> when prompted
      3. put <filename.csu.students.yyyymmdd.uniq>
      4. exit
  5. Generate a Patron Load Work Request Order (WRO)at the site http://auth.carli.illinois.edu/wro/cgi/wro.cgi This is done according to CARLI expectations
  6. Examine the WRO diagnostics on files placed on reports.carli.illinois.edu. Retrieve the files by using the GET command in sftp. The filenames involved will have been sent to the email addresses associated with the Work Request Order (WRO).
    1. sftp csuftp@reports.carli.illinois.edu
      1. Enter <password> when prompted
    2. get <filename>
    3. exit
  7. Repair the file as needed by repeating the above
  8. Verify WRO submission for full records by replying to the WRO. You may allow it to elapse or you may choose to withdraw it, depending upon an analysis of the information in the diagnostics and log files. CARLI supplies documentation of the procedures at a link embedded within the WRO report pages.
  9. Notify appropriate personnel of further processing needed
Personal tools