Import and export specifications

Each employee's data is on a separate line. Each line is structured as

ID, PR1, PR2, PR3, PR4, PR5, PR6, Ref, Units, Ref, Units, Ref, Units, etc

Every field must be delimited with a comma.

The first field is the employee ID as used in Ace Payroll.

The next six fields (PR1 to PR6) are the number of units of each of the first six user definable pay rates to be paid for the employee. If less than six rates are used, the unused fields must still be delimited with commas.

After reading the pay rates fields, the import utility reads in allowances to be paid until the end of the line is reached.

There can be any number of allowances paid. They are structured in pairs. The first element of the pair (Ref) is the allowance reference as used by Ace Payroll. The second element of the pair (Units) is the number of units of the allowance to be paid. The import utility reads these paired fields until the line ends.

The information is read into the current pay period (This Pay) for the relevant employee.

All fields after the identifier (ID) accept a maximum of eight characters of numeric data. The maximum number that can be used in each field is 9999.999. If a whole number is used you do not have to include a decimal point.

The import utility will ignore any line that contains an invalid employee identifier, and will also ignore any allowances that have not been assigned to the relevant employee.

The utility overwrites the 'This Pay' fields for the relevant imported employees but leaves all other employees alone.

Example

Employee EH has two different rates for fruit picking. Apricots have been loaded as allowance reference number 6. Peaches are reference 8 and the unit value of each of these allowances is per kilogram.

EH has picked 300 kilograms of apricots and 500 kilograms of peaches in the current pay period. The data in IMPORT.TXT for this employee will appear as:

EH,0,0,0,0,0,0,6,300,8,500

Employee FJ has worked 40 hours at payrate 1, 3 hours at payrate 2, and 1.25 hours at payrate 3. Their union fee is loaded as allowance number 73 and 1 unit of this will be deducted. Their meal allowance is loaded as reference number 89 and 2 of these will be paid. The data in IMPORT.TXT for this employee will be:

FJ,40,3,1.25,0,0,0,73,1,89,2

Comments can be inserted into any part of the file by either:

Comments work on a line by line basis. To comment out a block of lines, every line in the block must be commented as shown.

A new employee can be imported into Ace Payroll by adding a line within the generic import file. The line contains a maximum of 11 fields, as follows:

Field 1 Style

• For adding a new employee, this is always #15.

Field 2 Employee ID

• This field is a unique 5 character identifier to the new employee.
• This field becomes the permanent identifier to the employee in Ace Payroll, and cannot be changed.
• The field must only contain the characters A to Z, and 0 to 9.
• The field must not contain spaces, hyphens, brackets, or any other characters apart from A to Z and 0 to 9.
• The field must be unique to the employee.
• Before importing, Ace Payroll ensures there are no other employees with the same identifier, and also that there are no duplicates in the file being imported.
• Character case is ignored. In other words, characters a..z are treated the same as A..Z
• Numeric values, if used, are treated as characters. In other words, "012" and "12" are different identifiers.
• If the field contains more than 5 characters, only the first 5 characters are used.
• If the field contains characters other than A to Z or 0 to 9, the invalid characters are removed, and the remaining characters are used as the identifier.

Examples 

• 003, 3, JOHN, JS are all valid identifiers.
• John. B. - the fullstops and space are removed, and the remainder converted to upper case. The ID becomes JOHNB.
• 035/264 - the slash is removed, then the first five characters are used. The ID becomes 03526.
  
  

Employee Names

• Fields 3, 4 and 5 contain the employee name. None of these fields are compulsory,

  but the three values are validated to ensure that at least one of the fields contains a value.

• In other words, a new employee cannot be imported without some sort of a name.
• These fields accept any characters, in upper or lower case, but it is best to stick to standard keyboard characters, avoiding chinese letters, and other characters entered by using the Alt key.

Field 3 Payslip Name

• This field takes up to 25 characters.
• The field can be used to record an abbreviated name, or nickname, such as "John", "Mary", "Billy T" etc.

Field 4 Surname

• This field takes up to 15 characters.

Field 5 First Names

• This field takes up to 25 characters.
  

Other Information

Fields 6,7,8 Postal Address

• These three fields are for a three line postal address, 30 characters per line.
• These fields accept any characters, in upper or lower case, but it is best to stick to standard keyboard characters, avoiding chinese letters, and other characters entered by using the Alt key. 

