Using Excel VBA to Automate SQL Server Data Migration

Data Migration Sucess

Some simple concepts to help you with more successful conversion and migration projects.

Front-load the Automation

Automation requires more work at the start - on the front-end of a project. Create simple data connection and output tools immediately. Doing so will give your team the confidence to defer to the automation tool rather than, in a time-crunch, fall back on manual processes.

Failure to create simple connectivity tools and code at the start usually results in disbanding automation almost entirely.

Model Model Model

Model completed data in the new application. Create a sampling of records using existing data from your current system. If the app has a way to import that data, determine how that would be done to create templates. If you can export the data, do so. That can give clues as to differences between how your old system and new system view the same data.

Automate the Standards

Once you've identified how your data should be presented to the new system, do not spend time manually adjusting or fixing data. Always automate. There is a good chance you will need to test and re-do imports. If you've automated the formatting, making small adjustments is FAR simpler and accurate with code than with manual adjustments.

Los Angeles Database and Business Intelligence

How We Help Your Team

At Pulse Infomatics, we build custom database software, data conversion tools, and train, coach, and consult data analysts, financial analysts, and business analysts to better understand data and master the tools they need to get the job done.

Contact us at:

Automating Data Migration Tools - SQL Server and Excel VBA

We love software and data migration projects. In part because we are often brought in when a company is struggling with their data and, using automation, we can provide amazing value and be the hero at the same time.

Recently, a client in Los Angeles was converting from an in-house software running SQL Server to a cloud-based software running an unknown database. The new software company did not have mature tools or standards for importing data.

This video demonstrates the tool we built. Below is some additional information about the project.

Our client had been trying to use their software's reporting tools (Crystal Reports) to export data and then clean it up for the new software. Additionally, they were, in some instances, running their old software on one screen and the new software on another to screen and copying and pasting or just typing the data into the new system.

They contacted us after reaching a frustration point. We had built several other data conversation and custom report tools either in SQL Server or using VBA (both Excel and Microsoft Access).

Data Migration Challenges

Many of the challenges they faced are common to all data conversion projects. Some of those are:

  • Data discrepancies
  • Missing data & unnecessary (extra) data
  • Inconsistent format or lack of standards in their own system or data entry
  • Lack of destination data templates

Benefits of Automation

Automating data migration, similar to any business function or report automation, has a number of benefits. They include:

  • Speed of data preparation
  • Uniformity of data
  • Flexibility in making changes to output
  • Re-use of tools elsewhere in the conversation
  • More time can be spent understanding data
    because you are not wrestling tools
Posted in Consulting, Programming, SQL Server and tagged , , .

Leave a Reply

Your email address will not be published. Required fields are marked *