Employee Import via Excel / CSV

Importing Employees from an XLSX or CSV file is a great way to get set up and running quickly.
Iit is also a handy way to perform bulk updates of employee data. Please be aware if your business has over 200 locations configured, the export feature will not automatically apply a lookup table to relevant data. The Import Employees feature can be found in the menu under the "Add Employee" tab:

 

The best way to get started is by exporting the XLSX or CSV template file, adding data to it, and then re-importing it. To export the template, click the Export button and then click on the down arrow to choose an Empty Template. Then choose whether you want to work with an XLSX or CSV Template:

Screen_Shot_2019-04-04_at_6.47.00_pm.png

This file contains the column headers for the import. You simply need to add a row for each employee that you wish to import. Once you have finished editing the XLSX or CSV file, you can upload it by clicking on Import > Select File > upload file > Open > Confirm Upload?. The import will then begin.

Screen_Shot_2019-04-04_at_6.49.12_pm.png

Once the import is complete, a report will be displayed showing you the employees that were created/updated.

File Specification

There are quite a few fields in the file however, they are broken into sections as shown below and not all sections are mandatory.

Core Data

NOTE: either the IRD Number or First Name+Surname+Date of birth must be present in the file to uniquely identify the employee

Field Name

Data Type

Notes

EmployeeId

Number

This should be kept blank unless you're updating an existing employee in the system. If this is the case, then use the system generated Employee ID. 

N.B. If an external Id is used that has been used before you won't be able to save it if the 'unique external Id' setting is switched on. This setting is located on the Payroll settings > Advanced settings page. See here for more information. 

Title

Text

Valid values: Mr, Mrs, Miss, Ms, Dr

PreferredName

Text

 

FirstName

Text

 

MiddleName

Text

 

Surname

Text

 

DateOfBirth

Date

 

Gender

Text

Valid values: Male, Female, Unspecified

ExternalId

Text

Can be the id of the employee used in an external system (eg: HR)

ResidentialStreetAddress

Text

 

ResidentialAddressLine2

Text

 

ResidentialCity

Text

 

ResidentialRegion

Text

 

ResidentialPostCode

Number

 

ResidentialCountry

Text

This field is compulsory when ResidentialAddressIsManuallyEntered = True 

ResidentialAddressIsManuallyEntered

Text

Valid values: True, False

PostalStreetAddress

Text

 

PostalAddressLine2

Text

 

PostalCity

Text

 

PostalRegion

Text

 

PostalPostCode

Number

 

PostalCountry

Text

This field is compulsory when PostalAddressIsManuallyEntered = True 

PostalAddressIsManuallyEntered

Text

Valid values: True, False

EmailAddress

Text

 

HomePhone

Text

Landline phone numbers must contain 9 digits, including the area code. The area codes are:

-03: South Island and the Chatham Islands, including Christchurch, Dunedin, Invercargill, Nelson, Timaru, and Westport.

-04: Wellington region.

-06: Southern and eastern North Island, including Manawatu-Wanganui, Hawkes Bay, Taranaki, Gisborne, New Plymouth, and Palmerston North.

-07: Waikato, The Bay of Plenty, Hamilton, Rotorua, and Tauranga.

-09: Auckland, and the islands of the Hauraki Gulf, Howick, and Northland.


Mobile number must have a maximum of 11 digits and start with a prefix of '02'.

WorkPhone

Text

Landline phone numbers must contain 9 digits, including the area code. The area codes are:

-03: South Island and the Chatham Islands, including Christchurch, Dunedin, Invercargill, Nelson, Timaru, and Westport.

-04: Wellington region.

-06: Southern and eastern North Island, including Manawatu-Wanganui, Hawkes Bay, Taranaki, Gisborne, New Plymouth, and Palmerston North.

-07: Waikato, The Bay of Plenty, Hamilton, Rotorua, and Tauranga.

-09: Auckland, and the islands of the Hauraki Gulf, Howick, and Northland.

