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.
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).
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
Table PW001PAY (Payroll Entries, Current and future periods)
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 |
|
|
|
Table PW001PAI (Payroll Entry Aditional Info on the Entry Code)
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 |
|
|
|
Table PW001P0Y (Person's Payroll information (Mostly Constant page in Payroll window))
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 - 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.
