Employee Import via Excel / CSV

Importing Employees from an XLSX or CSV file is a great way to get set up and running quickly.
It 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.

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 platform, 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 platform. 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.

mceclip0.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 NRIC/FIN or First Name+Surname+Date of birth must be present in the file to uniquely identify the employee

NRIC Details

Field Name Data Type Notes
EmployeeId Number

Must stay blank for new employees as the system will auto generate the next available unique number.  

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 Drop down Valid values: Mr, Mrs, Miss, Ms, Dr
PreferredName Text  
FirstName Text Mandatory field
MiddleName Text  
Surname Text Mandatory field
Legal name Text

Optional field

Legal name will be used to populate the Statutory reports/forms. If Legal name field is blank then the employee's First name and Surname will be used.

DateOfBirth Date  
Gender Text

Mandatory field

Valid values: Male, Female

ExternalId Text Can be the Id of the employee in another system (eg: HR)
ResidentialAddressType Drop down

Options: Foreign address, Local C/O address, Local residential address

ResidentialBlockNumber Text

Only applicable if local residential or local C/O address type. Mandatory field for these address types

ResidentialLevelNumber Number

Only applicable if local residential or local C/O address type. Mandatory field if the ResidentialUnitNumber is entered

ResidentialUnitNumber Number

Only applicable if local residential or local C/O address type. Mandatory field if the ResidentialLevelNumber is entered

ResidentialStreetName Text

Only applicable if local residential or local C/O address type. Mandatory field for these address types

ResidentialAddressLine1 Text Only applicable if foreign address type. Mandatory field for this address type
ResidentialAddressLine2 Text Only applicable if foreign address type
ResidentialAddressLine3 Text Only applicable if foreign address type
ResidentialPostcode Number Mandatory field if local residential or local C/O address type
ResidentialCountry Text Only applicable if foreign address type. Mandatory field for this address type
PostalAddressType Drop down Options: Foreign address, Local C/O address, Local residential address
PostalBlockNumber Text Only applicable if local residential or local C/O address type. Mandatory field for these address types
PostalLevelNumber Number

Only applicable if local residential or local C/O address type. Mandatory field if the PostalUnitNumber is entered

PostalUnitNumber Number

Only applicable if local residential or local C/O address type. Mandatory field if the PostalLevelNumber is entered

PostalStreetName Text

Only applicable if local residential or local C/O address type. Mandatory field for these address types