Field 9 Phone Number

• 15 characters.
  
  

Field 10 Occupation

• 20 characters.
  
  

Field 11 Start Date

• This is the employee start date in 8 or 10 character format.
• Examples are 26/03/96 , 26/03/1996 , 26/03/02 , 26/03/2002 , 26-03-96.
• If an invalid date is passed, it is not picked up by the validation method, and is not imported. The rest of the line is imported.

Style #50 provides a method of importing hours worked, allocated to individual departments.

Each line takes five parameters. Example lines are:

#50,JE,50001,12.25,"ADMIN"

#50,JE,50001,25.5,"SALES"

#50,JE,50002,3,"TRAINING"

This style is used in conjunction with style #50, and enables the import of allowances and deductions. By combining styles #50 and #51 within the same import file, pay rates and allowances can be imported.

Style #51 takes four parameters. An example is:

#51,FJ,73,1

#51,FJ,89,2

The last example given for the generic import utility was:

FJ,40,3,1.25,0,0,0,73,1,89,2

This can be replaced completely using styles #50 and #51. Using these styles, the same functionality is provided by:

#50,FJ,50001,40, - 40 hours at first pay rate

#50,FJ,50002,3, - 3 hours at second pay rate

#50,FJ,50003,1.25, - 1.25 hours at third pay rate

#51,FJ,73,1 - 1 unit of allowance 73

#51,FJ,89,2 - 2 units of allowance 89

This style imports into Ace Payroll the hours worked by an employee, together with the hourly rate to be paid.

Each line represent the hours and rate to be paid for each of an employee pay rates.

A style #52 line has five fields. An example is:

#52,FJ,50001,40,15.75 - First pay rate 40 hours @ $15.75

#52,FJ,50002,3,12.70 - Second pay rate 3 hours @ $12.70

#52,FJ,50020,1.25,18.00 - Twentieth pay rate 1.25 hours @ $18.00

When an employee takes days in lieu, there are four considerations. These are:

When importing data from an external source for lieu days taken, each of the above is considered.

Lieu days taken may be imported to Ace Payroll by adding a line within the generic import file. The line contains a maximum of 8 fields, as follows:

#61, Employee ID, Entitlement, Hours Paid, Hourly Rate, Amount Paid, Days Taken, Reason 

The specification for lieu days taken is identical to that for sick and special leave, apart from fields 1 and 3.

Field 1. Style
For lieu days taken, this is always #61.

Field 2. Employee ID
Up to 5 characters identifying the employee, as identified by Ace Payroll. This field is always validated, and import will not proceed if this field does not identify a valid employee.

Field 3. Entitlement
Currently, this field is either left blank, or contains a zero. The data contained in the line is always imported irrespective of whether the employee has any lieu days owing.

The specifications for this field may be enhanced at a later date, to allow the importation of lieu days taken based on the employee eligibility, by the import file specifying a value other than zero or a null field.

The value of this field is currently not validated, but the field must exist. It must be either blank, or contain a zero.

Field 4. Hours Paid
This field is optional. If used, payment will be made to the employee based on the number of hours in the field, multiplied by an hourly rate.

Field 5. Hourly Rate
This field is optional, and is only relevant if a value exists in the previous field. If both fields exists, the employee is paid by multiplying the values together.

If this field is blank, and a value exists in the hours paid field, the employee is paid using the value of their first pay rate used.

Field 6. Amount Paid
This field is optional, and is only used if the hours paid field is either zero, or blank. If so, the amount in this field is paid as the total dollar amount.

Note: It is permissible for fields 4, 5 and 6 to all be blank. This is

done if the purpose of the import is to reduce an employee entitlement, without making specific payment for it. This could be done if an employee is paid by salary, and the user prefers to leave the salary payment untouched.

Field 7. Entitlement Reduction
This field is optional, and if used contains the number of days by which an employee's entitlement is to be reduced. Note that by leaving this field blank, it is possible to pay an employee for leave without reducing their entitlement.

Field 8. Reason
This field is optional, and contains up to 25 characters decribing the leave entry. If the field is either blank, or does not exist, the reason is shown as "imported data".

Examples 

#61,JS,0,8 - this line pays employee JS for 8 hours leave, at the rate of their first pay rate used. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

 

