Employee Import via Excel / CSV

  1. Importing Employees from a XLSX or CSV file is a great way to get set up and running quickly.
    Hint: it is also a handy way to perform bulk updates of employee data

A short video on this setup can be found here.

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.

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 the "Select File…" button.

Screen_Shot_2019-03-08_at_2.57.54_pm.png


Take note of the setting Automatically create missing locations. Selecting this means that any location names listed in the PrimaryLocation and Locations column in the import that are not already set up in the system, will automatically be created. If this setting is not selected, there will be an import error if there are location names contained in the import not already created in the system.

Once you select your file, click on "Confirm Upload" and the import will begin.

Screen_Shot_2019-03-08_at_3.01.26_pm.png


When the import is complete, the results will be displayed on the screen advising the status  of each employee updated.

Screen_Shot_2019-03-08_at_3.09.51_pm.png

Screen_Shot_2019-03-08_at_3.11.31_pm.png

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 Tax File 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 column must stay blank for new employees as the system will auto generate the next available unique number
TaxFileNumber Number  
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 in some other system (eg: HR). 

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

ResidentialStreetAddress Text  
ResidentialAddressLine2 Text  
ResidentialSuburb Text  
ResidentialState Text  
ResidentialPostCode Number  
ResidentialCountry Text This field only needs to be completed if ResidentialAddressIsManuallyEntered = True
ResidentialAddressIsManuallyEntered Text Valid values: True, False
PostalStreetAddress Text  
PostalAddressLine2 Text  
PostalSuburb Text  
PostalState Text  
PostalPostCode Number  
PostalCountry Text This field only needs to be completed if PostalAddressIsManuallyEntered = True
PostalAddressIsManuallyEntered Text Valid values: True, False
EmailAddress Text  
HomePhone Text  
WorkPhone Text  
MobilePhone Text  
StartDate Date  
EndDate Date date that 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

Tax File Declaration

Data Type Notes
EmployingEntityABN Number You cannot change an employee's employing entity using this import file. Rather, you can find instructions on how to do this from here.
EmploymentType Text Valid values: Full Time, Part Time, Casual, Labour Hire, Superannuation Income Stream
PreviousSurname Text  
AustralianResident TrueFalse  
ClaimTaxFreeThreshold TrueFalse  
SeniorsTaxOffset TrueFalse  
OtherTaxOffset TrueFalse  
StslDebt TrueFalse  
StslCalculationType Taxable Earnings/Repayment Income Enter a value here where 'StslDebt' = True. If this field is left blank and 'StslDebt' = True, the default calculation type will be used, being  'Taxable Earnings'.
IsExemptFromFloodLevy TrueFalse Only used for 2011/2012 financial year.
HasApprovedWorkingHolidayVisa TrueFalse  
WorkingHolidayVisaCountry Select from dropdown This field is only required if 'HasApprovedWorkingHolidayVisa' = True. The country selected here must be the country stated in the employee's Visa.
IsSeasonalWorker TrueFalse  
HasWithholdingVariation TrueFalse  
TaxVariation Number Should only be specified if HasWithholdingVariation is 'Yes'
TaxCategory Select from dropdown  
MedicareLevyExemption Text Valid values: None, Full, Half
MedicareLevySurchargeWithholdingTier Select from dropdown If a value is selected here for an employee, the employee cannot also claim a medicare levy exemption or medicare levy reduction
ClaimMedicareLevyReduction TrueFalse Valid values: True, False
MedicareLevyReductionSpouse TrueFalse True must only be entered for if ClaimMedicareLevyReduction = True and the employee has a spouse who is wholly or partly maintained by the employee.
MedicareLevyReductionDependentCount Number This field is only required if 'ClaimMedicareLevyReduction' = True.
DateTaxFileDeclarationSigned Date Date that the tax file declaration was signed
DateTaxFileDeclarationReported Date Date that the tax file declaration was reported to the ATO

Pay Run Setup

Data Type Notes
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 ‘Full Time – Standard’
PrimaryLocation Text Corresponds to the fully qualified name of a Location that you have already created. See 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
HoursPerWeek Number Standard number of hours per week for this employee
HoursPerDay Number Standard number of hours worked per day for this employee. Value cannot be '0'.
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
EmploymentAgreement Text Name of an existing employment agreement to associate with this employee
IsEnabledForTimesheets Text Valid values: Enabled, Disabled, EnabledForExceptions
IsExemptFromPayrollTax TrueFalse  
Locations Text Pipe (‘|’) separated list of Fully Qualified Locations that this employee works at
WorkTypes Text Pipe (‘|’) separated list of work types to enable this employee to submit timesheets for

