Contents
Data migration template overview and best practice guide
Overview
We have created Data Templates to assist with importing data into your Advanced HR system. These have been seperated into files to simplify the process and easily identify what you need to get started.
Data types and format
All sheets provide the data type, format and column name with a colour coded key and helpful tips.
Validating and checking your data
When you have completed your data on the sheet, there are a couple of checks you can do to ensure the import will be successful.
The files come with a macro we have created that helps to validate your data. This macro may be blocked by Excel:
To unblock the macro, open the properties of the file and select 'Unblock' in the General tab
CTRL+SHIFT+E to validate data formatting
Pressing CTRL+SHIFT+E will validate the content of the cells. The cells will be checked against the data type and format specified in the first two rows in the sheet. If the data does not match the required type and format it will highlight the cells in red. You can filter on the colour red to locate any errors and correct.
CTRL+SHIFT+M to check for multiline cells
You can import using .csv or .xlsx file. However, in our experience, Imports are often more successful in a raw text format (such as.csv), as this removes problematic formatting that can sometimes be generated by Excel.
As .csv files are one record per line with values separated by commas, multi line fields such as notes fields and values containing commas do not import correctly.
If you have multi-line values and wish to retain that formatting we would recommend importing them separately with an Excel file type such as .xlsx after your .csv import. Alternatively, consider re-formatting the text to a single line and using the .csv file.
We've also provided a tool to identify and fix values containing commas. Pressing CTRL+SHIFT+M will identify any cells with commas, replacing them with a semicolon and highlighting in red for you to review.
You can search the columns for errors found by filtering on the colour.
Saving your files
When you have completed your sheet, copy the tab into a new book and save as .csv.
Your file can be uploaded to a Cloud Folder in Advanced HR when you are ready to import.
Best practice
Although we have provided tabs for all the tables in Personnel Records, in our experience not all users use them all.
Here's what we recommend to get you started with the basics to make best use of the functionality:
File 1: Lookup Tables
Whilst populating your Lookup Tables can be done using the Data Templates, the records created in the Lookup Tables may not contain all the details required. We recommend populating the following Lookup Tables prior to data import, using this file, to make the most of the functionality available:
Lookup tables | What you need to know |
Division | This includes a payroll code which maybe mandatory for an Advanced Payroll integration or Payroll export files. |
Department | This includes a payroll code which maybe mandatory if required for an Advanced Payroll integration or Payroll export files and contains Leave Representative email that may be used on certain leave types |
Location | This includes a payroll code which maybe mandatory if required for an Advanced Payroll integration or Payroll export files |
Job Titles | This includes a payroll code which maybe mandatory if required for an Advanced Payroll integration or Payroll export files and identifies if the title is Managerial to support Line Manager functionality and contains Base Leave Entitlement that may be used if selected in your Leave Policy |
Grade | This contains Base Leave Entitlement that may be used if selected in your Leave Policy |
Cost Centre | Maybe mandatory if required for an Advanced Payroll integration or Payroll export files |
File 2: Core HR data
With this file you will create the Personnel Records for all employees in the current tax year with related core HR Data.
Core HR data | What you need to know |
Personnel Records 1st Import | This is the first sheet that must be imported to create the Personnel Records. It contains the majority of the items required on the main record and includes mandatory items that may be required for an Advanced Payroll integration or Payroll export file |
Personnel Records 2nd Import | Additional HR data you may hold including ED&I, Right to Work, DBS and Alternative Approver details that maybe used to authorise Leave types |
Personnel Records 3rd Import | Additional HR data you may hold including Exit interview details, PAE and Pension information, 2nd Bank Account and CPD items |
Employment | Mandatory if required for an Advanced Payroll integration or Payroll export files and contains the employment information including Division, Location, Job title, Salary, Hours and Leave Policy |
Address | Mandatory if required for an Advanced Payroll integration or Payroll export files and contains address details |
Absence | All absence records |
Leave Transactions | Leave that has been purchased, sold or brought forward |
Working Pattern | Working days of the week and number of hours on those days. This informs the calculations for duration of absences |
Emergency Contacts | Employee Emergency Contacts |
Employee Login ready for Go Live | Only required when ready to Go Live |
File 3: Additional pay items
With this file you can create additional pay related items.
Additional pay items | What you need to know |
Allowances | Allowance types, payment frequency and amounts |
Benefits | Benefits types, provider, payment frequency and amounts |
Bonus | Bonus types, percentage and amounts |
Deductions | Deduction types, frequency and amounts |
Loans | Loan types, repayment frequency and amounts |
Pensions | Pension scheme, date and contribution details |
Subscriptions | Professional body, cost and frequency |
File 4: Additional Personnel items
With this file you can create additional Personnel History records with information you may have, and is relevant for your company.
Additional Personnel items | What you need to know |
Dependants | Details of dependant can be used in relevant leave types |
Appraisal | Historical appraisal detail, dates and outcomes |
CPD Summary | Historical CPD information with accredited percentages and hours |
Competencies | Competency type, date and rating |
Disciplinary | Historical disciplinary dates, details and outcomes |
Equipment Issued | Equipment issued with date issued/returned and serial numbers |
Eye Tests | Eye Test dates, costs and claimed |
Fitness Assessments | Fitness assessment dates, details and outcomes |
Flexible Working | Request dates, reasons, discussion and meeting outcomes and notes |
Grievances | Historical grievance dates, details and outcomes |
Incidents | Historical incidents dates, details and outcomes |
Languages | Language name, fluency and level |
Medical Reports | Medical report type, date requested, cost and details |
Previous Employment | Previous employment details |
Professional Qualifications | Professional body, membership details and date of expiry |
Qualifications | Qualification subject, level details and awarding body |
Qualification Units | Attached to relevant Qualification record - Type, reference, date and credits |
Referees | Referee type, dates and details |
Return to Work | RTW interview date, details and outcome |
Risk Assessments | Date, reason, equipment issued check box and date of issue |
Stat Adoption | Statutory Adoption records |
KIT Days Adoption | Attached to relevant Statutory Adoption record - date and reason |
Statutory Maternity | Statutory Maternity records |
KIT Days Maternity | Attached to relevant Statutory Maternity record - date and reason |
Stat Paternity Adoption | Statutory Paternity Adoption records |
Stat Paternity Birth | Statutory Paternity Birth records |
Stat Shared Par Leave Adopt | Statutory Shared Parental Leave Adoption records |
ShPL Adopt Leave Request | Attached to Statutory Shared Parental Leave Adoption record - date and details |
ShPL Adopt SPLIT Days | Attached to Statutory Shared Parental Leave Adoption record - date and reason |
Stat Shared Par Leave Birth | Statutory Shared Parental Leave Birth records |
ShPL Birth Leave Request | Attached to Statutory Shared Parental Leave Birth record - date and details |
ShPL Birth SPLIT Days | Attached to Statutory Shared Parental Leave Birth record - date and reason |
Training Courses (Bookings) | Historical course details |
Training Needs | Date identified, course and required by date |
Training Sponsorship | Course title, professional body, dates and details |
Training Unavailability | Date from, to and warning |
Vehicle Allocations | Date of issue, return and vehicle details |