#61,JS,0,8,12.75 - this line pays employee JS for 8 hours leave,

at the rate of $12.75 an hour. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#61,JS,0,0,0,115.75 - this line pays employee JS $115.75 for leave

taken. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#61,JS,0,0,0,115.75,1.25,"took Easter days" - this line pays employee JS $115.75

for days in lieu taken, and reduces their entitlement by 1.25 days. The reason is shown as "took Easter days".

#61,JS,0,0,0,0,1.25,"took Easter days" - this line does not pay the employee any

amount, but reduces their entitlement by 1.25 days. The reason is shown as "took Easter days".

#61,JS,0,22,0,0,2.75,"took Easter days" - this line pays the employee 22 hours at

their first pay rate used, and reduces their entitlement by 2.75 days. The reason is shown as "took Easter days".

When an employee is paid for working on a public holiday, there are four considerations. These are:

When importing data from an external source for public holidays, each of the above is considered.

Public Holidays may be imported to Ace Payroll by adding a line within the generic import file. The line contains a maximum of 8 fields, as follows:

#62, Employee ID, Entitlement, Hours Paid, Hourly Rate, Amount Paid, Lieu Days Added, Reason 

The specification for public holidays is identical to that for styles 60 to 63 apart from fields 1,3 and 7.

Field 1 Style
For public holidays, this is always #62.

Field 2 Employee ID
Up to 5 characters identifying the employee, as identified by Ace Payroll. This field is always validated, and import will not proceed if this field does not identify a valid employee.

Field 3 Entitlement
Currently, this field is either left blank, or contains a zero. The data contained in the line is always imported irrespective of the employee entitlement.

The specifications for this field may be enhanced at a later date.

The value of this field is currently not validated, but the field must exist. It must be either blank, or contain a zero.

Field 4 Hours Paid
This field is optional. If used, payment will be made to the employee based on the number of hours in the field, multiplied by an hourly rate.

Field 5 Hourly Rate
This field is optional, and is only relevant if a value exists in the previous field. If both fields exists, the employee is paid by multiplying the values together.

If this field is blank, and a value exists in the hours paid field, the employee is paid using the value of their first pay rate used.

Field 6 Amount Paid
This field is optional, and is only used if the hours paid field is either zero, or blank. If so, the amount in this field is paid as the total dollar amount.

Note: It is permissable for fields 4, 5 and 6 to all be blank. This is

done if the purpose of the import is to reduce an employee entitlement, without making specific payment for it. This could be done if an employee is paid by salary, and the user prefers to leave the salary payment untouched.

Field 7 Lieu Days Added
This field is optional, and if used contains the number of days in lieu the employee is entitled to from working on the public holiday.

Field 8 Reason
This field is optional, and contains up to 25 characters describing the leave entry. If the field is either blank, or does not exist, the reason is shown as "imported data".

Examples 

#62,JS,0,8 - this line pays employee JS for 8 hours leave, at the rate of their first pay rate used. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

 

#62,JS,0,8,12.75 - this line pays employee JS for 8 hours leave,

at the rate of $12.75 an hour. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#62,JS,0,0,0,115.75 - this line pays employee JS $115.75 for leave

taken. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#62,JS,0,0,0,115.75,1.25,"took easter days" - this line pays employee JS $115.75

for working on the public holiday, and increases their entitlement to days in lieu by 1.25 days. The reason is shown as "worked over easter".

#62,JS,0,0,0,0,1.25,"took easter days" - this line does not pay the employee any

amount, but increases their entitlement to days in lieu by 1.25 days. The reason is shown as "worked over easter".

#62,JS,0,22,0,0,2.75,"worked over Easter" - this line pays the employee 22 hours at

their first pay rate used, and increases their entitlement to days in lieu by 2.75 days. The reason is shown as "worked over easter".

When an employee is paid for bereavement leave, the considerations are:

When importing data from an external source for bereavement leave, each of the above is considered.

Bereavement Leave is imported into Ace Payroll by adding a line within the generic import file. The line contains a maximum of 8 fields, as follows:

#63, Employee ID, Entitlement, Hours Paid, Hourly Rate, Amount Paid, Days Taken, Reason 

The specification for bereavement leave is identical to that for styles 60 to 63 apart from fields 1,3 and 7.

