Migrating data is one of the most mysterious parts of switching systems for new accounting software buyers. Even for experienced buyers who have previously implemented software, the big question remains: How easily will our data transfer?

The dream is to transfer data with a couple quick commands, in one try, with no headaches. But there's always the fear of the nightmare scenario that you'll need to manually enter data and burn up days or weeks of valuable time in the process. The reality is almost always somewhere in between these extremes.

Dan McGuckin Consultant, TAG Management

In order to pull back the curtain on what goes into transferring data from one accounting system to another, I connected with Dan McGuckin from TAG, an accounting software provider offering programs from NetSuite and Sage, as well as their own Contractor V product. Dan has 20 years of experience in designing and launching enterprise application initiatives and has personally assisted on hundreds of implementations.

To get a better grip on what you can expect when migrating accounting data between systems, read on to get answers on the following questions:

Why is there no open-source standard for accounting data?
What's the best case scenario for moving data?
What's the worst case scenario for moving data?
What role do conversion "utilities" play?
What are typical data conversion challenges?
How much time will converting software take?
What issues can occur if data isn't moved properly?

Every Accounting Software Developer Has a Unique Data Structure for Storing Application Data. Why Do You Think an Open-Source Standard Hasn't Evolved?

There's primarily two reasons that an open-source standard hasn't developed. First, different types of industries and companies require different data elements within their customer record. Second, the need to export customer data out of an ERP package, for the purposes of an ERP conversion, simply isn't a high priority for software publishers.

Can You Describe the Best Case Scenario for Converting Data From an Existing Program to a New One?

The easiest type of conversion is when only G/L balances need to be converted. Otherwise, the best case scenario is when the data is "pristine," meaning that:

There is no clean-up needed. There are no old inventory items that cannot be filtered-out during go-live export.
There are no fields that need to be changed during import (i.e. the item description is accurate).
Validated fields are the same across legacy and new system. For example, the code used for an inventory valuation (i.e. average) is the same code setup in the new system.

Worst Case?

The worst-case scenario is essentially the opposite of the above. At a certain point, the customer's entire chart of accounts will need to be redone. This has a cascading effect on everything else since the subsidiary ledgers generally call out G/L accounts.

A common source of conversion complexity is when exported data from the legacy system requires a lot of clean-up. This not only takes time, but mistakes can be made during this clean-up effort. Things can get especially complex when validating fields (i.e. terms codes, vendor codes, valuation codes) when they're not the same across the legacy and new ERP system. Great care must be taken so that the intermediary Excel spreadsheet that will be the source of the data for the new system is correctly updated to reflect the new coding system. Bringing over complete AR and AP history further complicates things.

When Software Providers or Implementation Support Providers Describe Data Conversion "Utilities," What Specifically Is Being Described?

Generally, this is meant to describe a data import program that is designed to add records to a "list" type table in the new ERP system. An intermediate holder of data is usually an Excel spreadsheet which holds the data after it is exported from the legacy system and before it is imported into the new system. By virtue of having this Excel spreadsheet, it allows for the editing of data before it is imported into the new system

Conversion utilities may also refer to an API, but since 99% of the time data needs to be modified before being imported, API's are not generally used.

I'd like to model a data conversion. Can you provide a step-by-step process for how, let's say, accounts payable vendor records would be transferred into a new program?

The basic steps would be as follows:

The customer is provided an Excel spreadsheet that will be used as a template for the import of vendor data into the new system.
The vendor data is exported from the legacy system and the columns in the spreadsheet are matched to the fields in the template.
The data in the spreadsheet is manipulated as needed to match a) field size, b) validating fields in the new system (vendor terms code), additional fields are populated when additional vendor identifiers need to be added to the vendor record in the new ERP system.
A program is run from the new system that will populate the new system with the records populated in the spreadsheet.

What Sort of Obstacles Would a Technically Proficient IT Staff Member Face When Moving Data Into a New Program They Haven't Worked With Previously?

The biggest issues is that the staff member would need to have a full data dictionary listing of all the fields in the new ERP database. If there's any lack of visibility to field properties such as type (number vs. integer), length, or validation, mistakes are inevitable.

Is There a Reliable Way for Buyers to Estimate the Time It Will Take to Convert Data From Their Existing Software to New Software?

In order to estimate the required time to convert data, it's necessary to estimate both the time involved in data cleansing and the time involved in doing the import.

In regards to the data cleansing time estimate, there's a number of variables. Can the data be normalized before exporting to Excel? How many records need to be changed? Can the records be changed individually? Answering those questions--which generally needs to be done by the customer--will determine the data cleansing time estimate.

The time estimate for importing data is a bit more predictable. I would budget 6-8 hours per record type. For instance, I'd estimate about 4-8 hours for a customer import and 4-8 hours for the vendor import. For the import of a clean sales history file, budget 12-24 hours.

What Kind of Issues Can Occur if Data Is Not Properly Converted? What Are the Most Likely Things to Go Wrong?

During the data import, if the new system runs into a bad record (one or more fields do not match the required properties of the field), the system will generally tell the user about the source of the error. The detail of the error, and therefore the amount of time required to figure-out the reason for the record rejection, varies across ERP systems. The other thing that can go wrong is that when a trial transaction is run using the record that you have imported, the system will throw an error.