PostalAddressLine1 Text Only applicable if foreign address type. Mandatory field for this address type
PostalAddressLine2 Text Only applicable if foreign address type
PostalAddressLine3 Text Only applicable if foreign address type
PostalPostcode Number Mandatory field if local residential or local C/O address type
PostalCountry Text Only applicable if foreign address type. Mandatory field for this address type
JobTitle Text  
EmailAddress Text Mandatory field
HomePhone Number  
WorkPhone Number  
MobilePhone Number  
StartDate Date Mandatory field
EndDate Date Date that employment was terminated (if employee has ceased 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 associated with this employee
Field Name Data Type Notes
EmployingEntityUEN 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. You can set the employing entity for new employees
NRICNumber Number Mandatory field. The following identification numbers will be accepted: National Registration Identity Card (NRIC), Foreign Identification Number (FIN), Immigration File Reference Number, Work Permit Number, Malaysian Identification Card or Passport Number
NRICExpiryDate Date Only needed if FIN provided
EmploymentType Drop down Options: Full time / Part time
Nationality Text  
Religion Text  
Ethnicity Text  
LegalStatus Drop down Options: Citizen / Permanent resident / Tax resident / Foreigner
ObtainedResidencyDate Date Only needed if employee is 'Permanent resident'
CoveredByEmploymentAct Drop down Options: Yes / Yes, excluding part 4 / No
EmployeeGroup Drop down Options: Private sector / Non-pensionable government / Pensionable government
ApplyFullCpfRate Drop Down

Options: Partial (graduated) employer and employee / Full employer and employee / Full employer and partial employee

Only applicable to permanent residents, if no selection made 'Partial' rates will apply during the first 2 years of obtaining residency.

SdlExempt Drop Down

Options: True / False

CpfExempt Drop Down

Options: True / False

Pay Run Setup

Field Name Data Type Notes
PaySchedule Drop down Corresponds to the name of a Pay Schedule that you have already created. For example ‘Monthly’
PrimaryPayCategory Drop down Corresponds to the name of a Pay Category that you have already created. For example ‘Full Time – Salary’
PrimaryLocation Drop down Corresponds to the fully qualified name of a Location that you have already created. See below for details on Fully Qualified Locations.
PaySlipNotificationType Drop down Options: Email, SMS, Manual, None
Rate Number How much is the employee paid (may be specified as a ‘per Month’ or ‘per Annum’ value)
RateUnit Drop down Options: Hourly, Monthly, Annually, Daily
HoursPerWeek Number Standard number of hours per week for the employee
HoursPerDay Number Standard number of hours worked per day for the employee. Value cannot be '0'
AutomaticallyPayEmployee Drop down

Options: True / False

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

TieredLeaveAllowanceTemplate Drop down Name of the Tiered Leave Allowance Template to apply to this employee
LeaveTemplate Drop down Name of the Leave Allowance Template to apply to this employee
PayRateTemplate Drop down Name of the Pay Rate Template to apply to this employee
IsEnabledForTimesheets Drop down Valid values: Enabled, Disabled, EnabledForExceptions
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 Number  
EmergencyContact1_AlternateContactNumber Number  
EmergencyContact2_Name Text  
EmergencyContact2_Relationship Text  
EmergencyContact2_Address Text  
EmergencyContact2_ContactNumber Number  
EmergencyContact2_AlternateContactNumber Number  

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_AccountName Text  
BankAccount1_BankSwift Text Up to 11 characters, validated to ensure number is valid and returns bank name for IRAS lodgement
BankAccount1_BranchCode Text

Optional, 3-4 characters

If not provided the system will use the account number and swift number provided to identify the branch code. If there are multiple possibilities for the branch code, the first one in the list will be used

BankAccount1_AccountNumber Number Mandatory, up to 34 characters
BankAccount1_AllocatedPercentage Number Use 100 to nominate remaining balance
BankAccount1_FixedAmount Number Fixed dollar amount may be specified
BankAccount2_AccountName Text  
BankAccount2_BankSwift Text Up to 11 characters, validated to ensure number is valid and returns bank name for IRAS lodgement
BankAccount2_BranchCode Text

Optional, 3-4 characters

If not provided the system will use the account number and swift number provided to identify the branch code. If there are multiple possibilities for the branch code, the first one in the list will be used

BankAccount2_AccountNumber Number Mandatory, up to 34 characters
BankAccount2_AllocatedPercentage Number Use 100 to nominate remaining balance
BankAccount2_FixedAmount Number Fixed dollar amount may be specified
BankAccount3_AccountName Text  
BankAccount3_BankSwift Text

Up to 11 characters, validated to ensure number is valid and returns bank name for IRAS lodgement

BankAccount3_BranchCode Text

Optional, 3-4 characters

If not provided the system will use the account number and swift number provided to identify the branch code. If there are multiple possibilities for the branch code, the first one in the list will be used

BankAccount3_AccountNumber Number Mandatory, up to 34 characters
BankAccount3_AllocatedPercentage Number Use 100 to nominate remaining balance
BankAccount3_FixedAmount Number  Fixed dollar amount may be specified

Miscellaneous

Field Name Data Type Notes
RosteringNotificationChoices Drop down Options: Email, SMS, None
LeaveAccrualStartDateType Text Options: LeaveAccrualStartDateType, SpecifiedDate
LeaveYearStart Date A date should only be entered here if the LeaveAccrualStartDateType setting is set as 'SpecifiedDate'. Otherwise keep blank.
LeaveAnniversarySetting Drop down

Options:

Calendar Year

Employee Start Date

Specified Date

Use Business Setting

LeaveAnniversarySettingDate Date

A date should only be entered here if the LeaveAnniversarySetting is set as 'SpecifiedDate'. Otherwise keep blank.

Minimum Required Fields

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

  • NRICNumber
  • FirstName
  • Surname
  • DateOfBirth
  • ResidentialAddressType
  • ResidentialBlockNumber
  • ResidentialLevelNumber
  • ResidentialUnitNumber
  • ResidentialStreetName
  • PostalAddressType
  • PostalBlockNumber
  • PostalLevelNumber
  • PostalUnitNumber
  • PostalStreetName
  • StartDate
  • EmploymentType
  • PaySchedule
  • PrimaryPayCategory
  • PrimaryLocation
  • PaySlipNotificationType
  • Rate
  • RateUnit
  • HoursPerWeek
  • BankAccount1_SWIFT
  • BankAccount1_AccountNumber
  • BankAccount1_AccountName
  • BankAccount1_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:

  • NRIC 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
    • Head Offices
      • Sales
    • Admin Offices
      • HR

The fully qualified location for ‘Sale’ would be All Offices / Head Offices / Sales

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.io

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

Comments

0 comments

Article is closed for comments.