Employee Import via Excel / CSV

Follow

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

The Import Employees feature can be found in the menu under the Business Management heading after you have clicked on Payroll Settings...

QBO users click on 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.

After selecting your file, you will need to click Confirm Upload and the import will begin.

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 need to be present.

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
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
ExternalId Text Can be the id of the employee in some other system (eg: HR)
ResidentialStreetAddress Text  
ResidentialSuburb Text  
ResidentialState Text  
ResidentialPostCode Number  
PostalStreetAddress Text  
PostalSuburb Text  
PostalState Text  
PostalPostCode Number  
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
Tags Text Pipe (‘|’) separated list of tags to associate with this employee

Tax File Declaration

Field Name Data Type Notes
EmployingEntityABN Number  
EmploymentType Text Valid values: Full Time, Part Time, Casual, Labour Hire, Superannuation Income Stream
PreviousSurname Text  
AustralianResident TrueFalse  
ClaimTaxFreeThreshold TrueFalse  
SeniorsTaxOffset TrueFalse  
OtherTaxOffset TrueFalse  
HelpDebt TrueFalse  
AFSDebt TrueFalse  
IsExemptFromFloodLevy TrueFalse Only used for 2011/2012 financial year.
IsApprovedWorkingHolidayMaker TrueFalse  
HasWithholdingVariation TrueFalse  
TaxVariation Number Should only be specified if HasWithholdingVariation is 'Yes'
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

Field Name 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
HoursPerWeek Number Standard number of hours per week 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
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

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
Field Name Data Type Notes
BankAccount1_BSB Text  
BankAccount1_AccountNumber Text  
BankAccount1_AccountName Text  
BankAccount1_AllocatedPercentage Text  
BankAccount1_FixedAmount Text Percentage or Fixed amount may be specified.
BankAccount2_BSB Text  
BankAccount2_AccountNumber Text  
BankAccount2_AccountName Text  
BankAccount2_AllocatedPercentage Text  
BankAccount2_FixedAmount Text Percentage or Fixed amount may be specified.
BankAccount3_BSB Text  
BankAccount3_AccountNumber Text  
BankAccount3_AccountName Text  
BankAccount3_AllocatedPercentage Text  
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
Field Name Data Type Notes
SuperFund1_FundName Text  
SuperFund1_MemberNumber Text  
SuperFund1_AllocatedPercentage Text  
SuperFund1_FixedAmount Text Percentage or Fixed amount may be specified.
SuperFund2_FundName Text  
SuperFund2_MemberNumber Text  
SuperFund2_AllocatedPercentage Text  
SuperFund2_FixedAmount Text Percentage or Fixed amount may be specified.
SuperFund3_FundName Text  
SuperFund3_MemberNumber Text  
SuperFund3_AllocatedPercentage Text  
SuperFund3_FixedAmount Text Percentage or Fixed amount may be specified.
SuperThresholdAmount Number  
MaximumQuarterlySuperContributionsBase Number  
RosteringNotificationChoices Text Valid values: Email, SMS, None

 

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:

  • 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
    • 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

Powered by Zendesk