This article explains how to use Import Spreadsheet routine as a powerful tool that allows to add various payroll related information to the batch of employees.
1. Introduction
Import Spreadsheet tool allows to upload various payroll related information into the system for a batch of persons with the help of a simple xls / xlsx file.
It allows to import:
- Person's Payroll information (mostly that is added under Payroll → Constants tab and Payment tab).
- Payroll Entries (in current and future periods).
- Additional Payment Info for those Entry Codes.
To access the routine, go to Payroll tab > Batch Entry as shown in the below screenshot.
To access this tool access rights should be set to Y for the routines 766 Spreadsheet in Adonis Control Center (ACC).
2. Import setup
Once you open the Import Spreasdsheet window you will see the following dialogue that allows you to run the import.
However, before you start you need to setup and configure the import parameters.
The setup can be found under Tools -> Import -> Spreadsheet -> Properties button.
Here you can setup and configure the import details by following the below recommendations.
2.1. Add transactions
Once you click on Properties, you will see the following setup screen.
- Primary Worksheet Name – is a free input text field where user can define the name of the source tab from excel template (.xls / .xlsx) to be used for import.
- Batch Start / Batch End – numeric fields where identify first & last rows containing import information within the excel file (.xls / .xlsx). Only the records in this range will be processed. The row with headers should not be included.
- Primary Column – here you define the letter of the column from the excel file which will be used as the primary key value. Usually it is a column with PIN.
The field is mandatory for input. If there is no value in this column, the transaction is stopped even if the Batch End is not reached.
- Add / Delete buttons – are used to define the set of transactions that will be performed during import. Usually only one transaction is defined per each import.
- Transaction nr - Integer value identifying the transaction number. The import utility will import transaction by transaction, the transaction number is used to group all the field assignments for one transaction.
Table Name - predefined list of database tables that can be used for import.
In total Import Spreadsheet contains 3 database tables:
- Table PW001PAY (Payroll Entries in current and future periods)
- Table PW001PAI (Payroll Entry Additional Info on the Entry Code).
- Table PW001P0Y (Person's Payroll information - mostly taken from Constant page in Payroll window).1) PW001PAY and PW001P0Y tables can not be imported at the same time.
For each of them you need to make a separate setup each time you make the import.
If you make import only for one of those tables, then there is no need to delete the setup. You can re-use it the next time when you do the import.2) To import PW001PAI table you must import PW001PAY table as well. PW001PAI can not be imported by itself.
In order to add the link between PAI information and PAY entries you have to import PW001PAY.REF field with "Y" value.- Update if record exist - enables the input to the column "Key Fields" in the Details screen.
If this option is set, the system needs to know what are the key fields identifying the transaction that need to be updated.
2.2. Tables description
2.3. Add transaction details
A list of fields to be imported & the rules of import should be defined for each existing transaction.
In order to do this select the corresponding Transaction & click Details.
- Field - offers you a list of fields from the selected database table that you can use in the import
e.g. PIN, ENTRYCODE, ENTRYFIELD1 (also called L21 or Amount), ENTRYFIELD2 (also called L22 or Quantity/Days) etc.
If you don't know which payroll fields correspond to which database tables, contact Adonis support for assistance. - XLS Reference - defines the column (or cell) that contains data to be imported into selected field (e.g. A1, A2, B1, B7 etc.)
It is possible to set not only the cell but also the sheet that it belongs to (e.g. SHEET1!A1, SHEET2!A1 etc.) Reference should be the specific column from the Excel file which corresponds the PIN field on the payroll table.
- Batch - when XLS reference is set to a cell value (meaning that it is the same for the entire batch), the Batch option should be set off (disabled).
- Mandatory - defines if the value in this field is mandatory or not. When set and the value is missing - corresponding transaction line is skipped.
Mandatory should be checked accordingly as importing will not be possible without knowing which person it should be imported to. - Key Field - is taken to count only if Update record if exist is set On for the Transaction on Properties level. Otherwise it is treated the same as Mandatory.
If both options are set - the transaction line checks if the table already has the record with Key Field values. If so - such record is updated. If not - the record is created.
If multiple records meet the set of Key Fields - the system displays warning & update is not performed. This is to avoid updating multiple records by mistake.
3. Import procedure
- In the initial dialogue window, you should browse the root folder for imported files and click Prepare.
The system will create folders Receive / Rejected / Archive within the folder you seleceted.
- Place your import file(s) into the Receive folder. The file should be of xls / xlsx format.
Then click Prepare button again.
The system will generate a list of all the xls / xlsx files existing in Receive folder.
- If the list is ok, click Start Import. It will upload all files from the list according to defined transactions.
The file should be closed during the import.
a) If import is successful, the status will be changed to Imported.
The successfully imported files will be moved to the Archive folder, the date and time of import will be added to the name of the file.
b) If an error occurred during the import the status will be set to Rejected with an explanation line.
The rejected file will be moved to the Rejected folder.
- You can use Save Summary to save the log file with import results to your hard drive.
