Dynamics AX Importing Master Records and Transactions from Legacy Accounting

image_f2e0565917fd0974298d6ac66a1bab6b

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


Matching School Ads
  • Available business concentrations include Accounting, Economics, General Management, Marketing, and Human Resource Management.
  • Ranked among the Best Online MBA and Online Graduate Business Programs in 2016 by U.S. News & World Report.
  • Programmatic accreditation provided by the Accreditation Council for Business Schools and Programs (ACBSP).
  • Regionally accredited by the Southern Association of Colleges and Schools Commission on Colleges (SACSCOC).

Programs:

  • Bachelor of Science in Accounting
  • Bachelor of Science in Business - Accounting

The Secret to Getting Ahead is Getting Started

Congratulations! Your interest in Independence University is an important first step toward changing your life. A career-focused degree is the key to a new career with a potentially higher income, better benefits, and more satisfaction.

We want to help you get that degree. Consider:

1.       Start Fast, Finish Fast: You can earn an Associate’s degree in 20 months, and a Bachelor’s degree in 30 to 36 months.*

2.       Save up to $5,000 on your college degree.† You can afford college with our new Fresh Start Scholarships!

3.       Tuition Assistance: Financial aid and grants are available to those who qualify.

Programs:

  • Accounting (BS)
  • Accounting - Forensic Accounting (BS)
  • Business Management & Accounting (AAS)
  • Earn your bachelor's degree in business online with GCU to deepen your knowledge base, expand your skill set and begin your business career.
  • GCU's Colangelo College of Business offers bachelor's degrees in business information systems, applied business analytics, hospitality management, marketing and more – Take your pick!
  • Pursue a next-generation education with an online program or join Grand Canyon University's vibrant and growing campus community in beautiful Phoenix, Arizona.
  • Many of our business degrees have attained elite accreditation from the Accreditation Council for Business Schools & Programs (ACBSP).
  • GCU provides quality training for business majors in servant leadership, ethics and entrepreneurism in order to thrive in a future career.

Programs:

  • B.S. in Accounting
  • B.S. in Finance and Economics
  • Degrees offered include Bachelor of Science in Accounting, Bachelor of Science in Business Administration, and Master of Business Administration.
  • Students are instructed in relevant business skills like communication, mathematics, and management.
  • Coursework emphasizes the development of project management, communication, decision-making, and problem-solving skills.
  • Flexible, 100% online and campus based programs available.

Programs:

  • BS in Accounting
  • BS in Accounting - Managerial Accountancy
  • BS in Accounting - Public Accountancy
  • And more...
  • Recognized in the first Best for Vets: Business Schools list in Military Times Edge magazine’s 2013 “Best for Vets” edition.
  • Offers MUSE (My Unique Student Experience), a content delivery system that gives students the option to watch, view, read or listen to required course materials.
  • Allows students to complete courses at whatever pace they want.
  • A DANTES-affiliated university and member of the Service Members Opportunity Colleges.
  • Offers IntelliPath, a proprietary learning technology that lets students learn at their own pace.

Programs:

  • Bachelor of Accounting
  • Bachelor's (BBA) - Accounting
  • Bachelor's (BBA) - Finance
  • Earn your degree online from Ashford University!
  • Choose from Undergraduate and Graduate classes
  • Business, Health Care, Psychology and Liberal Arts programs to choose from
  • Apply Now! Classes begin every Tuesday!

Programs:

  • BA Finance

CTU can help you connect to what matters most: a powerful professional network, faculty who are real-world professionals and innovative technology. And now several CTU degree programs are ranked by U.S. News Best Online Programs for 2015.  Are you in?

Programs:

  • Bachelor of Science in Accounting
  • Bachelor of Science in Business Administration - Finance
  • Designated as a 2015 Military Friendly® School by Victory Media, publishers of G.I. Jobs®.
  • Each program is designed to instill the knowledge, ethical values, and interpersonal skills of professional practice and to foster values of social responsibility.
  • Offers several flexible learning options, including a blended format that combines campus and online learning.
  • Several scholarship opportunities are available for students who qualify.

Programs:

  • Business Administration - Accounting (BS) (Online)
  • Business Administration - Finance (BS) (Online)
  • Online and ground-based program areas include Fashion Marketing and Management and more.
  • Students can study branding, event management, and public relations to help them prepare for a marketing career
  • Course topics include Fundamentals of Business, Brand Strategy, Project Management, and Principles of Market Research
  • Over 50 locations nationwide, with a student support team dedicated to helping students grow
  • Join our next Open House on 2/15!

Programs:

  • Marketing & Entertainment Management (BS)
  • Fashion & Retail Marketing (AS)
  • Online bachelor's programs include BBA, Criminal Justice, IT, Healthcare Management, Pscyhology & Public Health
  • Over 115 years of delivering quality education and personalized attention to students

