This article explains how to use the Import Spreadsheet routine as a powerful tool that allows to add various payroll-related information to the batch of employees.

1 1. Introduction
2 2. Import setup
- 2.1 2.1. Add transactions
- 2.2 2.2. Tables description
- 2.3 2.3. Add transaction details
3 3. Import procedure

1. Introduction

Import Spreadsheet tool allows uploading various payroll-related information into the system for a batch of persons with the help of a simple xls / xlsx file.

It allows importing:

- 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 Spreadsheet window you will see the following dialogue that allows you to run the import.

However, before you start you need to set up and configure the import parameters.

The setup can be found under the Tools -> Import -> Spreadsheet -> Properties button.

Here you can set up and configure the import details by following the below recommendations.

To access the setup part, access rights should be set to Y for the routine 767 Spreadsheet Properties in Adonis Control Center (ACC).

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 a 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).
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 needs to be updated.

2.2. Tables description

Reference from list of the table PW001PAY

Foreign Key Columns	Child Table	Parent Table
PIN	PW001PAY (Payroll Entries, Current and future periods)	PW001P01 (Person Details)
ENTRYCODE	PW001PAY (Payroll Entries, Current and future periods)	PYETY001 (Entrycode information)
ACTIVITYSEQ	PW001PAY (Payroll Entries, Current and future periods)	PW001P03 (Activity Datagroup)
EXECUTIONID_CALC	PW001PAY (Payroll Entries, Current and future periods)	PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

Column list of the table PW001PAY

Code	Comment	Data type	Mandatory	Primary	Foreign Key
PIN		integer	X		X
SEQNO		integer	X	X
UPDATEFLAGS		varchar(125)
SOURCE		varchar(1)
FROMVESSEL		integer
CREATEDBY		varchar(6)	X
CREATETIME		date	X
CHANGEDBY		varchar(6)
CHANGETIME		date
CALCULATEDBY		varchar(6)
CALCULATETIME		date
FROMPERIODE		integer
TOPERIODE		integer
FROMDATE		date
TODATE		date
REF		varchar(2)
ENTRYCODE		integer	X		X
ENTRYFIELD1		varchar(20)
ENTRYFIELD2		varchar(20)
ENTRYFIELD3		varchar(20)
ENTRYFIELD4		varchar(20)
ENTRYFIELD5		varchar(20)
ACCOUNTNO		varchar(30)
ACCOUNTDIM2		varchar(10)
ACCOUNTDIM3		varchar(10)
ACCOUNTDIM4		varchar(10)
ACCOUNTDIM5		varchar(10)
ACCOUNTDIM6		varchar(10)
LTOREF		varchar(5)
TEXT		varchar(30)
PERIODEUSED		integer
CREDITENTRY		char(1)
BASIS		varchar(20)
CURRENCYCODECALC		varchar(3)
CALCULATEDAMOUNT1		float
CALCULATEDAMOUNT2		float
CALCULATEDAMOUNT3		float
CALCULATEDAMOUNT4		float
CALCULATEDAMOUNT5		float
CALCULATEDAMOUNT6		float
CALCULATEDAMOUNT7		float
CALCULATEDAMOUNT8		float
CALCULATEDAMOUNT9		float
CALCULATEDAMOUNT10		float
CALCULATEDAMOUNT11		float
CURRENCYCODE		varchar(3)
CURRENCYRATE		float
ACCOUNTNOOFCALCAMOUNT1		varchar(30)
ACCOUNTNOOFCALCAMOUNT2		varchar(30)
ACCOUNTNOOFCALCAMOUNT3		varchar(30)
ACCOUNTNOOFCALCAMOUNT4		varchar(30)
ACCOUNTNOOFCALCAMOUNT5		varchar(30)
ACCOUNTNOOFCALCAMOUNT6		varchar(30)
ACCOUNTNOOFCALCAMOUNT7		varchar(30)
ACCOUNTNOOFCALCAMOUNT8		varchar(30)
ACCOUNTNOOFCALCAMOUNT9		varchar(30)
ACCOUNTNOOFCALCAMOUNT10		varchar(30)
ACCOUNTNOOFCALCAMOUNT11		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT1		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT2		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT3		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT4		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT5		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT6		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT7		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT8		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT9		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT10		varchar(30)
ACCOUNTNOOFCALCAMOUNTCREDIT11		varchar(30)
FLAGOFCALCAMOUNT1		varchar(1)
FLAGOFCALCAMOUNT2		varchar(1)
FLAGOFCALCAMOUNT3		varchar(1)
FLAGOFCALCAMOUNT4		varchar(1)
FLAGOFCALCAMOUNT5		varchar(1)
FLAGOFCALCAMOUNT6		varchar(1)
FLAGOFCALCAMOUNT7		varchar(1)
FLAGOFCALCAMOUNT8		varchar(1)
FLAGOFCALCAMOUNT9		varchar(1)
FLAGOFCALCAMOUNT10		varchar(1)
FLAGOFCALCAMOUNT11		varchar(1)
QUANTITY1		float
QUANTITY2		float
CURRENTCURRENCY		char(1)
CALCPRIORITY		integer
DEPARTMENT		integer
COUNTYNO		integer
PAYSCALETABLE		varchar(10)
PAYSCALECODE		varchar(10)
PAYRISETABLE		varchar(10)
PAYRISECODE		varchar(10)
CALCSIGN		varchar(1)
ADDITIONALINFO		varchar(30)
NEVERTAX		varchar(1)
RANK		varchar(10)
ENTRYFIELD6		varchar(20)
CURRENCYRATEFORCALC		float
AUTOENTRY		char(1)
ISBACKPAYENTRY		varchar(1)
SD		varchar(1)
ACTIVITYSEQ		integer			X
ACCOUNTDIM7		varchar(30)
ACCOUNTDIM8		varchar(30)
ACCOUNTDIM9		varchar(30)
PAYNAME2		varchar(35)
CURRENCYFORPAYMENT		char(3)
CURRENCYRATEFORPAYMENT		float
PAID_WEEK		integer
EXECUTIONID_CALC	Indicates when a record was last changed during payroll calculation procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.	int			X
EXECUTIONID_CLOSE_M	Indicates when a record was last changed during closing period procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.	int
EXECUTIONID_CLOSE_Y	Indicates when a record was last changed during closing year procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.	int
SESSIONID	A current session ID. Refers to PWLOGINSESSION.SESSIONID.	int	X
DBACTION	Indicates a type of changes done.	varchar(10)	X
AUDIT_LINENR		int	X
repl_ModifiedBySite	Site that modified the record	integer
repl_ModifiedDate	Date of modification	datetime
repl_GMTModifiedDate	GMT date of modification	datetime

