Import Tool

PowerSchool ERP 23.10 and later

The Import Tool provides a way to enter and update large volumes of data in PowerSchool ERP. A system administrator with specific roles or resources can perform a one-time import, or schedule an import to run on a schedule where the selected source import file is updated in a cadence, for example a file that is provided by a third-party external application that uses the same file name each time it deposits a new file. Only .csv files can be imported. The maximum file size for a local file source is 2MB.

Imported data adds or overwrites existing data in application tables of the following modules and areas:

Module	Import areas	Topics
Fund Accounting	Fund Accounting Ledgers	Account List Expenditure Ledger Expenditure Budget Ledger Fund Accounting Organization Chart General Ledger Revenue Ledger
Fund Accounting	Fund Accounting Reference Table	Account List Additional Vendor Address Alternate Vendor Address Batch Vendors Bank Account Information Fund Accounting Organization Chart Payer List Vendor List Vendor User Defined Windows
Fund Accounting	Vendor Information	Additional Vendor Address Alternate Vendor Address Batch Vendors Vendor List Vendor User Defined Windows
Purchasing	Purchase Reference Table	Commodity Codes Purchasing Defaults Shipping Codes
Human Resources	Batch Position Control (may not be listed if the database is not using Position Control)	Batch Position Batch Position Certification Batch Position Charging Batch Position Project Charging Batch Position Qualifications Batch Position Requirements
Human Resources	Batch Position Control Employee Assignments (may not be listed if the database is not using Position Control)	Employee Employee Race Payroll Person
Human Resources	Employee Information	Employee Employee Assignment Employee Certificate Employee Degree Employee License Employee Race Global SSO Employee Mapping Payroll Person Personnel Activity User Defined Information
Human Resources	Human Resources Reference Table	Assignment Codes Bank Codes Departments Education Degree Subjects Employee Defined Windows Future Job Classes Job Classes Locations User Defined Codes
Human Resources	Pay Rates without Positions (Only available for customers during implementation)	Pay Rate Pay Rate Distribution Pay Rate Project Distribution
Budget Preparation	Budget Preparation Ledgers	Budget Account List Budget Organization Chart
Benefits	Benefit Information	Benefits Benefits Effective Date Changes Dependents Employee Information
System Administration	Advanced Workflows	Workflow Applications Workflow Groups Workflow Roles
System Administration	User Security	User Access User Views Users

Menu path

From the System Administration menu, select Administration. From the Imports & Exports menu, select Import Tool.

Security resources

Access and permissions for the Import Tool follow standard PowerSchool ERP authorization, and user-assigned resources or roles determine the menu items that appear for a user and how the user can interact with the pages.

Assign a user resource 5031 - May Run Import Tool for the Import Tool to appear on the System Administration menu. Additional resources control user interaction. Assign resources directly or in a role assigned to the user.

Format of Import Tool resources

Field	Description
Package	Aligns with the module or function where the data will be imported. For example, HRM - Human Resources, FAM - Fund Accounting, SEC - User Security
Subpackage	The Subpackage for resource codes related to the Import Tool is Import.
Function	Aligns with standard resource formatting. Function is 0 (zero) for Super User, System Administrator, and Supervisor level resources. Otherwise it matches the resource code.
Privilege Code	Aligns with standard resource formatting. 1: Super User 2: System Administrator for Import 3: Supervisor for Import 5: User for Import User-level resources are separated into add and read-only (view). If a user only has user-level read-only (view) resources, the Scheduled Imports tab does not appear.

Preliminary actions

To use a file from an SFTP source:
- SFTP must be configured. Enter a Support case or consult with TSG for assistance.
- The file must be uploaded to the SFTP server prior to adding the job in the Import Tool.
Log in to PowerSource and select a template for the import area and objects to be imported. These templates provide guidance with column headers that align with the fields on the software pages.
- Each import area has defined template files that map with table requirements. Required fields are validated in the import process to determine the data integrity.
- Schema validation confirms data types, character limitations, and other requirements in the same manner as if a user were to manually enter data on a software page.

