Home   >   Training   >   Application Administrator   >   Staff Data Import

Staff Data Import


This article provides an overview of how to import staff data in UC OATS. The information provided for staff will drive the following system functionality: 1) routing and approval workflow, and 2) reporting. The staff data can be updated as often as needed using the UC OATS data import feature or by editing the staff member's individual record.


Data Preparation

To prepare for the staff data import:

  1. Download the Staff Data Import Template.
    Please note:
    • The staff data import sheet is a flat file.
    • Only one row of data should apply to a single staff member.
  2. Review the staff data elements description below and complete the Staff Data Import Template. Once the file is complete, save it as a "Text (Tab delimited) (*.txt) file or CSV (Comma delimited) (*.csv).

Staff Data Elements Description: (Note, Scroll to the right to see the full table)

Data Element Name Description Max size Required (Y/N) Accepted Format Example Note UCPath_Entity UCPath_Attr

UCpath_
employee_id

UCPATH employee ID 

500 characters Y Numeric 12345678      

emp_lname

Last name of staff member

50 characters Y

Text; spaces, hyphens, and apostrophes accepted

Brown; 
Brown Smith; 
Brown-Smith;

 

PS_NAMES

LAST_NAME

emp_fname

First name of staff member

50 characters Y

Text; spaces, hyphens, and apostrophes accepted

Mary;
Mary Ann;
Mary-Ann
 

 

PS_NAMES

FIRST_NAME

emp_mname

Middle name of staff member

50 character N

Text; spaces, hyphens, and apostrophes accepted

John;
John Joseph;
John-Joseph

 

PS_NAMES

MIDDLE_NAME

emp_email

University email address used by the user. Email addresses are used for automated emails from UC OATS. This is limited to one email address per user.

100 characters Y

Email address

user@
campus.edu

Limited to one email per staff member

PS_EMAIL_ADDRESSES

EMAIL_ADDR

dept_code

A unique ID number that does not change over the lifetime of an organizational unit. This ID will serve as a unique identifier in UC OATS to identify the organizational unit and associate incoming data.

30 characters Y

Alphanumeric

1400, must be unique within a campus. 

This field will be used to map staff members to the appropriate department, division, school, or organized research unit (ORU).

PS_DEPT_TBL

DEPTID

add_role

Roles to be assigned to this staff

4000
characters

Y = either add_roles or remove_roles

Comma separated values (valid roles are alternate approver, approver, auditor, faculty status change, historical data manager, reviewer, stock evaluator)

alternate approver, historical data manager

Alternate approvers role will automatically assign the approver role. Historical data manager role will automatically assign the reviewer role.

   
remove_role Roles to be removed from this staff  4000
characters
Y = either add_roles or remove_roles Comma separated values (valid roles are alternate approver, approver, auditor, faculty status change, historical data manager, reviewer, stock evaluator) approver, stock evaluator Alternate approvers role will automatically assign the approver role. Historical data manager role will automatically assign the reviewer role.    

Import Staff Data File

To upload the staff data import file:

  1. Click on the "App Admin" button to open the Application Administrator workspace.Application Administrator workspace page

  2. Make sure you are in the correct fiscal year. You can move forward and back through fiscal years by clicking the arrows.
  3. Select the "Data Import" tab and open the "Staff Data Import" section.
  4. Click on the "Upload Staff Ingest" button.
  5. Click the "Choose File" button and locate your data file. Then click "Upload".
    uploading a file
  6. The system will process your file and one of the following statuses will appear.
    • Invalid - There is an issue with the file. (1) The file is not in "Text (Tab delimited) (*.txt) file or CSV (Comma delimited) (*.csv)., (2) there is no data in the file, (3) file columns have been removed.
    • Valid and processed / Complete Successful- The file was successfully uploaded and processed. The changes will be reflected in UC OATS.
    • Complete with Exceptions - The system was unable to process some of the records. To resolve data errors follow steps 7-10 to fix the issue.
  7. Click on the file name to view the "Ingest File Details" log. 
  8. The "Ingest File Details" log will show you the data upload details. If the file status is "Complete with Exception", click the "Download" button to download the data set that did not get processed.
    faculty ingest file details
  9. Open the downloaded data file in Excel. The file will show only records with errors. The reason for the errors will be in the errors column. 
  10. Fix the errors, and upload the data file. 

 

Last Modified: 2023-06-05 12:04:07.266823-07