We have developed a powerful import engine which allows you to very easily import time / allowance data from your other software. You can also use it for complex costing or piece/rate payments. This article will describe the file specifications required (Part 1) and the necessary PaySauce settings to cater for the import (Part 2)
Part 1 - the PaySauce file format
Mandatories when you create a file to load into PaySauce
Header fields are required
File must be a csv
The filename is flexible and has the only requirement that PaySauce (case insensitive) appear somewhere in the filename and not contain the words 'timehub' or 'deputy'. e.g. My PaySauce Worksheet for 2021-08-01.csv is perfectly acceptable as is paysauce-upload.csv for example, but not deputy-paysauce-upload.csv.
Header
Name | Type | Required | Description |
Date | String | Yes | The date of the work record in either YYYYMMDD or DD/MM/YYYY format. |
Factor / Type | String / Number | Yes | Code or number that represents the payroll instruction / factor. If a code is present then this must match a corresponding label field of the tag set setup on the timesheet template. See Type Codes below. If a number, then this represents the factor directly. If this value is not supplied then the factor will default to 1. |
IrdNo | String | Yes | The IRD number of the employee and must match that of the employee record in PaySauce in the pay run that is currently receiving the import. Can be an 8 digit or 9 digit number, with or without intervening - (dashes) |
Total / Hours / Leave / Quantity / Amount | Number | Yes | The unit quantity of the line item. Any of the five field names are acceptable. The recommended name is Quantity or Total as this represents the most generic form of the data. |
xxxCode | String | No | Any number of additional fields that either represent costing or rate codes. These need to be setup in the timesheet template and depending on their configuration will map accordingly. See Additional Code Fields below. This allows for an infinite number of cost centres and also allows for them to be named according to the source system. The only restriction is that the field name must end in Code (case insensitive). If this field is not supplied then no cost centres will be applied to the line item and instead default costing will apply according to the allowance and employee master setup. |
TH | String | No | Only used when importing Public Holiday Worked hours which is indicated by the factor (see Pay Card Codes below). TH represents time and a half (x1.5). Leaving the field blank will enable a x1.5 payment by default. Entering Yes will do the same. Entering No will not apply x1.5. |
Bank hours | Number | No | Only used when importing Public Holiday Worked hours which is indicated by the factor (see Pay Card Codes below). Entering a number applies that quantity of hours to bank as Alternate Leave gained. Leaving the field blank will not bank any hours |
Rate PHW | Number | No | Only used when importing Public Holiday Worked hours which is indicated by the factor (see Pay Card Codes below). If you require to pay at a different rate to the ordinary, you can nominate a custom rate to pay. This rate will then be multiplied by TH (if opted for) and quantity to calculate the eranings. |
Factor Codes
Label | Value | Action |
ORD | 1 | The payment is ordinary |
Blank | 1 | If left blank, a default factor of 1 will be used |
TQ | 1.25 | The payment is ordinary but paid out at the appropriate factor |
TH | 1.5 |
|
DT | 2 |
|
TT | 3 |
|
Factor / Type codes are divided into two sets:
Those that control the factor, e.g. Time and a half, Double time etc.
Those that control the pay card allowances that appear after the timesheet import.
If codes are defined, then they must also be on a PaySauce tag set and must be configured on a timesheet template under the Factor setting. This means that the template type must be Rate and Factor.
If numbers are supplied then this has the added advantage of less configuration in PaySauce as no Factor tag set is required as well as the ability to use an infinite number of factors (as the data is simply numeric) as well as the ability to use Simple template option.
However, using codes over straight numerical values has the added option of being able to import leave as well as fixed payment allowances (either taxable or non taxable). These allowances when uploaded this way will appear on the pay card as well as appearing on the primary payment timesheet. When appearing on the timesheet these allowances will appear as separate views labeled Leave and Allowances.
When allowances are loaded this way, they are loaded on a daily basis, however will appear on the pay card as a summary (read only) item and are only editable in the timesheet Allowances view.
When leave is loaded this way it will appear as read only on the pay card (in the normal way) but will be editable both on the timesheet Leave view and well as the Leave Management view and the Paycard Leave Popup view.
The following type codes are examples of how you might instruct PaySauce to pay out different factors that are applied for things like holiday pay and ordinary time.
Pay Card Codes
Label | Value | Action |
AL | allowance.leave.annual | Creates an annual leave payment at the rate as determined by payroll settings |
SL | allowance.leave.sick | Creates and records a sick leave payment |
ALT | allowance.leave.alttaken | Creates and records an alternate leave payment |
STAT | allowance.leave.stattaken | Creates and records a public holiday taken leave payment |
PHW | allowance.leave.pubworked | Creates and records a public holiday worked leave payment |
BL | allowance.leave.bereavement | Creates and records a bereavement leave payment |
LWOP | allowance.leave.withoutpay | Records leave without pay |
EXP | allowance.fixed | Supplies a fixed payment to the matched payment EXP in PaySauce. Can be other things like Reimbursement, Rent etc, whatever you want to supply on the import file that is meaningful. |
EXPNTX | allowance.fixedntx | Same as EXP, but allows for non taxable allowances, things like Mileage, Uniform etc |
Alt. Day | allowance.leave.lieuearned | Banks an Alternative Public Holiday |
Days Paid | leave.dayspaid | Records the number of days paid in the period |
NOTE: The Label field is configurable and depends on how external systems might be setup. The key takeaway is that the Value field controls the behaviour. If the value field is numeric, then a Factor is applied, if the Value field is one of the preset instruction values then a pay card allowance instruction is applied.
Additional Code Fields
Any field that ends with “Code” (case insensitive) will be treated as a candidate for costing and rate overrides. This allows for example, a line item to be costed to say the Shady Glade farm and doing say the Fencing activity. You are limited here only by what you configure in PaySauce and what your external data capture systems are collecting.
In addition, an override rate can be applied to a line item which gives full control for rates changing on a daily basis.
Rates are codes and are configured in the same way as factors. For example the HCA-1 (Healthcare Assistance) rate might have a value of 25 and HCA-2 might be 27, meaning for different line items, different hourly rates can be applied.
Rates are configured on the timesheet template and are auto discovered on the worksheet import file.
Importing into an allowance other than the Primary Payment
PaySauce allows for an employee to have an unlimited number of allowances. Allowances are grouped on a pay card and a pay card is calculated accordingly. For things like determining an employee's current rate (for leave purposes) and being able to import data, a Primary Payment needs to be defined.
The primary payment is preciously what it says - it is the go-to payment to use in absence of any other payment reference. This is fine for simple cases, however when additional data is required to be imported (like piece rates for example) and the primary payment is set to the timesheet hours, we need to explicitly define which payment we require the source data to be imported into.
This is easily achieved by using a code field (call it what you like, it only needs to end with code) and placing a value in that code field that matches the Title field of the allowance you wish to import into.
Every allowance has a Title field and this is used to refer to it both on the pay card and the payslip and furthermore for clarity this should be unique per employee.
This means we can, for example, import data into the primary payment (without defining an allowance code), or into a piece rate allowance by calling it say, Piece Rate and / or even into a zero hours rated timesheet by calling it say Piece Rate Hours Worked or similar.
To import into a pre-existing taxable/non-taxable allowance (for example a phone allowance), do not date the line item and ensure the correct allowance name is nominated in the AllowanceCode column.
This allows us to import data into multiple allowances on the pay card in a single file upload.
Sample
Date,IrdNo,Factor,Quantity,Allowance Code,Rate Code,LocationCode,ActivityCode,TH,Bank hours,Rate PHW 20210801,111-111-111,,8,,,Meadowvale,Fencing,,, 20210801,111-111-111,TQ,2,,,Meadowvale,Fencing,,, 20210802,111-111-111,1.5,6,,,Meadowvale,Milking,,, 20210803,111-111-111,Rent,400,,,,,,, 20210804,111-111-111,Tools,25,,,,,,, 20210803,111-111-111,,200,Piece Rate,,ShadyGlade,Apple Picking,,, 20210803,111-111-111,,75,Piece Rate,Golden,ShadyGlade,Apple Picking,,, 20210803,111-111-111,,6,,Piece Hours,ShadyGlade,,,, 20210804,111-111-111,,6,,Living,ShadyGlade,Cropping,,, 20210805,111-111-111,,7,,Minimum,ShadyGlade,Trench Digging,,, 20210806,111-111-111,AL,8,,,,,,, ,111-111-111,,150,Travel,,,,,,
20210807,111-111-111,PHW,8,,,,,Yes,8,
# | Description |
1 | This is the header line configured as per the rules outlined under Header above |
2 | Imports 8 hours of ordinary time for the 1/8/2021, into the Primary Payment, to be costed to the Meadowvale farm doing Fencing |
3 | Imports 2 hours of time for the 1/8/2021, to be paid at Time and a Quarter into the Primary Payment, to be costed to the Meadowvale farm doing Fencing |
4 | Import 6 hours of time for the 2/8/2021 to be multiplied out by 1.5 into the Primary Payment to be costed to the Meadowvale farm doing Milking |
5 | Import a taxable Rent allowance of $400 to appear under the Allowances view of the Primary Payment on the 3/8/2021. |
6 | Import a non taxable Tools allowance of $25 to appear under the Allowances view of the Primary Payment on the 4/8/2021. |
7 | Import 200 baskets of apples for the 3/8/2021, into the Piece Rate allowance (to be paid out at the base rate), to be costed to the ShadyGlade orchard under the activity of Apple Picking |
8 | Import 75 baskets of apples for the 3/8/2021, into the Piece Rate allowance (to be paid out at the Golden apples override rate), to be costed to the ShadyGlade orchard under the activity of Apple Picking |
9 | Import 6 hours of zero rated time for the 3/8/2021 into the Primary Payment to be costed to the ShadyGlade orchard |
10 | Import 6 hours of time for the 4/8/2021 into the Primary Payment, to be paid at the Living wage on the ShadyGlade orchard for Cropping trees. |
11 | Import 7 hours of time for the 5/8/2021 into the Primary Payment, to be paid at the Minimum wage on the ShadyGlade orchard for Trench Digging. |
12 | Import 8 hours of Annual Leave for the 6/8/2021 |
13 | Import $150 in to a non-taxable allowance already set up on the pay card |
14 | Import 8 hours of public holidays worked leave, paid at time and a half and bank 8 hours of alternate leave for 7/8/2021 |
Importing into empty periods
Some companies may require the ability to import data into an empty period, this is advantageous if you employ mainly casual staff which makes it difficult to manage employees paid period to period. As long as the employee is loaded into PaySauce with the appropriate payments set up, they can be imported via the PaySauce import file. The file must use the correct IRD no. or external reference to identify the employee.
Click here to read Part 2 - PaySauce settings to cater for the import