Field 1. Style
For bereavement leave, this is always #63.

Field 2. Employee ID
Up to 5 characters identifying the employee, as identified by Ace Payroll. This field is always validated, and import will not proceed if this field does not identify a valid employee.

Field 3. Entitlement
Currently, this field is either left blank, or contains a zero. The data contained in the line is always imported irrespective of the employee entitlement.

The specifications for this field may be enhanced at a later date.

The value of this field is currently not validated, but the field must exist. It must be either blank, or contain a zero.

Field 4. Hours Paid
This field is optional. If used, payment will be made to the employee based on the number of hours in the field, multiplied by an hourly rate.

Field 5. Hourly Rate
This field is optional, and is only relevant if a value exists in the previous field. If both fields exists, the employee is paid by multiplying the values together.

If this field is blank, and a value exists in the hours paid field, the employee is paid using the value of their first pay rate used.

Field 6. Amount Paid
This field is optional, and is only used if the hours paid field is either zero, or blank. If so, the amount in this field is paid as the total dollar amount.

Note: It is permissable for fields 4, 5 and 6 to all be blank. This is

done if the purpose of the import is to merely record the taking of bereavement leave, without making specific payment for it. This could be done if an employee is paid by salary, and the user prefers to leave the salary payment untouched.

Field 7. Days Taken
This field is optional, and if used contains the number of days bereavement leave the employee has taken. Note there is no cumulative entitlement for bereavement leave - payment is based on the occurence of a bereavement, with no limits placed on the number of bereavements an employee may suffer.

Field 8. Reason
This field is optional, and contains up to 25 characters decribing the leave entry. If the field is either blank, or does not exist, the reason is shown as "imported data".

Examples 

#62,JS,0,8 - this line pays employee JS for 8 hours leave, at the rate of their first pay rate used. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

 

#62,JS,0,8,12.75 - this line pays employee JS for 8 hours leave,

at the rate of $12.75 an hour. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#62,JS,0,0,0,115.75 - this line pays employee JS $115.75 for leave

taken. No adjustment is made to the employee entitlement, and the reason is shown as "imported data".

#62,JS,0,0,0,415.75,3,"grandfather died" - this line pays employee JS $415.75

as bereavement leave, and records they took 3 days on the death of their grandfather.

#62,JS,0,0,0,0,3,"grandfather died" - this line does not pay the employee any

amount, but records they took 3 days on the death of their grandfather.

#62,JS,0,22,0,0,3,"grandfather died" - this line pays the employee 22 hours at

their first pay rate used, and records they took 3 days on the death of their grandfather.

Style #70 is used for importing holiday pay data from an external source, such as a third party timeclock.

Ace Payroll supports the payment of holiday pay using all the methods currently used by New Zealand businesses. These methods are:

Percentage Of Gross - this method accrues holiday pay as a percentage of gross. If less than a full years service this method when set at 6% gives the same result as the Annual Leave method, and a close approximation if employed longer.

Annual Leave - this method pays holiday pay exactly in accordance with the Holidays Act 2003 by accounting for both days taken and dollars paid.

Casual Holiday Pay - this method is designed for casual employees, and pays out holiday pay each pay period as a percentage of gross.

No Holiday Pay - this method is used for the rare situations in which no holiday pay is paid to an employee, such as the employment of contractors, etc.

Within some of the above methods, different methods are called based on the employee circumstances at the time holiday pay is paid.

In other words, using the Annual Leave method, the calculation and payment of holiday pay is completely different depending on whether an employee is continuing their employment, or terminating it.

When designing this import style, we have excluded support for those methods where the automated import of data makes no sense. The methods excluded are:

No Holiday Pay - self explanatory. Casual Holiday Pay - if an employee is set to be paid holiday pay using this method, the holiday pay is based entirely on gross wages. No additional information is required.

Annual Leave and Percentage Of Gross - for these two methods, we have assumed that the employee is continuing their employment, rather than receiving a termination pay.

If the employee holiday calculation method is set as either "No Holiday Pay" or "Casual Holiday Pay" the line is ignored. Otherwise it is imported on the assumption the employee is continuing their employment.

Holiday Pay is imported into Ace Payroll by adding a line within the generic import file. The line contains a maximum of 9 fields, as follows:

#70, Employee ID, Entitlement, Days Taken, Daily Rate, Tax Weeks,