Add import

From the Import Tool page, select Add Import. The Add Import button and the Import Area list are controlled by security resources. Users must have one or more May Add resources.

Select file

Select the Import Area. The list is restricted by user security resources. Lists may appear different from one user to another.
Enter an Import Title. The title must be unique and should convey some information about the import data.
Select a Date Format. Dates in the spreadsheet may use a hyphen, forward slash, or period but must have digits in the order MMDDYYYY.
Select the location of the source file.
- SFTP: The file is fetched from the identified SFTP server.
  - Select a Server. If none are listed, SFTP configuration has not been completed.
  - Enter the File Location and Name. This should include the SFTP file directory path and file name, for example: /Import/abc.csv.
  - Select Delete Source File if the source file should be deleted after a successful import.
- Local File: The file is fetched from your computer or a server that you access.
  - Drag and drop a file from a folder or select Upload Local File to navigate to the file, then select Open. It must be a .csv file.
  - The Directory field is read-only and displays the path where the file will be located after it is uploaded.
Select Next.

You may choose to delete a source file when the file name is used in a repetitive manner. For example, a third-party attendance file may always use the same file name and the file must be deleted so the file name can be used repeatedly.

Import Handling

In the menu, there are two options to select from

Import Handeling	Details
Insert	Import tool creates new records. Blanks in required fields generate errors.
Update	Import Tool validates key data to match existing records. Allows blanks in required fields. Overwrites fields with data.

Map import fields

You may select from the Choose Mapping Template list. If you or other users have previously saved templates, they appear in this list.
In the Imported Fields section, Field Descriptions from the import file are matched to database tables, if possible, and display a default match.
If the Field Description is blank for a column in your file, select the appropriate item from the list. The list is controlled by the Import Area that you selected. The Table Field populates automatically based on the Field Description selection. The Table Field identifies where the data values will be entered or updated in the records.
If desired, after entering all Field Descriptions, you may click Save Mapping Template and enter a name for future use. This retains the mapping selections you made and the template is saved to the Choose Mapping Template list.
Click Next.

Set required values

If there are fields that are marked as required in the interface and are missing from your file, each one is listed. Otherwise, a message indicates that all required fields are mapped and you may continue.
Enter a value for each required field. The value entered will be imported for all records in the file, even though the file did not contain the data element. If records need unique or varied data for an item displayed on the Set Required Values page, exit the process and edit your file to add the field in a column with the appropriate data, then restart the Add Import process.

Schedule import

Repeat	Description	Settings	Preview Mode
Run Now	Processes the import immediately and only one time. There is no recurrence. Use for one-time data updates or to preview a repeating file for errors prior to scheduling recurrence.	Not applicable	Yes. Review the potential import and identify potential errors without changing any table data in the software.
Daily	Sets an import on a daily schedule at a specific time.	Every X days, where X is an integer less than 7. Start Date: Date of the first import in the series. Run Time: Enter the time of day when the import should run. Ending: End on a specific date. End after a specific number of occurrences. Run until the job is made inactive.	No
Weekly	Sets an import on a weekly schedule on specific days and at a specific time.	Every X weeks, where X is an integer less than 4. Days: Select one or more days of the week for the import to run. You may select all 7 days. Start Date: Date of the first import in the series. Run Time: Enter the time of day when the import should run. Ending: End on a specific date. End after a specific number of occurrences. Run until the job is made inactive.	No
Monthly	Sets an import on a monthly schedule on a specific day of the month and at a specific time.	Day of the Month: Enter the day of the month when the import should run. For example, 15 would be the 15th of each month. Consider that February has fewer dates. Start Date: Date of the first import in the series. Run Time: Enter the time of day when the import should run. Ending: End on a specific date. End after a specific number of occurrences. Run until the job is made inactive.	No

Recent Imports

The Recent Imports tab lists import jobs that have run. It provides information about the import request, including the success or failure of data that was applied.

