Dynamics AX Importing Master Records and Transactions from Legacy Accounting


General advice is try to make data massage and conversion project scope minimal. In the case of AX data conversion is recommended to be minimal as well. Consider keeping your legacy accounting open for inquires. And in your new Corporate ERP system enter GL beginning balances, import customers, vendors, employees, inventory items and start using the new accounting on the first day of new fiscal period. In certain situations this advice doesn’t work and you have to dive into full scale historical transactions cleansing, massage and import project. Importing data into AX is not an easy routine, we recommend you to read Microsoft whitepaper, where integration tools are compared. However even more challenging for AX developer might be the project to export data from legacy accounting application. It is possible to switch to AX from large scale ERP and MRP packages, such as Oracle EBusiness Suite, PeopleSoft. We see more cases where customers are migrating from mid-market accounting packages, such as old Great Plains Dynamics, Great Plains DOS version, QuickBooks. There were some requests to migrate from SAP Business One, but this small business package seems to be too new on the US market to be retired. Sometimes customer has its own proprietary accounting system, programmed in something like MS Access, FoxPro. Let’s take and look at data export technologies:

1. Two methods to extract data from legacy ERP. The first one is to print reports into text files and then cleanse the data in MS Excel or in custom MS SQL Server Database (use Data Import Wizard or SSIS). And the second method is exporting via ODBC connection into text files go from there. If you know how to stretch ODBC connection to let’s say Great Plains Dynamics Btrieve database, this doesn’t necessary mean that this is the best method to do the job. If all you need to do is exporting customers and their addresses, then this routine might be quickly accomplished in so-called Report Writer custom reports. So, management homework seems to be required to avoid the situation when you are trapped in data conversion limbo. Let’s take a look now at the popular legacy accounting brands

2. QuickBooks. It is not the easiest one to convert, however migration route is usually via printing report into text file and further processing it in Excel or via importing into MS SQL Server custom table and then creating SQL View. In the View itself you have to weed out blank lines, page numbers, header, footer, etc. Expect several attempts, as we often see problems, associated with human entry errors. For example GL account is missing and you have to enter it and redo GL historical transactions import

3. Old versions of Great Plains Dynamics on Btrieve/Pervasive SQL: 7.5, 7.0, 6.0, 5.5, 5.0, 4.0. It is reasonably simple to establish ODBC connection and pull out records from GP tables to something like Microsoft Access or SQL Linked Server. You will need so-called Data Definition Files (DDF) in order to create ODBC System DSN. Btrieve based Great Plains Dynamics had GenDDF.set file. This file could be captured by mice, dragged and dropped at Dynamics.exe file to launch DDF files generation. When DDF files are produced you need to copy them into company folder root. You can read technical manuals in PDF directly on your legacy Great Plains user workstation folder, so this publication is for orientation only. GP tables structure could be reviewed in user interface: Tools -> Resource Description -> Tables

4. Great Plains Accounting for DOS, Mac or Windows: 9.5, 9.2 and earlier. It is also possible to establish ODBC connection to Btrieve based GPA data. However there is a complication, related to the data storage manner. GPA stored several tables in one Btrieve file. In this scenario some records, read from the table seem to be legitimate. Others seem to be filled with unreadable characters (because different table stores its records in the same file and its structure is different). Great Plains Accounting for DOS, Windows or Mac data migration often requires external consultant help

5. SAP Business One. As it is typically hosted in Microsoft SQL Server you can consider pulling records directly from original tables. If in menu you select show -> system information and then pull SB1 form – field and table name will be shown in the low left part of the form. SAP BO is also open to exporting most of its reports and custom queries into MS Excel

6. In-house programmed legacy accounting packages. Each one requires individual consideration. However DB platforms are usually easier for data export and table structure is often simple and self-explanatory

Andrew Karasev is Great Plains Certified Master, MVP, help@efaru.com 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project http://www.efaru.com and founder of Alba Spectrum information space