Import File Layouts

PowerSchool's External Applicant Interface program will load the data into standard PowerSchool ERP Human Resources tables. The file layouts are defined below and include processing notes and destination fields in the standard tables.

Basic Employee Data

The file layout for basic employee demographic information is shown below. Data imported from the file will be stored in the standard tables. The employees will be marked and processed as pending employees. The tables loaded in Human Resources include: employee, person, empl_races, and payroll.

#	Data	Format	Required Y-Yes N-No	Description / Notes	Defaults for blank values and other Processing Notes	Destination TableName.FieldName
1	Employee Identifier	INTEGER (see Note 2)	N	BLANK	See Interface Setup for more information.	System generated
2	Social Security Number	CHAR(11)	Y	Format 999-99-9999	Default set by profile. Convert format 999999999 to 999-99-9999	employee.ssn
3	Last Name	CHAR(30)	N			employee.l_name
4	Suffix	CHAR(30)	N			employee.name_suffix
5	First Name	CHAR(30)	N			employee.f_name
6	Middle Name	CHAR(30)	N			employee.m_name
7	Address Line 1	CHAR(30)	N			employee.addr1
8	Address Line 2	CHAR(30)	N			employee.addr2
9	City	CHAR(20)	N			employee.city
10	State Code	CHAR(2)	N			employee.state_id
11	Zip Code	CHAR(10)	N	Check format 9999 or 99999-9999		employee.zip
12	Home Phone	CHAR(12)	N	Format 999-999-9999	Program will attempt to format non-standard formats	person.home_phone
13	Cell Phone	CHAR(12)	N	Format 999-999-9999	Program will attempt to format non-standard formats	person.cell_phone
14	Personal Email	CHAR(50)	N			employee.personal_email
15	Previous Name	CHAR(30)	N	Not Available		employee.prev_lname
16	Gender	CHAR()	Y	M/ F	M	person.sex
17	Ethnicity	CHAR(1)	Y	H – Hispanic N – None	N	person.ethnic_code
18	Race	CHAR(1)	Y	See Race Code Conversion Chart below	Default set by profile. .	person.race empl_races.race_code Note: Race Reference table codes must match in order to add Race records
19	Handicapped	CHAR(1)	N	Y/N	N	person.hand
20	Status	CHAR(1)	Y	Site-defined code verified	Default set by profile	person.status
21	Start Date	MM/DD/YYYY	Y	First day worked	Dates can be overridden on input screen. If blank and no override, the current run date is used.	Employee.hire_date
22	Hire date (original)	MM/DD/YYYY	Y	Hire date		employee.orig_hire
23	Employee Department	CHAR(8)	Y	Site-defined code verified	Default set by profile	employee.home_orgn
24	Check Location	CHAR(4)	Y	Site-defined code verified	Default set by profile	payroll.chk_locn
25	Base location (for security and printing)	INTEGER	Y	Site-defined code verified	Default set by profile	employee.base_locn
26	EEO Group	CHAR(1)	N	4 – Municipalities 5 – School districts N – None	N	person.eeo_group
27	EEO Reported	CHAR(2)	N	EEO reported codes 1-21	blank	person.eeo
28	Pay Frequency	CHAR(1)	Y	A– Annual, B– Bi-weekly, M– Monthly, S-Semi-monthly, W– Weekly, X/Y/Z	Default set by profile	payroll.pay_freq
29	Part/Full Time status	CHAR(1)	Y	P – Part-time F – Full-time	Default set by profile	person.part_time
30-40	User-defined	CHAR(30)	N	Reserved for future use		user_ref.*

Note 1: The employee/applicant number in the basic employee data and education file must be the same value. If auto-numbering is enabled in PowerSchool ERP, the employee identifier could be the applicant number. If you are assigning the employee number in the third-party application, these fields could contain the employee number you will be using in PowerSchool ERP.

Note 2: If the site will be auto-assigning employee numbers in PowerSchool ERP, the employee identifier field does not need to be an integer but can contain alphanumeric characters.

Sample Basic Employee Data Export Record

|999-99-9999|LAST|SUFFIX|FIRST|MIDDLE|123 ANY STREET||ANY CITY|PA|12345|555-444-3333|888-777-6666|first.last@email.com||M|N|W|N|ACT|4/1/2014|4/1/2014|ADMIN|ADMIN|001|N||S|F||||||||||||

RACE CODE CONVERSION CHART

The following codes are accepted in race code fields in the person and empl_race tables Human Resources:

Racial/Ethnic Group	PowerSchool ERP code
Caucasian	W
Black of African-American (not Hispanic or Latino)	B
Hispanic or Latino	H
2 or more races (not Hispanic or Latino)	O
Asian (not Hispanic or Latino)	A
Native Hawaiian or other Pacific Islander (not Hispanic or Latino)	A
Native American	I
Refused to self-identify racial/ethnic group (Note: PowerSchool ERP currently does not have this designation, but plans to add this in the future)	O

Education Data

A sample file layout for employee education information is shown below. The file uses an ASCII pipe-delimited format. Data imported from this file will be stored in the standard Activity Tracker table: pat_act.

#	Data	Format	Required Y-Yes N-No	Description / Notes	Defaults for blank values and other Processing Notes	Destination TableName.FieldName
1	EMPLOYEE IDENTIFIER	INTEGER (See Note 1)	Y	Applicant ID	See Interface Setup for more information.	pat_act.empl_no
2	RECORD TYPE	CHAR(15)	Y	"EDUCATION" for all records		pat_act.sit_type
3	SCHOOL NAME	CHAR(50)	N			pat_act.sit_data1
4	FROM DATE	CHAR(50)	N	Dates in sample data are MM/YYYY		pat_act.sit_data2
5	TO DATE	CHAR(50)	N	Dates in sample data are MM/YYYY		pat_act.sit_data3
6	MAJOR	CHAR(50)	N			pat_act.sit_data4
7	MINOR	CHAR(50)	N			pat_act.sit_data5
8	DEGREE	CHAR(50)	N			pat_act.sit_data6
9	DEGREE DATE	CHAR(50)	N	Dates in sample data are MM/YYYY		pat_act.sit_data7
10- 14	USER DEFINED 1	CHAR(50)	N	For Future Use		pat_act.sit_data8 - data12

Note 1: If the site will be auto-assigning employee numbers in PowerSchool ERP, the employee identifier field does not need to be an integer but can contain alphanumeric characters.

Sample Education Data Export Records

1|EDUCATION|ALABAMA|||ECON|ACCTG|BA|08/30/1990||||||
1|EDUCATION|ALABAMA|||ECON|ACCTG|BA|08/1990||||||