Mobile number must have a maximum of 11 digits and start with a prefix of '02'.

MobilePhone

Text

Mobile number must have a maximum of 11 digits and start with a prefix of '02'.

StartDate

Date

 

EndDate

Date

Date employment was terminated (if employee has finalised their employment)

AnniversaryDate

Date

eg: the date the employee received their qualifications

TerminationReason

Text

This field contains a pre-determined drop down list. This field can only be used if the EndDate field has been completed  

Tags

Text

Pipe (‘|’) separated list of tags to associate with this employee

 

Pay Run Setup

Field Name

Data Type

Notes

EmploymentType

Text

Valid values: Full Time, Part Time, Casual, Independent Contractor (only available if feature has been enabled, refer to Advanced Settings for more information)

PreviousSurname

Text

 

JobTitle

Text

 

PaySchedule

Text

Corresponds to the name of a Pay Schedule that you have already created. For example ‘Weekly’

PrimaryPayCategory

Text

Corresponds to the name of a Pay Category that you have already created. For example ‘Permanent Ordinary Hours’

PrimaryLocation

Text

Corresponds to the fully qualified name of a Location that you have already created. Refer below for details on Fully Qualified Locations.

PaySlipNotificationType

Text

Valid values: Email, SMS, Manual, None

Rate

Number

How much is the employee paid (may be specified as a ‘per hour’ or ‘per annum’ value)

RateUnit

Text

Valid values: Hourly, Annually, Daily

OverrideTemplateRate

Text

Valid values: True, False. Select True where an employee has been assigned a PayRateTemplate but you want to pay a different base rate of pay.

HoursPerWeek

Number

Standard number of hours per week for this employee

HoursPerDay

Number

Standard number of hours per day for this employee

AutomaticallyPayEmployee

TrueFalse

Determines whether the employee's “standard weekly hours” are automatically added as earnings lines to a new pay run

LeaveTemplate

Text

Name of the Leave Allowance Template to apply to this employee

PayRateTemplate

Text

Name of the Pay Rate Template to apply to this employee

PayConditionRuleSet

Text

Name of the pay condition rule set to assign to this employee

IsEnabledForTimesheets

Text

Valid values: Enabled, Disabled, EnabledForExceptions

WorkTypes

Text

Pipe (‘|’) separated list of work types to enable this employee to submit timesheets for

 

Emergency Contacts

Field Name

Data Type

Notes

EmergencyContact1_Name

Text

 

EmergencyContact1_Relationship

Text

 

EmergencyContact1_Address

Text

 

EmergencyContact1_ContactNumber

Text

 

EmergencyContact1_AlternateContactNumber

Text

 

EmergencyContact2_Name

Text

 

EmergencyContact2_Relationship

Text

 

EmergencyContact2_Address

Text

 

EmergencyContact2_ContactNumber

Text

 

EmergencyContact2_AlternateContactNumber

Text

 

 

Bank Accounts

  • Up to 3 bank accounts may be specified however only 1 is required.
  • Percentages across all bank accounts must total 100
  • Use an Allocated Percentage of 100 on only 1 of the bank accounts to indicate remaining balance. Note in this case percentages across all bank accounts will be greater than 100, and validation will pass if ONLY 1 bank account is allocated 100 percent

Field Name

Data Type

Notes

BankAccount1_BankCode

Text

 

BankAccount1_AccountNumber

Text

 

BankAccount1_AccountName

Text

 

BankAccount1_AllocatedPercentage

Text

Use 100 to nominate remaining balance

BankAccount1_FixedAmount

Text

Percentage or Fixed amount may be specified.

BankAccount2_BankCode

Text

 

BankAccount2_AccountNumber

Text

 

BankAccount2_AccountName

Text

 

BankAccount2_AllocatedPercentage

Text

Use 100 to nominate remaining balance

BankAccount2_FixedAmount

Text

Percentage or Fixed amount may be specified.

BankAccount3_BankCode

Text

 

BankAccount3_AccountNumber

Text

 