This tab is restricted by user resources.
Users may have more than one resource assigned, individually or through an assigned role.
The package of the resource controls the list. For example, if a user only has resource 23004 - May View Employee Information Import, the Recent Imports tab list is restricted to recent imports for that category. The list does not display recent imports for the Fund Accounting module, or any other area of the software.

Summary report

Select an import title on the Recent Imports tab to display a summary report. Click the three-dot menu and select Download Summary Report to create an Excel file if desired. You can also download the summary report to Excel from the list page. Select Download, then Summary Report.

Refer to Recent Imports Fields and Descriptions for details.

Import file (Excel)

From the Recent Imports list page or Summary Report page, select the three-dot menu, then Download Import File (Excel) to download the original file. Review and correct errors, and if it is a recurring import, find the title on the Scheduled Imports page and upload the replacement file. This option does not appear for users who only have user-level read-only (view) resources. Access to the Scheduled Imports tab is restricted and may not be available.

Recent Imports fields and descriptions

Field	Description
Status	Indicates the status of the import. Completed: All data from the import was applied as either an insert or update. Failed: The import failed as a result of a communication error or no records could be applied. Partially Completed: Some records imported and applied successfully, other records could not be applied.
Import Title	Title entered by the user who performed the import. On the Summary Report page, the title is in the page header.
Import Area	Identifies the location where the imported data should be applied.
Successes	A number of records that successfully imported and inserted or updated data.
Errors	A number of records that failed to import. For example, a row of employee data with no Employee ID should create a pending employee record. If the Social Security number exists on another record, it fails to insert a new record.
Start Date/Time	The date and time that the import process started.
Run Time	The length of time the import took to process.
Requester	The name of the person who performed the import.
Download	Download the imported file to check for errors. Download a summary report that provides status, row number from the import file, and messages to indicate whether data was inserted, updated, or the reason for the failure.

Scheduled Imports

The Scheduled Imports tab lists import jobs that are scheduled to run in the future. It provides information about the nature of the import request.

This tab is restricted by user resources and is not available to users who only have user-level read-only (view) resources.
Users may have more than one resource assigned, individually or through an assigned role. Super User, System Administrator, and Supervisor resources provide access to the Scheduled Imports tab.
The package of the resource controls the list. For example, if a user only has resource 23003 - May Add Employee Information Import, the Scheduled Imports tab list is restricted to future imports for that category. It does not display future imports for the Fund Accounting module, or any other area of the software.

Scheduled Imports fields and descriptions

Field	Description
Status	Indicates if an import is active or has encountered an error and will not run. If the status is In Progress, the import cannot be modified. Users cannot upload a new file for an import in progress.
Import Title	Title of the import, entered by the user who scheduled the import.
Import Area	Identifies the location where the imported data should be applied.
Next Run	The next scheduled time that the import will be run.
Source	Indicates whether the user selected a local file or SFTP source.
Requester	The name of the person who performed the import.
Options	Options depend on the Status. Error or Active: Upload File: Upload a replacement file. This may resolve an Error status, or can replace the existing file for the next recurrence if it is a local file. Deactivate: Deactivates the import with Error status. Deactivated: Delete Import: Removes the scheduled import from the queue and from the list. Activate: Re-activate a deactivated import recurrence.

Validations

Imports execute standard data validations before importing. Validations maintain data integrity and prevent importing data errors. For example, if a field is marked required in the software, the field is required in the import. If an import file is missing cell data for a required field, the wizard presents an opportunity to enter a static value. The value entered on this page applies to all imported records. The Summary Report contains a list of errors that prevent data from importing successfully.

Source file validations

File format: The source file must be in Comma-Separated Values .csv file format.
Empty file validation: If the source file contains no data, a message appears.
Empty row handling: If the source file contains rows that have only space characters or no data, the rows are skipped in the import process.
Dataset validation: After the source file is successfully imported, a dataset validation is performed to ensure the integrity and validity of the imported data.