Emergency Contacts

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 or BPAY accounts may be specified however only 1 is required.

  • Percentages across all bank/BPAY 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/BPAY accounts will be greater than 100, and validation will pass if ONLY 1 bank/BPAY account is allocated 100 percent

Data Type Notes
BankAccount1_BSB Text This field also maps to the BPAY Biller Code.
BankAccount1_AccountNumber Text This field also maps to the BPAY Customer Reference Number.
BankAccount1_AccountName Text For a BPAY account, the value here must be 'BPAY'.
BankAccount1_AllocatedPercentage Text Use 100 to nominate remaining balance
BankAccount1_FixedAmount Text Percentage or Fixed amount may be specified.
BankAccount2_BSB 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_BSB 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.

Super Funds

  • Up to 3 super funds may be specified however only 1 is required.
  • Percentages across all super funds must total 100
  • Use an Allocated Percentage of 100 on only 1 of the super funds to indicate remaining balance. Note in this case percentages across all super funds will be greater than 100, and validation will pass if ONLY 1 super fund is allocated 100 percent
Data Type Notes
SuperFund1_ProductCode Text  
SuperFund1_FundName Text  
SuperFund1_MemberNumber Text  
SuperFund1_AllocatedPercentage Text Use 100 to nominate remaining balance
SuperFund1_EmployerNominatedFund TrueFalse Value can only be TRUE if the employer nominated fund has been set up via the Superannuation screen
SuperFund1_FixedAmount Text Percentage or Fixed amount may be specified.
SuperFund2_ProductCode Text  
SuperFund2_FundName Text  
SuperFund2_MemberNumber Text  
SuperFund2_AllocatedPercentage Text Use 100 to nominate remaining balance
SuperFund2_FixedAmount Text Percentage or Fixed amount may be specified.
SuperFund2_EmployerNominatedFund TrueFalse Value can only be TRUE if the employer nominated fund has been set up via the Superannuation screen
SuperFund3_ProductCode Text  
SuperFund3_FundName Text  
SuperFund3_MemberNumber Text  
SuperFund3_AllocatedPercentage Text Use 100 to nominate remaining balance
SuperFund3_FixedAmount Text Percentage or Fixed amount may be specified.
SuperFund3_EmployerNominatedFund TrueFalse Value can only be TRUE if the employer nominated fund has been set up via the Superannuation screen
SuperThresholdAmount Number  
MaximumQuarterlySuperContributionsBase Number  

Miscellaneous

Data Type Notes
RosteringNotificationChoices Text Valid values: Email, SMS, None
LeaveAccrualStartDateType Text Valid values: LeaveAccrualStartDateType, SpecifiedDate
LeaveYearStart Date A date should only be entered here if the LeaveAccrualStartDateType setting is set as "SpecifiedDate". Otherwise keep blank.
CloselyHeldReporting Text Enter a value here if 'SingleTouchPayroll' = Closely held employee. Valid values: Per quarter, Per pay run. If 'SingleTouchPayroll' = Closely held employee and this value is blank, the default reporting option will be per quarter. 
SingleTouchPayroll Select from dropdown Complete this field if your employee is classified with an income type that is NOT salary and wages, working holiday maker or seasonal worker. Further information on income types can be found here.
PayrollId Text This ID can only be changed if the business has changed the BMS ID in accordance with these procedures.

 

Minimum Required Fields

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

  • TaxFileNumber 
  • FirstName
  • Surname
  • DateOfBirth
  • ResidentialStreetAddress
  • ResidentialSuburb
  • ResidentialState
  • ResidentialPostCode
  • PostalStreetAddress
  • PostalSuburb
  • PostalState
  • PostalPostCode
  • StartDate
  • EmploymentType
  • PaySchedule
  • PrimaryPayCategory
  • PrimaryLocation
  • PaySlipNotificationType
  • Rate
  • RateUnit
  • HoursPerWeek
  • BankAccount1_BSB
  • BankAccount1_AccountNumber
  • BankAccount1_AccountName
  • BankAccount1_AllocatedPercentage
  • SuperFund1_FundName
  • SuperFund1_MemberNumber
  • SuperFund1_AllocatedPercentage

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:

  • Tax File Number

OR:

  • Employee ID

NB: Gender information will revert to Unspecified if the gender field is deleted prior to uploading.

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
    • NSW Offices
      • Strathfield
    • QLD Offices
      • Logan

The fully qualified location for ‘Strathfield’ would be All Offices / NSW Offices / Strathfield

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.

If you have any questions or feedback, please let us know via support@yourpayroll.com.au

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

Comments

0 comments

Article is closed for comments.