BankAccount3_AccountName

Text

 

BankAccount3_AllocatedPercentage

Text

Use 100 to nominate remaining balance

BankAccount3_FixedAmount

Text

Percentage or Fixed amount may be specified.

 

Rostering Notifications

Field Name

Data Type

Notes

RosteringNotificationChoices

Text

Valid values: Email, SMS, None

 

Employee Leave Year Setup

This section MUST be completed for any employee who has a leave category setup based on the employee leave year

Field Name

Data Type

Notes

LeaveAccrualStartDateType

Text

Valid Values: SpecifiedDate, EmployeeStartDate. If using system default leave allowance templates value should be 'SpecifiedDate'.

LeaveYearStart

Date

Keep blank unless LeaveAccrualStartDateType = SpecifiedDate. If using system default leave allowance templates value for SpecifiedDate should be 6 mths after the employee start date, ie employee start date is 10/10/18, value should be stated as 10/4/19.

LeaveWithoutPayAweAdjustmentType Drop down data Move anniversary date OR Reduce AWE divisor
AdjustedLeaveEntitlementDate Date  

 

KiwiSaver

Field Name

Data Type

Notes

KiwiSaver_EmployeeContribution

Number

Valid Values: 3, 4, 6, 8, 10

KiwiSaver_EmployerContribution

Number

Valid Values: 3 or greater

KiwiSaver_OptOutDate

Date

A date must be entered here for any employee whose KiwiSaver_EnrollmentStatus = OptOut

KiwiSaver_LateOptOutReason

Text

A reason must be entered here for any employee whose KiwiSaver_EnrollmentStatus = OptOut and whose KiwiSaver_OptOutDate is 57 days after the employee's start date. Valid Values: CRIT (ie did not meet the criteria to join KiwiSaver); ERIS (ie employer did not provide an investment statement for the employer chosen KiwiSaver scheme); EVNT (ie events outside of control meant the opt-out application was unable to be submitted within the 8 week time limit); INER (ie incorrectly enrolled under the age of 18); INFO (ie employer did not provide a KiwiSaver information pack within 7 days of starting employment); IRIS (ie IRD did not send an investment statement upon allocation to a default scheme); OTHR (ie other explanation).

KiwiSaver_LateOptOutReasonOtherExplanation

Text

A reason must be entered here for any employee whose KiwiSaver_LateOptOutReason = OTHR.

KiwiSaver_SavingsSuspensionFromDate

Date

The start date must be entered here for any employee whose KiwiSaver_EnrollmentStatus = SavingsSuspension

KiwiSaver_SavingsSuspensionToDate

Date

The end date can be entered here for any employee whose KiwiSaver_EnrollmentStatus = SavingsSuspension

KiwiSaver_EnrollmentStatus

Text

Valid values: Automatically Enrol, Ineligible, Not Enrolled, Opt In, Opt Out, Savings Suspension

KiwiSaver_IneligibilityReason

Text

Must be completed where KiwiSaver_EnrollmentStatus = Ineligible. Valid values: Not a citizen/permanent resident, Not normally living in New Zealand, Under 18 years of age, Working as an independent contractor (only available if feature has been enabled, refer to Advanced Settings for more information)

KiwiSaver_PreviouslyReported

Text

Valid values: TRUE; FALSE.

 

Tax Declaration

Field Name

Data Type

Notes

TaxCodeDeclaration_IrdNumber

Number

Valid number containing no more than 9 numbers. If the employee's IRD number is unknown enter 000000000 as the interim number.

TaxCodeDeclaration_DateSigned

Date

 

TaxCodeDeclaration_EsctRate

Number

Valid Values: 10.5, 17.5, 30, 33

TaxCodeDeclaration_IncomeBand

Text

Applicable for any employee where the job is secondary income. Valid Value: 1-14000, 14001-48000, 48001-70000, greater than $70001

TaxCodeDeclaration_StudentLoan

Text

Valid Values: True, False

TaxCodeDeclaration_IsNonDisclosed

