Dynamics,Converting,Data,from, computer Dynamics AX Converting Data from Great Plains Dynamics and O

Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc ----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a

Probably the easiest and user friendly tool in AX to migrate legacy accounting data is Excel Import.  Often the question is about how to export data from Great Plains Dynamics to cleanse it and save in Excel templates for further import into Axapta?  Sometimes people refer as Great Plains its predecessor Great Plains Accounting versions 9.5, 9.2 or earlier for DOS, Mac or Windows.  It sounds similar, but the table structure in GPA is different and this archaic Corporate ERP application requires special consideration in data extraction.  Let’s review popular data export technologies often deployed in exporting from all kinds of Great Plains ERP legacy products.  We will begin with Dynamics and later on Accounting for DOS:1. GP Dynamics and eEnterprise.  Former Great Plains Software was branding Dynamics Select to fit smaller businesses and eEnterprise to large companies.  Select was available on Pervasive SQL 2000 or former Btrieve and Ctree/Faircom, later on it was made available on MSDE and SQL Standard.  eEnterprise was available on Microsoft SQL Server.  If you are migrating – likely that you are on historical version of GP: 8.0, 7.5, 7.0, 6.0 or maybe even earlier.  From what we are discussing so far you may expect that data access instruments are different for different database platforms2. GP table structure.  There are several methods to discover the structure.  In our opinion the simplest one is to open user workstation and look at Tools -> Resource Descriptions -> Tables.  They are categorized by series: Financial, Sales, Purchasing, Payroll, Inventory, Project, etc.  Names are not human friendly, but they have pretty strict naming convention.  Prefix specifies the module, as for example SOP means Sales Order Processing.  Next to the prefix is Master or Document Status number, where 0 indicates that this is master table (customer master is RM00101), 1 tells you that this tables stores not yet posted work documents (SOP10100 is Sales Document Header).  There are also posted (open) and historical (when you move documents to history) tables: 2 and 3 respectively.  Try to find SOP Historical Transaction Header table, we’ll help you to guess – its name is SOP30200.  Second method to review the structure is in SDK, which could be installed from CD #2 for legacy versions (current versions 2010 and 10.0 have SDK on installation DVD).  SDK has huge table relation diagram, if this is what you would like to see and research.  If you were able to get the idea about table structure and your legacy ERP is on MS SQL Server platform – we would assume that you do not have additional technical questions and ready to export.  If you are on Btrieve/Pervasive, keep reading the next paragraph3. ODBC connection to Pervasive/Btrieve database.  Here you need so-called Data Definition Files with DDF extension.  They are not really difficult to generate on the user workstation.  Simply capture with the mouse GenDDF.set file move and drop it on Dynamics.exe.  Three DDF files will be produced: Files, Indexes and Fields.  You need to copy them to your targeted for export company folder on the server.  On the server the name of the root directory is Dynamics and under it you typically see System folder and the folders with Companies abbreviations.  Open company subfolder and copy DDF files in its root.  If you are not sure where is your Dynamics folder on the server, on the user workstation review Dex.ini file – the path should be specified there.  Pervasive SQL 2000 ODBC driver media is on the CD #2.  At this point you need to decide on data connection and export tool.  Popular instruments are Microsoft Access (linked tables) or MS SQL Server Linked Server.  If your historical tables contain several millions records – MS Access might be too slow.  In this case we recommend direct SQL queries in Pervasive SQL Control Center.  You can install Control Center from Pervasive SQL 2000 CD4. Direct Export via Custom Report.  If data export scope is reasonably modest you may decide to do the job by creation so-called Custom Report in Report Writer.  Base Custom Report on the desired table, place the fields on the report body section and go ahead with printing this report into text file.  When you got text file, strip off its header and footer and it is now ready to be imported to Excel for further cleaning.  Another popular cleansing method is importing it into custom MS SQL Server table and then create the View, which weeds out non-structured lines5. GPA for DOS, Windows and Mac.  It was based in Btrieve.  In earlier 2000th Microsoft Business Solutions released the technical recommendation on how to host this old accounting package in Pervasive SQL, leading to massive migration from Novel and Windows NT to Windows 2003 and even Windows 2008.  Important thing here to understand is the fact that earlier Pervasive SQL 2000 and its Service Packs were not compatible with modern 64 bit computing platform.  In order to move GPS (root folder of this application on the server) and redeploy it on Pervasive you have to do it on Windows 32 bit.  There is no need to dedicate the server computer, you can deploy GPS on Windows XP, Vista or Windows7 32 bit user workstation.  Exporting data from GPA is somewhat more challenging, where the reason for complexity is related to the multiple tables hosting in the same Btrieve file idea.  We recommend do initial export in Pervasive SQL Control Center.  Then you import produced text file via Microsoft SQL Server Data Import Wizard and sort out records there.  And do not forget that DDF files are required, as it was Btrieve based system.  Read ODBC manual on the installation media to get the procedure to create DDF files 6. 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),  [email protected]  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico.  If you need help with data export from Great Plains feel free to check with us