Amount Paid, Hours Paid, Hourly Rate

Field 1 Style

• For holiday pay, this is always #70.

Field 2 Employee ID

• Up to 5 characters identifying the employee, as identified by Ace Payroll.
• This field is always validated, and import will not proceed if this field does not identify a valid employee.

Field 3 Entitlement

• Currently, this field is either left blank, or contains a zero.
• The specifications for this field may be enhanced at a later date.
• The value of this field is currently not validated, but the field must exist. It must be either blank, or contain a zero.

Field 4 Days Taken

• This field indicates the number of days holiday being paid. The field is floating point, with up to 3 decimals.
• If the employee is paid holiday pay using the Annual Leave method, this field is mandatory. The import is not allowed to proceed if there is no value.
• For the Percentage Of Gross method, this field is strongly recommended, but is not mandatory.

Field 5 Daily Rate

Annual Leave 

• If the employee is paid holiday pay using the Annual Leave method, holiday pay is

  calculated by multiplying together the days taken, by a daily rate.

• If this field contains a value, the field value is used as the daily rate.
• If the field is blank or does not exist, Ace Payroll automatically uses the daily rate required by the Act - the greater of the employees usual pay, or the average daily rate over the previous twelve months.

Percentage of Gross 

• If an employee is paid holiday pay using the percentage of gross method, and

  this field contains a value, and field 4 contains a value, then the dollar value of holiday pay is the number of days, multiplied by the value in this field.

• If this field is blank, or field 4 does not contain a value, the amount paid depends on the values in fields 7, 8 and 9.

Field 6 Tax Weeks

• Due to increasing marginal tax rates, tax on holiday pay reduces if apportioned over a number of weeks as described in the Taxation of Holiday Pay FAQ.
• This field is optional, and passing an integer value between 1 and 8 apportions tax on the holiday pay over that number of weeks.
• Tax is not apportioned over any additional weeks if the field is omitted, if the field is blank, if the field contains a value of zero, or contains an invalid value.
• If a decimal value is passed, only the integral value is used.

Field 7 Amount Paid

Annual Leave 

• This field is ignored. If an employee is set to be paid holiday pay using the Annual Leave method, only the "Days Taken" and "Daily Rate" fields are used.

Percent Of Gross 

• If an employee is paid holiday pay using the percentage of gross method, and this field contains a value, then the value in this field is used in preference to values in fields 8 and 9.
• If this field is blank, the amount paid depends on the values in fields 8 and 9.

Field 8 Hours Paid

Annual Leave 

• This field is ignored. If an employee is set to be paid holiday pay using the Annual Leave method, only the "Days Taken" and "Daily Rate" fields are used.

Percent Of Gross 

• This field is optional.
• If used, and fields 5 and 7 are zero or blank, payment is made to the employee based on the number of hours in the field, multiplied by an hourly rate.

Field 9 Hourly Rate

Annual Leave 

• This field is ignored. If an employee is set to be paid holiday pay using the Annual Leave method, only the "Days Taken" and "Daily Rate" fields are used.

Percent Of Gross 

• This field is optional, and is only relevant if a value exists in the previous field. If both fields exists, the employee is paid by multiplying the values together.
• If this field is blank, and a value exists in the hours paid field, the employee is paid using the value of their first pay rate used.

Examples

#70,JS,0,1.25,,,,10

Annual Leave - pays the employee 1.25 days at their average daily rate, and reduces their holidays entitlement accordingly.
Percent Of Gross - records that 1.25 days of holiday have been taken, and makes payment of 10 hours at the first pay rate used by the employee.

#70,JS,,,,,,40

Annual Leave - will fail validation and import cannot proceed, because no days have been specified.
Percent Of Gross - pays 40 hours of holiday pay at first rate used. No days taken can be recorded, which results in incomplete holiday records.

For example:

#89

deletes the file after importation. To provide more detail within the import file, you could precede the #89 line with a comment. For example:

! delete file after importing 
#89

Style #99 introduces the facility for the supplier of an import file to communicate with the Ace Payroll user, via the import file, without the user having to open the file in Notepad or similar.

The text circled in pink is created by placing the following line anywhere in the import file:

#99,"[center]Hi John. This file contains attendance records for the[br][b]week ending 22/06/2003[/b][/center]"

Note the following: