Tips for Data Migration banner


Tips for Data Migration

  • Posted on

I've dealt with a LOT of data over the last 16 years. Whether this was migrating customer data, supporting them in their own migration, migrating the data of my own employers, rapidly acquiring and working with data from an acquisition to ensure new employees continue to be paid accurately and on time, cleansing data, reporting on data, etc. etc.. I'd say I've seen all there is to see with HR Data.

It's a huge part of any HR System implementation project, and yet so many people enter this process with limited preparation or experience. Aside from letting me take over your data migration (I really wouldn't mind), there are a few things you can do to ensure a successful migration.

What is ETL?

Firstly, let's discussed a method that will pop up regularly. ETL stands for Extract, Transform and Load, and it's a simple enough premise in the context of HR Software Implementations. You need to Extract the information from your current system and then Transform it (whether that be cleansing the data, trimming down job titles and picklist values, losing some historical data etc). Finally you need to Load it into your new system (often with support from your vendors or implementation partners).

Timing and Planning Your Data Migration

When do you want to plan your migration? Personally, I like to run a sample data migration before User Acceptance Testing (UAT). When that's been done successfully, then a full migration can be performed with confidence prior to go-live. The benefits of this approach are numerous, and include:

  1. Giving you test data to work with for User Acceptance Testing that will look similar to your live data.
  2. Allowing you to cleanse your live data as you test and see how your data looks in your new system
  3. You get a practice run at migrations. If you learn from your test migration, it will make your live migration go smoother and quicker, meaning less time for your live data to age.

When planning your project, try to have a project resource that's dedicated to leading and coordinating data migration activities. This person needs to understand the makeup of the data, the business and any reporting implications to consider once this data becomes live. It might be that this person is supported by subject matter experts from other areas of the business who also receive information from HR.

Finally, try to make sure that the team is correctly resourced and given the time to work on the ETL process. When the 'business as usual' activities get in the way, the data preparation can suffer, leading to poor quality data which in turn sheds a poor light on your fancy new system!

Your Opportunity to Cleanse

Use the ETL process fully; this is your opportunity to truly transform your data, making it more efficient for reporting purposes and improving your admin processes across the company:

  • Roles, departments and teams can all be structured correctly. Any business that has grown through acquisitions might have experienced a disjointed organisation structure that has been inherited and appended to an existing business. Look at this process as your chance to improve that situation.
  • Make your data consistent. Through inherited spreadsheets and ill-defined processes, your old data might be inconsistent. This is a chance to put right some of the common problems we see, e.g. several variations of the same Job Titles and Departments such as:
    • R&D
    • R+D
    • R and D
    • Research and Development
    • Research & Development
  • Do your dates make sense? Are they in the correct order? Apply validation to make sure you don't have absence end dates after their start dates, or start dates earlier than continuous service dates. You can also use this time to make sure you're filling in any blanks.
  • Key Data. Think about what data is fundamental
    • Core employee data for RTI purposes
    • Salary information for payroll and finance reporting
    • Organisation information for reporting purposes
    • Next of Kin information for benefits
    • Data for IT service provision (usernames, hardware and more)


Pitfalls and Advice

This is all valuable information that you now have an opportunity to provide for your new system. Watch out though; there will be pitfalls!


Date formats can be a nightmare for those multinational organisations that have different people at a local level providing the data. Make sure that your dates are all in the same format.


Commas outside of the UK are another pitfall for multinational companies. The UK seems to be one of the few European countries that use a full stop (.) to represent a decimal place, with the majority of Europe opting for a comma. If you have to provide your data in a .CSV file that separates columns with a comma, you can probably see why this is a potential pitfall. If you are putting your data together in different countries, it's often wise to keep in XLS until it's all compiled, then saving as a .CSV file at the local level choosing an appropriate delimiter to separate values.


Excel can try to be a little too clever at times, only this week I saw it try to change Lithuanian phone numbers from a string of digits to a shortened formula. Trust me, nobody's phone number is "3.71E+10", you're trying too hard Excel! To get around this, you might need to format the columns as text before adding your data.


Picklists can be a great tool to regulate the data choices on offer in your system and to provide consistency. They can often be set up to hold dependencies, allowing a dependent picklist to only display options that are relevant to the one selected previously. For example selecting a department of "Sales" might present you with team options of "New Business" and "Customer Success". If you have any such dependencies, make sure you've prepared your data to accommodate them. Your data should be an accurate representation of your new system's structure.

Finally, with picklists, the number one thing to check is that the values in your data match the values in your system. You should be able to export your picklist values from your new system to the area you're preparing your data in (most likely Excel), let lookups become your new best friend. Vlookups will help you identify where your data will not match your new system.

Better minds than me have already explained Vlookups, so I'll not reinvent the wheel, the below is a very quick guide, but honestly, there are literally thousands of people explaining this online!


Validation is a fantastic tool employed in most systems in one way or another. Put simply, it's a way to make sure that your data conforms to any predefined rules. A basic example of this could be that probationary end date should not be before the start date.

Validation is there for a reason; you might be able to turn it off for the purposes of your data load, but that might lead to issues when it's turned back on. Instead, consider why it's there in the first place; in most cases, it's to make sure your data is clean and will work efficiently for you. I see no reason why you'd want to avoid that! Take the time in the transform stage of your data migration journey to make sure that your data meets any criteria set out in your system's validation. That way you'll have the best possible start to using that information effectively come go live.

There are further journeys into data validation that you can venture down. Regular Expression, for example, can be an excellent tool in verifying the formats of NI Numbers, Social Security Numbers, Bank accounts and Sort Codes, Post Codes and Phone numbers. In simple terms, it ensures that data conforms to a predefined format. It can get complicated, but if your new system employs regular expression, it's worth finding out before you try to load your data!

There you have it. Hopefully nothing too scary, but those are my words of advice for any newcomers to Data Migration or HR System Software Implementation.

This article has been authored by Kevin Hume, Senior HRIS Consultant for Tugela People. Kevin has spent the last 16 years working with HR and Payroll software in various roles and has over 3 years' experience working with Sage People.

Kevin can be contacted directly on:

What Next

To learn more about the support and services we offer, please contact us  on tel +44(0)1908 030368 or email: Alternatively complete the ‘Get in touch’ form and a member of our team will contact you directly.