Text

Valid Values: True, False

TaxCodeDeclaration_IsPrimaryIncome

Text

Valid Values: True, False

TaxCodeDeclaration_NewZealandResident

Text

Valid Values: True, False

TaxCodeDeclaration_SpecialTaxCode

Text

Valid Values: M, ME, M SL, ME SL, S, SH, SB, S SL,SH SL, SB SL, ST, NSW, CAE, EDW, STC, ND, WT (WT is only available if feature has been enabled, refer to Advanced Settings for more information)

TaxCodeDeclaration_StudentLoanRate

Number

 

TaxCodeDeclaration_ElectedExtraPayRate

 

 

TaxCodeDeclaration_MeetsMainIncomeMeansTest

Text

Valid Values: True, False

TaxCodeDeclaration_SpecialTaxCodeRate

Number

Must be completed if 'TaxCodeDeclaration_SpecialTaxCode' contains a value

TaxCodeDeclaration_FtcOverseasSuperOrPension

Text

Valid Values: True, False. If the employee entitled to Families Tax Credits, NZ Super, or Veteran's Pension (or overseas equivalents), select 'True'

TaxCodeDeclaration_IncomeAppliedToTestedBenefit

Text

Valid Values: True, False. If the employee receives an income tested benefit that is applied to the benefit test, select 'True'

TaxCodeDeclaration_StudentLoanRateEndDate

Date

A date must be entered here if the employee is eligible for SDR

TaxCodeDeclaration_SpecialTaxCodeRateEndDate

Date

A date can be entered here if TaxCodeDeclaration_SpecialTaxCodeRate contains a value

TaxCodeDeclaration_RegisteredForGST

Text

Valid values: True, False. Value only needed if employment type is independent contractor (only available if feature has been enabled, refer to Advanced Settings for more information)

 

Minimum Required Fields

To setup an employee to be processed in a pay run the following fields are required as a minimum:

  • FirstName
  • Surname
  • DateOfBirth
  • ResidentialStreetAddress
  • ResidentialCity
  • ResidentialState
  • ResidentialPostCode
  • PostalStreetAddress
  • PostalSuburb
  • PostalState
  • PostalPostCode
  • EmploymentType
  • StartDate
  • PaySchedule
  • PrimaryPayCategory
  • PrimaryLocation
  • PaySlipNotificationType
  • Rate
  • RateUnit
  • HoursPerWeek
  • BankAccount1_BSB
  • BankAccount1_AccountNumber
  • BankAccount1_AccountName
  • BankAccount1_AllocatedPercentage
  • TaxCodeDeclaration_IrdNumber
  • KiwiSaver_EnrollmentStatus
  • KiwiSaver_EmployeeContribution
  • KiwiSaver_EmployerContribution

Once an employee is set up in the system, import files may contain a smaller subset of fields but the following must always be included in order to be able to identify the employee to update:

EITHER:

  • IRD Number

OR:

  • First Name
  • Surname
  • Date of Birth

Fully Qualified Locations

Since locations may be nested, when importing from the CSV file, it is important to specify the Fully Qualified Location. For the following set of locations:

  • All Offices
    • Auckland Offices
      • Balmoral
    • Taupo Offices
      • Wharewaka

The fully qualified location for ‘Balmoral’ would be All Offices / Auckland Offices / Balmoral

Deleting Data

If you want to use an import file to remove data from the employee records, in bulk, you'll need to use the value "(clear)" without the quotes in the appropriate field on your import file to remove it from the matching field on the employee record.

Alternatively you will notice an option to clear the opening balances / historic gross earnings you have entered for the employee:

mceclip0.png

This works on a 'per tab' basis. If you choose to clear the data within that particular tab keep in mind the relevant reports will be updated. This feature is especially helpful if you have a high level of data entered. 

If you have any questions or feedback, please let us know via support@nzpayroll.co.nz.

Was this article helpful?
1 out of 2 found this helpful

Comments

0 comments

Article is closed for comments.