From,Great,Plains,SAP,Business computer From Great Plains to SAP Business One: Data Export, Cleansin

----------------------------------------------------------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 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

Expected audience of this paper is IT managers.  It might be not technical enough for programmers.  We will review various flavors of GP and recommend the way of data export:1. Great Plains Accounting for DOS, Windows or Mac 9.5, 9.2 and earlier versions.  This is Btrieve application and in your case it might be redeployed on Pervasive SQL Server 2000.  For data export you can use ODBC compliant software such as Microsoft Access or Excel.  We recommend Pervasive SQL Control Center where you can use SQL queries against Btrieve tables.  In order to connect you need DDF files: File.ddf, Field.ddf and Index.ddf.  Please, look at your company database folder as they might be already there (they might be created in the past for something like Crystal Reports).  If you do not have these files then you can create them by reading instructions in ODBC manual (available on installation CD).  However you need to have floppy disk as described in the procedure.  Please copy DDF files to company DB directory (default company is hosted in GPS\GPData).  In order to connect in Pervasive SQL Server you need to create new database which will be based on new DSN.  If you plan to export something like historical sales orders then there are nuances.  File ORDHST.Dat hosts all related tables including order headers, lines, taxes, addresses and others.  If you simply try to open one of this tables, copy and save results into text file – it might be too challenging to cleanse data in Excel and columns will be shifted to the left.  Instead try Export feature with precise SQL Select statement where you are mentioning only field required for conversion.  GPA DOS is probably the most challenging one in the sense of exporting records2. Great Plains Dynamics on Pervasive SQL and Btrieve 7.5, 7.0, 6.0, 5.5 and earlier.  Use GenDDF utility to produce DDF files.  They should be copied into company directory which typically is under Dynamics folder on the server.  Table names are in ‘Unix convention’ but it is described in client application: Tools -> Resource Description -> Tables.  They are categorized by series.  Try to find Sales Order History Header and its name is SOP30200.  If you got it then the rest should be easy enough.  Tools selection is the same as described in paragraph above where we do not see challenges as in the case of GPA3. Dynamics GP and eEnterprise.  Before GPS acquisition by Microsoft there were SQL Server based brands such as eEnterprise, Dynamics C/S+ as well as Great Plains Select on MSDE 2000.  They had versions 7.5 and earlier.  Microsoft Business Solutions rebranded application as Dynamics GP with releases 7.5, 8.0, 9.0, 10.0 and 2010 as we are writing these lines in June of 2012.  Use SQL 2000 Query analyzer or if you are on 2005 or 2008 then Management Studio.  Review table structure: Tools -> Resource Description -> Tables4. Data Cleansing.  Usually for data import you use Data Transfer Workbench with predefined templates in CSV format.  Whatever you pumped out from GP you need to restructure and save in the format of specific template file.  This work is typically done in Excel.  If you have millions of extracted historical documents then we recommend the following technique.  Import text file into SQL custom table (data import wizard).  Then create view in exact structure of template and save the results5. Import into Business One.  Please expect several attempts as errors might be inevitable.  Use test company for the target and do not do it in production until experiment is successfulLet’s now take and answer questions:Q.  In our case we were able to pump out data from old Great Plains.  We gave directions to our consultant to migrate as much as possible.  We need historical documents in Sales, Purchasing, Inventory and General Ledger.  We expected conversion budget to be below fifty hours.  However the work is still not done and we are over budget.  Consultant seems to be experienced and knows what he is doing.  But it is too much time to convert one object and we expect at least two hundred templates to be processedA.  We would like to say that data conversion project scope should be restricted or even better say minimized.  The idea to ‘convert everything’ might be mathematically possible but likely not feasible budget wise.  Revisit the plan with your consultant and try to restrict the number of templates.  Why don’t you keep GP running for document inquiries and do not convert historical sales and purchase orders?Q.  Our attempts to connect to old accounting database were not successful.  But we believe that we need just minimum to export.  Do you have suggestion outside of ODBC route?A.  When we are exporting from such small business accounting applications as QuickBooks we usually print reports into text files and then process them in Excel to week out header, page number and blank linesPlease call us 1-866-304-3265, 1-269-605-4904, [email protected].  We have local presence in Chicagoland, Southern California, Atlanta Georgia, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA and Canada nationwide and internationally via web sessions and phone conferences (Skype is welcomed).  We are working on Brazilian ERP consulting market in Sao Paulo since 2004