Import Tool

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 on 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.

Import areas available by security resource

The Import Area list contains the import areas available to you. Your assigned Import Tool resources determine which areas are displayed and which actions you can complete.

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 resource 5031 to the user so the Import Tool appears 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

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 before adding the job in the Import Tool.

• Log in to PowerSource, then select a template for the import area and the 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 to table requirements. Required fields are validated during the import process to ensure data integrity.

  • Schema validation verifies data types, character limits, and other requirements, as if a user were manually entering data on a software page.

Add import

From the Import Tool page, select Add Import. Security resources control the Add Import button and the Import Area list. Users must have at least one May Add resource.

Select file

1. Select the Import Area. User security resources restrict the list. Lists may appear different from one user to another.

2. Enter an Import Title. The title must be unique and should convey some information about the import data.

3. Select a Date Format. Dates in the spreadsheet may use a hyphen, forward slash, or period but must have digits in the order MMDDYYYY.

4. 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.
       For instructions on preparing a local import file, refer to Prepare a local import file.

5. Select Next.

Prepare a local import file

Use a comma-separated values file that meets the following requirements before you upload it:

If the Import Tool rejects the file, correct the reported issue and upload the file again.

Choose import handling

Select the action that determines how the Import Tool processes rows in the import file. You can select Insert, Update, or both actions.

To choose import handling:

1. On the Select File page, in Import Handling, select Insert, Update, or both.

2. Continue with the remaining fields on the page.

Map import fields

1. You may select from the Choose Mapping Template list. If you or other users have previously saved templates, they appear in this list.

2. In the Imported Fields section, Field Descriptions from the import file are matched to database tables, if possible, and a default match is displayed.

3. If the Field Description for a column in your file is blank, 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.

4. 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 saves the template to the Choose Mapping Template list.

5. Click Next.

Set required values

1. If there are fields marked as required in the interface that are missing from your file, each is listed. Otherwise, a message indicates that all required fields have been mapped and that you may continue.

2. 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, edit your file to add the field as a column with the appropriate data, and then restart the Add Import process.

Schedule an import

Choose a repeat option that matches how often the Import Tool must process the source file. Scheduled imports require a start date, a run time, and an end-schedule selection.

Use Preview Mode for a one-time import

Preview Mode validates a one-time import without updating database tables. The Import Tool records the preview import in Recent Imports so that you can review its results.

Preview Mode is available only when you choose Run Now in the Repeat list.

To validate an import with Preview Mode:

1. Complete the Select File, Map Import Fields, and Set Required Values pages.

2. On the Schedule Import page, choose Run Now in the Repeat list.

3. Select Use Preview Mode.

4. Select Schedule Import, then select Schedule to confirm the import.

5. On the Recent Imports tab, select the import title to review the results.

6. Review the summary results for each row. A preview summary can report whether a row would insert or update data, but it does not apply changes.

The Import Tool displays the message "Preview only: No changes processed. Success messages indicate if import would insert new records or update existing records." on the import details page for a preview import.

After you resolve the reported errors and warnings, create a new import without Preview Mode to apply the data.

Recent Imports

The Recent Imports tab lists import jobs that have run. It provides information about the import request, including whether the data was applied successfully.

• User resources restrict this tab.

• Users may have more than one resource assigned, individually or through an assigned role.

• The resource package 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

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.

• User resources restrict this tab; it is not available to users with only 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 resource package 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

Validations

Imports execute standard data validations before importing. Validations maintain data integrity and prevent importing data errors. For example, if a field is marked as required in the software, it must be included 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 lists errors that prevent data from being imported 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, dataset validation is performed to ensure the data's integrity and validity.