Programs:

  • Business Administration (BBA)


Matching School Ads
  • Available business concentrations include Accounting, Economics, General Management, Marketing, and Human Resource Management.
  • Ranked among the Best Online MBA and Online Graduate Business Programs in 2016 by U.S. News & World Report.
  • Programmatic accreditation provided by the Accreditation Council for Business Schools and Programs (ACBSP).
  • Regionally accredited by the Southern Association of Colleges and Schools Commission on Colleges (SACSCOC).

Programs:

  • Bachelor of Science in Accounting
  • Bachelor of Science in Business - Accounting

The Secret to Getting Ahead is Getting Started

Congratulations! Your interest in Independence University is an important first step toward changing your life. A career-focused degree is the key to a new career with a potentially higher income, better benefits, and more satisfaction.

We want to help you get that degree. Consider:

1.       Start Fast, Finish Fast: You can earn an Associate’s degree in 20 months, and a Bachelor’s degree in 30 to 36 months.*

2.       Save up to $5,000 on your college degree.† You can afford college with our new Fresh Start Scholarships!

3.       Tuition Assistance: Financial aid and grants are available to those who qualify.

Programs:

  • Accounting (BS)
  • Accounting - Forensic Accounting (BS)
  • Business Management & Accounting (AAS)
  • Degrees offered include Bachelor of Science in Accounting, Bachelor of Science in Business Administration, and Master of Business Administration.
  • Students are instructed in relevant business skills like communication, mathematics, and management.
  • Coursework emphasizes the development of project management, communication, decision-making, and problem-solving skills.
  • Flexible, 100% online and campus based programs available.

Programs:

  • BS in Accounting
  • BS in Accounting - Managerial Accountancy
  • BS in Accounting - Public Accountancy
  • And more...
  • Earn your bachelor's degree in business online with GCU to deepen your knowledge base, expand your skill set and begin your business career.
  • GCU's Colangelo College of Business offers bachelor's degrees in business information systems, applied business analytics, hospitality management, marketing and more – Take your pick!
  • Pursue a next-generation education with an online program or join Grand Canyon University's vibrant and growing campus community in beautiful Phoenix, Arizona.
  • Many of our business degrees have attained elite accreditation from the Accreditation Council for Business Schools & Programs (ACBSP).
  • GCU provides quality training for business majors in servant leadership, ethics and entrepreneurism in order to thrive in a future career.

Programs:

  • B.S. in Accounting
  • B.S. in Finance and Economics
  • Recognized in the first Best for Vets: Business Schools list in Military Times Edge magazine’s 2013 “Best for Vets” edition.
  • Offers MUSE (My Unique Student Experience), a content delivery system that gives students the option to watch, view, read or listen to required course materials.
  • Allows students to complete courses at whatever pace they want.
  • A DANTES-affiliated university and member of the Service Members Opportunity Colleges.
  • Offers IntelliPath, a proprietary learning technology that lets students learn at their own pace.

Programs:

  • Bachelor of Accounting
  • Bachelor's (BBA) - Accounting
  • Bachelor's (BBA) - Finance
  • Earn your degree online from Ashford University!
  • Choose from Undergraduate and Graduate classes
  • Business, Health Care, Psychology and Liberal Arts programs to choose from
  • Apply Now! Classes begin every Tuesday!

Programs:

  • BA Finance

CTU can help you connect to what matters most: a powerful professional network, faculty who are real-world professionals and innovative technology. And now several CTU degree programs are ranked by U.S. News Best Online Programs for 2015.  Are you in?

Programs:

  • Bachelor of Science in Accounting
  • Bachelor of Science in Business Administration - Finance
  • Designated as a 2015 Military Friendly® School by Victory Media, publishers of G.I. Jobs®.
  • Each program is designed to instill the knowledge, ethical values, and interpersonal skills of professional practice and to foster values of social responsibility.
  • Offers several flexible learning options, including a blended format that combines campus and online learning.
  • Several scholarship opportunities are available for students who qualify.

Programs:

  • Business Administration - Accounting (BS) (Online)
  • Business Administration - Finance (BS) (Online)
  • Online and ground-based program areas include Fashion Marketing and Management and more.
  • Students can study branding, event management, and public relations to help them prepare for a marketing career
  • Course topics include Fundamentals of Business, Brand Strategy, Project Management, and Principles of Market Research
  • Over 50 locations nationwide, with a student support team dedicated to helping students grow
  • Join our next Open House on 2/15!

Programs:

  • Marketing & Entertainment Management (BS)
  • Fashion & Retail Marketing (AS)
  • Online bachelor's programs include BBA, Criminal Justice, IT, Healthcare Management, Pscyhology & Public Health
  • Over 115 years of delivering quality education and personalized attention to students

Programs:

  • Business Administration (BBA)