Reference from list of the table PW001PAI

Foreign Key Columns	Child Table	Parent Table
PIN	PW001PAI (Payroll Entry Additional Info)	PW001P01 (Person Details)
EXECUTIONID_CLOSE_M	PW001PAI (Payroll Entry Additional Info)	PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

Column list of the table PW001PAI

Code	Comment	Data type	Mandatory	Primary	Foreign Key
PIN	Person's pin.	integer	X		X
SEQNO	A value coincides with PW001PAY.SEQNO for current payroll records or PW001PYH.SEQNO for historical records.	integer	X	X
TEXT	Additional text.	varchar(100)
ACCOUNTNO	Account No.	varchar(35)
SWIFT	Swift code.	varchar(11)
PAYNAME	Receiver name.	varchar(35)
PAYCOUNTRY		varchar(2)
PAYPOSTCODE		varchar(102)
ADDRESS1		varchar(40)
ADDRESS2		varchar(40)
ADDRESS3		varchar(40)
PAYBANKCODE		varchar(40)
INTERNATIONALBANK	Link to PW001C26	varchar(10)
INTERMEDIATEBANK	Link to PW001C26	varchar(10)
RECEIVERBANK	Link to PW001C26	varchar(10)
RTYPEINTERNATIONAL		varchar(11)
RTYPEINTERMEDIATE		varchar(11)
RTYPERECEIVER		varchar(11)
RCODEINTERNATIONAL		varchar(10)
RCODEINTERMEDIATE		varchar(10)
RCODERECEIVER		varchar(10)
BALANCEACTION	List of Values: 0=Carry Forward To Next Month 1=Transfer To Bank Acct. 2=Interpay 3=Alpha Credit Bank 4=Cash 5=Banque Worms 6=Citibank 7=Chase Manhattan 8=Hellenic Bank 9=Isabel Domestic A=Isabel International B=Bank Transfer B4=ISO20022/Swift B7=Check Payment B8=Check Onboard C=Swedish Domestic D=Swedish International E=Philippine Banking F=Manual Bank G=Zagrebancka H=ING Bank I=Direct Deposit E-Money J=Deutsche Bank K=BACS L=Hellenic Cyprus M=MT100	varchar(1)
EXECUTIONID_CLOSE_M	Indicates when a record was last changed during closing period procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.	int			X
repl_ModifiedBySite	Site that modified the record	integer
repl_ModifiedDate	Date of modification	datetime
repl_GMTModifiedDate	GMT date of modification	datetime

