File Formats for Timesheets, Leave and Payslips (NZ & UK)
File Formats for Timesheets, Leave and Payslips (NZ & UK)
Overview
UpSheets allows you to upload the following transactions to Xero:
- Timesheets
- Leave (Approved)
- Payslips NZ only – not available in UK
- Earnings
- Reimbursements
- Deductions
- Leave Accruals
Payslips are not available for United Kingdom Xero organisations.
File Formats
This document describes the requirements with a single row for each employee/date/type/hours. A common alternative format contains multiple columns for each type (pay item/earnings rate). These can be imported directly by configuring your pay items.
You may include any or all of the supported transaction types in the same file. Provide correct data in the appropriate columns and select them when uploading. See File Formats for Combined Timesheets, Leave and Payslips in a Single File (NZ & UK) for examples.
Selecting Options
After connecting to Xero, select the appropriate options on the Import screen.
Notes on file format
- Clicking the Download Sample button on the Import screen will download a file with your Xero data in it and is the easiest way to get started.
- A header row is not required, but it will save you time as UpSheets matches the headings automatically where possible and saves you from selecting them individually.
- The order of the columns in your file does not matter.
- Extra columns will be ignored, but it is faster if they are removed.
Full name field
In all of the examples, you may use a single full_name column instead of first_name and last_name.
Timesheets
| Heading | Description | Format | Notes |
|---|---|---|---|
| first_name | The first name of the employee. | Text | Required |
| last_name | The last name of the employee. | Text | Required |
| date | The date the employee worked. | Date | Required |
| hours | The number of hours the employee worked. | Number (max 2DP) | Required - You may optionally provide a Start Time and End Time column instead |
| type | The Earnings Rate for the hours worked. Must match the text of the Earnings Rate in Xero. | Text | Optional, but if not in the file a default or the employee default must be selected at import or by using multiple pay items. |
| tracking_code | The Tracking Code for the hours worked. Must match the text of the Tracking Code in Xero. | Text | Optional, but if not in the file a default must be selected at import. |
Leave
| Heading | Description | Format | Notes |
|---|---|---|---|
| first_name | The first name of the employee. | Text | Required |
| last_name | The last name of the employee. | Text | Required |
| date | The date the leave started. | Date | Required |
| end_date | The date the leave ended. | Date | Required |
| hours | The number of hours leave for the employee. | Number (max 2DP) | Required - You may optionally provide a Start Time and End Time column instead |
| type | The type of leave taken. Must match the text of the Leave Type in Xero. | Text | Required. Or use using multiple pay items. |
| description | A description of the leave taken. | Text | Required |
Payslips
Payslips are available for New Zealand organisations only.
| Heading | Description | Format | Notes |
|---|---|---|---|
| first_name | The first name of the employee. | Text | Required |
| last_name | The last name of the employee. | Text | Required |
| date | The transaction date. The specific date is not assigned in Xero. UpSheets will apply the line to the draft payslip it finds for the employee. | Date | Required |
| hours | The number of hours to be adjusted. | Number (max 2DP) | Required - See below - You may optionally provide a Start Time and End Time column instead |
| type | The Pay Item type. Must match the text of an active Pay Item defined in Xero. | Text | Required |
| rate | The amount or rate of the transaction. The calculation type determines how this is used. | Number (max 2DP) | Required - See below |
| hours | The number of hours for the transaction. The calculation type determines how this is used. | Number (max 2DP) | Required - See below |
| description | A description of the reimbursement that will appear on the payslip. Used for reimbursements and Kiwisaver superannuation pay item lines only | Text | Optional For Kiwisaver Superannuation set description to SUPER and complete fields as per a STAT_DEDUCTION |
Rate and Hours by Pay Item Type
The field values required in an upload file depend on the type of pay item being uploaded as described in the table below.
All payslip lines use the same file format and ALL fields must be included in the file, even if left blank. This enables you to upload multiple pay item types in a single file and process them at once.
For example, the Hours column is required even when loading a deduction or reimbursement but the value should be left blank as per the table below. Likewise, Earnings still require the Calculation Type column, even though the value would be left blank.
| Pay Item Type | Rate | Hours |
|---|---|---|
| DEDUCTION | The total amount to be deducted in dollars. Value must be greater than 0 OR leave blank to use the amount defined on the pay item in Xero | Leave blank |
| REIMBURSEMENT | The total amount to be reimbursed in dollars | Leave blank |
| OR the rate per unit. Note – Xero only allows the upload of Reimbursements defined as Fixed Amount. If both Rate and Hours are provided, UpSheets will calculate the total and upload it as a fixed amount and add the Rate and Units as the description. You may need to define a new pay item as Fixed Amount in Xero to allow this. | The number of units | |
| EARNINGS | If the pay item in Xero is set as FIXED_AMOUNT: The total amount in dollars | Leave blank |
| If the pay item in Xero is NOT set as FIXED_AMOUNT: The rate per unit earnings. OR leave blank if a Rate Per Unit value is set in Xero. | The number of units worked | |
| LEAVE ACCRUAL | Leave blank | The number of hours to be accrued |
| STAT_DEDUCTION | The total amount in dollars | Leave blank |
| SUPER | The total amount in dollars | Leave blank. Set Description field to SUPER |
Full name field
In all of the examples, you may use a single full_name column instead of first_name and last_name.
You may optionally provide a Start Time and End Time column instead of the Hours column.
For information on saving spreadsheets as CSV files see here