Data Conversion to Dynamics AX and Exporting from Great Plains, QuickBooks, SAP B1

Published: 16th August 2011
Views: N/A

It is reasonably straightforward procedure to migrate legacy data into your new accounting system, including AX or formerly known as Axapta. Problem is typically associated with legacy ERP data platform, which is not friendly to modern programmers and consultants. We would like to give you data export highlights for popular switch over legacy accounting. Typically you can consider two strategies. First one would be running reports in legacy accounting and saving them as text files. And the second one is exporting via ODBC compliant tools. Let’s begin with Great Plains on Pervasive SQL 2000 and DOS and then move to QuickBooks and SAP Business One:

1. Great Plains Dynamics on Pervasive SQL 2000/Btrieve versions: 7.5, 7.0, 6.0, 5.5, 5.0, 4.0. Prior to acquisition by Microsoft, Great Plains Software was pursuing flexible strategy and its Dynamics was available on several competing DB platforms: MS SQL Server, Btrieve (later acquired by Pervasive Software and rebranded as Pervasive SQL), Ctree/Faircom. It is fairly simple to export data from old GP. First, consider creating custom report in ReportWriter and then base in on the table that you would like to export. Toss into the report form as many fields as you need and print/save report into text file. Open it in Excel and you are at the destination. This method might be not practical when you are trying to move out huge table, SOP History for numerous years for example. Well, here is second method, where you may launch export utility overnight. Install DDF generator (from original CD, prior check if your Dynamics application folder already has it there – the name is DDF.set). Catch DDF.set with the mouse, move and drop it on Dynamics.exe. File, Field and Index DDF files should be created and placed in your workstation core directory. And now there is standard methodic to create System ODBC DSN, be sure that you placed ddf files into target company folder on the server. Client and Server side Pervasive ODBC driver could be installed from original GP CD, read supporting documentation. The work should be done on 32 bit Windows Server or user workstation, as old Pervasive was not compatible with 64 bit modern day computer

2. Great Plains DOS. Great Plains Accounting for DOS, Windows or Mac: 9.5, 9.2 and earlier versions was based on Btrieve. In earlier 2000th Microsoft Business Solutions recommended redeploy it on Pervasive SQL 2000. Again, you will need to produce DDF files. Read ODBC manuals from GPA installation CD. Helping customers we figured out that it is pretty difficult to enable reliable ODBC connection to WindowNT or Novell Server. In this case we recommend copying GPS folder to WindowXP 32 bit (they are still available in stores as cheap Netbooks on Intel Atom processor) where you can install Pervasive SQL ODBC driver. If the number of records is huge, use Pervasive Control Center, compile and save SQL query as text file

3. QuickBooks. As database platform is proprietary, we usually recommend data extraction via printing report into text file. Reporting is very rich and we never heard complains about inability to export something through reports

4. SAP Business One. As it hosts its tables in MS SQL Server, data export is not a problem. The question is often posed this way – how do we identify table name? Open SB1 user interface, in menu select View and mark System Information. Now open one of the records in user interface and place mouse cursor over the field in question. The table and field name will be shown on the bottom left side of the application form. If data export scope is reasonably narrow, SAP B1 is pretty flexible in exporting queries and reports into Excel

5. Old versions of Solomon and Navision. In both cases they are typically hosted in MS SQL Server. For Navision – the trick is that if you have several companies implemented all the tables are mixed in single database. Table name is self-explanatory and company is used as a prefix in the name. Having large number of tables Navision database sometimes is failing to connect via ODBC driver in such popular reporting tools as Crystal Reports. Our recommendation is the following – instead of doing direct table lookup and link in Crystal Report connection wizard, consider creating SQL View

6. MYOB and PeachTree. In both cases you have to deal with proprietary DB platform. Consider data extract via printing reports to the text files

7. International aspects of data conversion. Here you should be aware about such things as SQL Collations and Code Pages, Unicode, UTF-8. Chinese, Japanese, Korean languages have hieroglyphic nature and implemented in Unicode (two bytes per character). Most of the other popular languages, including Russian, Arabic, Portuguese, Spanish are ASCII compliant (one byte per character). AX is available pretty much worldwide and if you have question on extracting data from such locally supported ERP applications as Microsoft in Brazil or 1C Accounting (1S Bukhgalteria) in Russian Federation, you should probably contact local consultants

8. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representative pick up the phone in St. Joseph, MI call center). We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese. Our core expertise is in International Business

Andrew Karasev is Great Plains Certified Master, MVP, 1-866-304-3265, 1-269-605-4904. He is also the initiator of eFaru project and founder of Alba Spectrum information space

Report this article Ask About This Article

More to Explore