Reference from list of the table PW001P0Y

Foreign Key Columns	Child Table	Parent Table
PIN	PW001P0Y (Person's Payroll information (Mostly Constant and Payment page in Payroll window))	PW001P01 (Person Details)
EXECUTIONID_LOCK	PW001P0Y (Person's Payroll information (Mostly Constant and Payment page in Payroll window))	PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

Column list of the table PW001P0Y

Code	Comment	Data type	Mandatory	Primary	Foreign Key
PIN		integer	X	X	X
PAYSCALECODE		varchar(14)
LEAVEPAY	Payroll > page Constant:	integer
PAYROLLPERIODLOCK		varchar(1)
LAYOUTSLIP		integer
REGIONTAXADVANCE		integer
TAXTABLE	Payroll > Constant page: Table No	integer
TAXPERCENTAGE	Payroll > page Constant: Percentage Tax	integer
TAXDEDUCTIONCODE		varchar(1)
TAXFREELIMIT	Payroll > page Constant: Exception Limit	integer
SEAMANSALLOWANCE		varchar(1)
WAGESOPTIONS		varchar(12)
DAYSEMPLOYED		integer
LEAVEPAYSTARTBALANCE		integer
PAYCOUNTRY		varchar(3)
PAYSWIFTCODE		varchar(20)
PAYNAME	Payroll > Payment page > Alternative Receiver page: Name	varchar(30)
PAYNAME2	Payroll > Payment page > Alternative Receiver page: Name	varchar(35)
PAYADDRESS1	Payroll > Payment page > Alternative Receiver page: Address 1	varchar(40)
PAYADDRESS2	Payroll > Payment page > Alternative Receiver page: Address 2	varchar(40)
PAYADDRESS3	Payroll > Payment page > Alternative Receiver page: Address 3	varchar(40)
PAYADDRESSCOUNTRY	Payroll > Payment page > Alternative Receiver page: Country	varchar(2)
PAYPOSTCODE	Payroll > Payment page > Alternative Receiver page: Post Code	varchar(102)
REGIONCODE	Payroll > page Constant: Region Code	varchar(10)
NOEMPLOYERFEE		char(1)
PAYBANKCODE		varchar(40)
AUTOTAX	Payroll > page Constant: Automatic Tax	integer
TAXEXEMPTION		integer
TAXCOMMENT		varchar(100)
ALTERNATIVEASCODE		integer
INTERNATIONALBANK		varchar(10)
INTERMEDIATEBANK		varchar(10)
RECEIVERBANK		varchar(10)
RTYPEINTERNATIONAL		varchar(11)
RTYPEINTERMEDIATE		varchar(11)
RTYPERECEIVER		varchar(11)
RCODEINTERNATIONAL		varchar(10)
RCODEINTERMEDIATE		varchar(10)
RCODERECEIVER		varchar(10)
PAYBANKNO		integer
PAYBANK_CODE		varchar(10)
TROSSAMFUND		varchar(1)
LEAVEDAYSDEDUCTEDFACTOR		varchar(20)
ALLOWANCECLAIMED		integer
TAXINFO	Payroll > page Constant: Tax Info Number	varchar(15)
MBANKNAME		varchar(40)
MBANKROUTINGTYPE		varchar(40)
MBANKROUTINGCODE		varchar(40)
MBANKADDRESS1		varchar(40)
MBANKADDRESS2		varchar(40)
MBANKADDRESS3		varchar(40)
MBANKCOUNTRY		varchar(3)
MBANKPOSTCODE		varchar(102)
TAXCODE		varchar(10)
MONYHWEEKBASIS		varchar(1)
NI_NUMBER		varchar(9)
UKPAYRATE		varchar(4)
UKPAYCATEGORY		varchar(1)
EXECUTIONID_LOCK	Indicates when a record was last locked. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.	int			X
SESSIONID	A current session ID. Refers to PWLOGINSESSION.SESSIONID.	int	X
DBACTION	Indicates a type of changes done.	varchar(10)	X
AUDIT_LINENR		int	X
repl_ModifiedBySite	Site that modified the record	integer
repl_ModifiedDate	Date of modification	datetime
repl_GMTModifiedDate	GMT date of modification	datetime

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 - the 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 the 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 selected.
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.

Adonis HR Knowledge Base

Import Spreadsheet