Tips on Using Excel When Migrating Data Between Systems

Tips on Using Excel When Migrating Data Between Systems

By | 2020-11-16T06:10:00+00:00 June 25th, 2020|Technology|0 Comments

Migrating data between two different systems can be huge pain, especially when you have thousands of rows of data to move. In this post we discuss some tips you should consider when migrating system data and how we have used Excel to help make the process easier and more transparent to our clients.

Most of our work revolves around writing bespoke software applications for clients, so each solution can be vastly different from the ones we’ve written before. However, across each project one thing usually remains the same – the need to move data from the old system (or systems!) to the new.

Sometimes the old system is just a group of folders full of spreadsheets, but it could also be another application the client wants to move away from. In each case, a large part of our work is moving and transforming the data, but we also need to be concerned with validating the data and reconciling it with the old system.

Below is a rough list of tasks required to migrate data between systems:

  1. Extract the data from the old system
  2. Clean the data and remove duplicate records
  3. Transform the data from its old schema to the new schema
  4. Ensure that the original data relationships are maintained
  5. Validate that we have all the correct data
  6. Create import scripts to get the data into the new system

You may have more steps than this when you’re migrating data, but you shouldn’t have any less. Depending on the number of rows that we’re migrating, we often find Excel to be a great tool to help in each of these steps. If you’re migrating millions of rows from one enterprise system to another, a professional data mapping tool would probably be a better choice, but for moving thousands of records from one system to another, I’ve found Excel more than suitable.

So, onto the tips. I’ll briefly discuss each step and then provide a few ideas that we’ve found useful, and which you might as well.

Tip 1 – Import JSON into Excel

If the old system stores its data in a SQL database, the whole migration process can be much easier, especially if the database columns were named in a sane manner. But what if you can’t get easy access to the database or the columns and tables are structured and named so esoterically, it would take you days to understand. If the application has an API and you can get the data out in JSON format, you can then import that into Excel. The API probably has a better naming convention for the fields, so you now have a representation of the data in a spreadsheet. Here’s an example showing how to import a JSON file into Excel.

I’m just using an example JSON file that contains the details of a few books. It’s contains a top level element named work, which is an array of book objects.

{
 "@uri": "https://reststop.randomhouse.com/resources/works/?start=0&max=3&expandLevel=1&search=Grisham",
 "work": [
 {
 "@uri": "https://reststop.randomhouse.com/resources/works/72140/?start=0&max=3&expandLevel=1&search=Grisham",
 "authorweb": "GRISHAM, JOHN",
 "onsaledate": "2004-06-01T00:00:00-04:00",
 "series": "John Grisham",
 "titleAuth": "John Grisham Value Collection : John Grisham",
 "titleSubtitleAuth": "John Grisham Value Collection : A Time to Kill, The Firm, The Client : John Grisham",
 "titleshort": "JOHN GRISHAM VALUE COLL (CD)",
 "titleweb": "John Grisham Value Collection",
 "workid": "72140"
 },
 {
 "@uri": "https://reststop.randomhouse.com/resources/works/72134/?start=0&max=3&expandLevel=1&search=Grisham",
 "authorweb": "GRISHAM, JOHN",
 "onsaledate": "2003-09-09T00:00:00-04:00",
 "series": "John Grisham",
 "titleAuth": "Bleachers : John Grisham",
 "titleSubtitleAuth": "Bleachers : : John Grisham",
 "titleshort": "BLEACHERS",
 "titleweb": "Bleachers",
 "workid": "72134"
 },
 {
 "@uri": "https://reststop.randomhouse.com/resources/works/72137/?start=0&max=3&expandLevel=1&search=Grisham",
 "authorweb": "GRISHAM, JOHN",
 "onsaledate": "2008-10-07T00:00:00-04:00",
 "titleAuth": "El cliente : John Grisham",
 "titleSubtitleAuth": "El cliente : : John Grisham",
 "titleshort": "CLIENTE, EL",
 "titleweb": "El cliente",
 "workid": "72137"
 }
 ]
}

Follow these steps to import the data into Excel. If you want to follow along, just copy the JSON data from above and save to a file on your computer first.

Import JSON to Excel

 

  1. Click on the Data tab in ribbon menu
  2. Click on the Get Data button
  3. In the drop down menu, hover over From File
  4. Choose the From JSON menu item

A file dialog will appear, navigate and choose the JSON file you want to import. Once complete the Power Query Editor will load and show show you the top level elements within the JSON object.

We want to expand our array of objects, which is displayed here on the row named work.

  • Click on the List element

This navigates you into the list, and shows that there are 3 records within it.

  • Click on the To Table button, keep the defaults and click ok

This turns our list into a column, which we can now expand and then import.

  • Click on the icon highlighted in the image above. This will open the expand column dialog shown below:

  1. Choose the fields from the JSON object that you want to expand
  2. Untick this or all your columns will be named with column1 as a prefix
  3. Click OK

 

All the fields are now expanded and ready to import

  • Click on Close & Load to bring the data into Excel

Once complete, all the data will be loaded into Excel.

 

Tip 2 – Use the INDEX & MATCH functions to validate data

Many people use the VLOOKUP function to find values across sheets in Excel. However, a more powerful option is to use the INDEX and MATCH functions together to do the same job. There are many reasons why INDEX MATCH is superior to VLOOKUP, but the main one for me is that I can lookup a value in, say Column B and then return the value from Column F based on the found value. This is not possible with a VLOOKUP and makes matching up data very straight forward.

After migrating data from one system to another, I’ll usually load the old data into a worksheet in Excel and the new, migrated data into another worksheet. I’ll then use INDEX/MATCH in the new data sheet to bring across a few key columns from the old data. You can then put some simple Conditional Formatting on the columns so that errors stand out and can be found visually.

This is a great way to provide migration reports to business users and to get their input and sign-off on the final migration.

About the Author: