Timesheet Uploads File Format

iPayroll and CloudPayroll's systems allows an authorised user to upload transactions (timesheets) into an open payroll. This option caters for integrating software packages that can not use the iPayroll / CloudPayroll REST API. The following formats are available for use by developers:

• Spreadsheet (Multiple transactions) Format
• Standard (Single Transactions)Format

Spreadsheet Format

The spreadsheet (Multiple Transactions) format allows uploading of multiple transactions in each row.

The following rules apply to the file:

1. Each person starts on a new line.
2. The first line is a header record containing (all on one line):-
   id, name, T1, T1.5, BON, other, quantity, leaveDays, from, to, reason, costCentre, message, Days in Period
3. Names are optional and must not contain commas. (They are ignored - the id identifies the person).
4. The columns "T1", "T1.5" and BON" can be replaced with any other elements, in any order.
5. There may be any number of columns with payment or deduction ids, so for example, some Organisations pay their ordinary time day by day and would have columns for elements "MON", "TUE, "WED", "THU" etc. as well as other payments and deductions
6. A row can contain several transactions.
7. A person can have many transactions, split across several rows.
8. Empty fields are acceptable.
9. Leave Days are only applicable to leave balances that are held in days (generally Sick, Bereavement and Alternative Leave)Leave dates may be in dd/mm/yy or dd-mmm-yy format e.g. 15/1/13 or 15-Jan-13 (Year may be 2 or 4 digits e.g. 13 or 2013)
10. Reasons only apply to leave transactions.
11. If leave dates are not entered, then leave history is recorded as at period end date
12. Blank lines in the file are ignored, as are lines beginning with #.
13. A line starting with 'End of File' indicates remaining lines are ignored.
14. Spaces between comma's, and at the beginning and end of each line are ignored.
15. Quantities can have up to 2 decimal places, rates up to 4. (eg. quantity 40, 40.0 or 40.00).
16. If a column starts with a dollar sign ($) it is interpreted as a rate override and paid with a quantity of 1.00.
17. Text in the message column is appended to the person's payslip message.
18. The costCentre, reason and message columns are optional.
19. If a value is entered in 'costCentre' this overrides the default cost centre for every transaction in the line (e.g. if T1 AND AL were in the same line, and there was a cost centre override on that line, both T1 and AL would use the override cost centre)
20. Any override cost centre on the timesheet upload file must exist in the Cost Centre setup for the Organisation
21. For element ids in the "other" column, the qty may not be preceded by a $ value. If it is, display error message for line: "Quantity for element id should be number of hours, NOT a rate override" (e.g. "Quantity for AL-ADJ should be number of hours, NOT a rate override")
22. If Organisation has ADP "Record Days on Timesheet" activated (NZ only):
    1. if the value in the "Days" column is not blank, replace the existing value in the "Days in period" field in current timesheet
    2. if value in the "Days" column is blank, do not update the "Days for period" field in current timesheet (i.e. leave existing value)

e.g.

id,name,T1,T1.5,MEAL,other,quantity,leaveDays,from,to,reason,costCentre,message,Days in Period
boss,MATTHEWS John,24,3,$10.20,AL,,16,15-Jan-2013,16-Jan-2013
boss,,,,,SICK,8,1,17-Jan-2013

This pays the person who has the id 'boss' 24 hours Ordinary Time (T1), 3 hours Overtime (T1.5), 1 Meal Allowance (MEAL) at a rate of $10.20, 16 hours Annual Leave (AL) for 15 to 16 Jan 2013 and 8 hours (1 day) Sick Leave (SICK) for 17-Jan-2013

Standard Format

The File must be in the following format:

• The file must be a comma delimited, ASCII file.
• Each transaction starts on a new line.
• Each line contains the following fields: personId, elementId, quantity.
• Each line can also, optionally, contain one to eight additional fields: costCentre, rate, leaveFrom, leaveTo, reason, leaveDays, days in period (NZ only) and pay periods (AU only).
• The cost centre field must be included if the rate field is present. (An empty cost centre is acceptable -e.g. boss,MEAL,1,,10.20).
• Empty fields are acceptable, e.g.boss,T1,40,, is the same as boss,T1,40
• Leave dates may be in dd/mm/yy or dd-mmm-yy format e.g.15/1/13 or15-Jan-13 (Year may be 2 or 4 digits e.g. 13 or 2013)
• If leave dates are not entered, then leave history is recorded as at period end date
• Leave days are used if sick, alternative holiday and bereavement leave is recorded in days
• Blank lines in the file are ignored, as are lines beginning with #.
• A line starting with 'End of File' indicates remaining lines are ignored.
• Spaces between comma's, and at the beginning and end of each line are ignored.
• Quantities can have up to 2 decimal places, rates up to 4.(eg. quantity 40, 40.0 or 40.00).

e.g.

	
boss,T1,40
boss, T1.5, 3, 2000

boss,MEAL,1,,10.20
#Here is the timesheet for Chris
chris,T1.5,0.5
chris,AL,8,,,15-Jan-2013

This pays the person who has the id 'boss' 40 hours Ordinary Time (T1), 3 hours Overtime (T1.5) costed to cost centre '2000', and 1 Meal Allowance (MEAL) at a rate of $10.20. It also pays the person who has the id 'chris', 1/2 hour Overtime (T1.5) and 8 hours Annual Leave (AL).