Importing employees via Microsoft Excel worksheets (xlsx) and CSV files is a useful time-saving option, particularly when first setting-up your company’s Peakon account.
This article will serve as a step by step guide for a data import:
- Step 1: Create attributes to collect data on segments
- Step 2: Check that all employees have a unique identifier
- Step 3: Ensure that all employee email domains are allowed
- Step 4: Format the file according to the formatting requirements
- Step 5: Import the finalised data file
- Step 6: Troubleshoot errors and warnings via the error log
When updating existing employees using the data import method, the import will match and update employee records based on the employee unique identifiers:
- The Email address
- The Employee ID
If there are no matching records, the import will treat the employee as a new employee and create a new employee record on Peakon.
How to use the file import option:
- Click on Administration
- Click on Employees
- Here you will find the Import and Export buttons, which will be relevant to this guide
Tip: Download a template Excel sheet (found in the Import option).
Step 1. Create attributes to collect data on segments
Objective: A matching attribute is set up for each column on your employee file
In order for the upload to work, Peakon will need to match the columns on your sheet to the attributes on Peakon. For example, if the Excel sheet contains a column for “Location”, an attribute named "Location" must also exist on Peakon, in order for the data to sync. Read more about setting up attributes in the Company Structure section.
Default contact fields
There is no need to create additional attributes on Peakon for the following contact fields listed below, however, a column for each of the required contact fields needs to be present in the sheet:
- First name (required)
- Last name (required)
- Email (required if you don't use Employee ID)
- Employee ID (required if you don't use Email)
Attributes names need to match exactly the column headers on Excel. It's also possible to setup alias names for your attributes. For more information on setting up an alias, refer to our Configuration of attributes and segments article.
Language and Timezone
The preferred language and timezones can be set on employee records using the file upload method. To set timezone and language, ensure there is a column present on the sheet for each and refer to Setting the employee time zone via Excel and Setting the employee language via Excel for the exact formatting requirements.
To make sure your sheet is properly formatted, you can download a template file containing the attributes you’ve already setup on Peakon. To get the template, click on Import, then Download template.
Step 2. Check that all employees have a unique identifier
Objective: Unique identifiers are set on all employee records to avoid duplicate creation
Each employee record is required to have a unique identifier on Peakon, and a column for each (if using both) should also be present on your file in order for the upload to work. The unique identifier can be one of the following or both:
- The Email address
- The Employee ID
If your employees do not have email addresses, you would need to use an Employee ID and add a column in your Excel sheet named Employee ID. The Employee ID can be alpha numeric. Where possible we recommend using both Email and Employee ID. This is because Email addresses could change where as an Employee ID is likely to remain the same, making ongoing employee record maintenance easier.
Updating email addresses & employee ID
To update one of the unique identifiers, the other needs to be present in order to find a valid match within the system. If only one unique identifier (for example, the email address) is used, and email addresses are updated via a file upload method, duplicates will be created as a match will not be found in the system.
How to update email addresses correctly:
- Add Employee ID's to the employee records via a file upload
- Then do a second upload with the updated email addresses
The file for the second upload would need to contain the Employee ID column since that will be used to match with existing records going forward.
It is also important to ensure that each employee record has a Manager assigned to them on the file. This is an important step as Peakon will automatically map out the hierarchy and create a manager segments and groups in the access control section of the Employee overview page.
Ensure there is a column present on the sheet containing one of the following on each employee record:
- The manager's First name and Last name (in one cell on the sheet and spelled to match the manager's employee record)
- The manager's Employee ID (if Employee IDs are being used as the unique identifier)
- The manager's email address (recommended when using a CSV file)
The column header should match the attribute name on Peakon, which is typically Manager unless the name has been edited on Peakon.
It may be the case that some employees do not report to a manager, such as C-level employees, for example. In such instances, simply leave out the manager’s info on the Excel sheet row containing that employee record.
Step 3. Ensure that all employee email domains are allowed
Objective: All email domains have been allowed on the company account
If your employees have a range of different email domains, the Customer Care team must enable them on your Peakon account. After enabling an email domain, you can add employees with the respective email domain.
Step 4. Format the file according to the formatting requirements
Objective: Formatting has met the requirements to import successfully without errors
For the Excel sheet upload to work, it is important to ensure the following prior to uploading:
Column headers in Excel need to match the attribute titles exactly (unless aliases are used on the attributes), and it is case and space sensitive. Column headers need to be text - numbers will not be accepted.
First and last name
First name and Last name need to be in separate columns in the Excel sheet. Here’s a link showing how to split text into columns.
The employee email address column in Excel needs to be named “Email”.
The employee's manager column needs to be named "Manager" unless the attribute name has been changed on Peakon.
If using the phone number field, ensure that the column in Excel containing phone numbers is set to a text type cell - any other type may cause the upload to fail. Number type cells will not work because of the way excel treats “+” symbols. Since many phone numbers are prefixed with a “+” for the country code, this problem is avoided by using a text type cell. Here is a link to showing how to convert numbers to text.
Special characters in email addresses will not be accepted, however, regular characters found in an email address are fine: “@”, “-”, “.”, etc.
Format Excel columns to date type cells, when uploading date type attributes. You can use these standard formats:
If you're using an Excel file, any standard date format will work as long as it's a date type cell. However, YYYY-MM-DD is the all-encompassing option, which also captures text cells.
If you're using a CSV file, you must use the YYYY-MM-DD format.
Sometimes Excel stores dates as text, even when you've selected date format. If this happens, convert the dates using Text to Columns method, to import the data correctly (See I am trying to upload employee data, but I am getting an error with the date format).
The Excel sheet may only contain one sheet.
Password protected Excel sheets are not supported.
If using CSV file, make sure you're using UTF-8 encoding.
Blank cells on the sheet will remove existing values on employee records. This is especially important to remember when updating employee records using the file upload method as part of on-going employee record maintenance. Where a column that matches the attribute on Peakon is present on the sheet, and blank cells are present in that column, the upload will remove the values from the employee records for that particular attribute. This is a useful method to bulk-remove data for specific attributes.
Step 5. Import the finalised data file
Objective: Employee data file is imported using the relevant import option settings
When everything is finalized, it's time to import the data file in Administration > Employees > Import. On the import page there are three option tick boxes, which are explained below.
Skip creation of employees
This option will skip creation of new employees in an import, and is useful to tick if you do not intend to add any new employees, and only update existing employees. This can help avoid any duplication of employee records.
There are a few reasons why this setting can be useful:
- When an employee changes their email address or employee ID, and the change has not been made correctly on Peakon, a duplicate will not be created
- Avoiding adding duplicates also means that licenses will not be used up unnecessarily
Enable overwrite of employee number or email
By default Peakon ignores updates to employees, where the employee number/ID or the email would be either changed or removed. Changes to these fields should very rarely be the case, as often it will indicate a mixup of employee numbers or emails.
There are a few cases where this could be required:
- The old employee number was wrong, and the correct one should be set
- The email of an employee changed, usually either because the domain is changing or the employees name changed
- The email or employee number is being removed
In any of the above cases, the Enable overwrite of employee number or email checkbox can be checked, which will bypass the safety checks.
Use with care. In instances where you do need to update employee emails, be sure to first ensure that each employee has an allocated employee number on Peakon before updating the email addresses to avoid creating duplicates.
Set valid from
By default, any uploaded employee data will become effective on Peakon dashboards from the next survey round onward.
Checking the Set valid from box will allow you to backdate any updates to existing employee records already on Peakon and also present on your file (if also updating existing records). Backdating will take existing results and place them into different segments, if needed. If using the Set valid from option, select a date before selecting the file to upload, ie. the day before the latest survey started, in order to retroactively update the employee data for that survey.
Step 6. Troubleshoot errors and warnings via the error log
Objective: Any warnings and errors after the import have been addressed to ensure the employee data has been imported as intended
Warnings or errors may be present after the upload, and they will be listed after the upload is complete. If errors occur during the upload please refer to the Troubleshooting employee data uploads article.
Before clicking on the Okay I'm done option, be sure to make a note of the errors or download the log that will list all the updates as well as any errors for reference. For security reasons, it is not possible to retrieve the error log at a later time.