Migration To Sap Business One From Legacy Accounting
Business One data import is usually handled in Data Transfer Workbench
. This process is time consuming as you have to fill in CSV templates and try each object several times until you do not get errors and quality is acceptable. However each certified consultant should be able to do the job. If your old accounting is hosted in the database platform which is in essence forgotten by consulting community then the challenge is data export. We will review general export methods and take a look at popular ERP systems of the previous century. Then we will answer popular questions. Lets begin:
1. Two approaches. The first one is direct connection to tables and exporting from there. Popular technologies are ODBC and native drivers. Generic ODBC data export tools are Microsoft Access, SQL Linked Server and Excel. However there are thousands of others. And the second method is printing reports into text file. Then you cleanse rows in Excel or in custom SQL table. The idea is simple. Report may have all that you need in rows. But there are such lines as header, page number, summaries and blanks. All these are not SQL friendly and you need to weed them out. Good idea is to analyze your legacy DB platform first. Cheap software tends to use proprietary platform as in this case publishing company doesnt need to pay for third party DB software licenses and command low cost. Software in mid-price diapason is candidate to be hosted in one of the popular database platforms and is likely open to ODBC connection
2.QuickBooks. We would rather recommend printing report into text file and filter rows. There are ODBC connectors but they seem to require some experience and learning curve. Reports are pretty clean and data selection criteria are flexible. It is simple enough to export customers, vendors, GL transactions, etc.
3.Great Plains Dynamics on Pervasive SQL Server. ODBC connection is reasonably easy. You need to produce DDF files through GenDDF.set mechanism . Please read manuals which are available on GP installation CD. When you got DDF file please place them into company folder on the server. We recommend Microsoft Access where you use linked tables. Table name and structure are available Tools->Resource Description->Tables. If you think that you need only couple of tables for example customers and vendors then you can create custom reports in ReportWriter based on RM00101 and PM00200 tables. Just pull required fields and place them on report body and print it into text file. It is now ready for importing and formatting in Excel
4.Great Plains DOS. Company tables are usually in GPS folder in GPDATA subfolder where they are divided by series. They have DAT extension but the nature is not Ctree. Actually it is Btrieve where normally extension is BTR. Connection could be established in Pervasive SQL Control Center. Btrieve requires DDF files. Master files extraction is simple. But when you try export historical Sales or Purchase orders you may realize that fields are shifted and the result could not be used. This is why we recommend Pervasive Control Center where you can include only several required fields in SQL Select statement
Lets now take few questions and answer them:
Q. We had legacy accounting which is not mentioned in this publication. Data export is easy. Our SAP B1 consultant suggested migrate all the objects, including historical sales orders and AR Invoices, Purchase Orders and AP Invoices, GL transactions, etc. Now he is sitting and transforming exports into CSV files for DTW. However it takes a long time, results are poor and we are at this point out of budget. What is wrong here?
A. Conversion scope should be restricted and it is not practical to move everything. We usually recommend importing master records: customer, vendor, address, inventory item and GL accounts. Then enter beginning balances in General Ledger and go into production. Historical documents could be inquired in legacy system which could be up and running just for inquiries
Q. You mentioned above GPA DOS. We have version 9.2 and DDF files. We have issues with exporting posted Sales Orders and as you mentioned them above could you elaborate a little bit?
A. Yes. It seems like historical orders are all hosted in one file. And there are several tables such as headers, addresses, lines, taxes, etc. This is why you have to use Where construction in your query and use Select statement with restricted number of fields
Q. We need to export data from Great Plains Accounting MAC version. We made several calls to consulting firms and nobody knows it and they are not comfortable to deal with our project
A. GPA for Macintosh is relatively rare nowadays. It seems to be also on Btrieve but we are not sure. We could try to redeploy it on Windows XP workstation on Pervasive SQL Server and do records export job from there. We have not researched the question yet. But if you have the project then we should mention that we have GPA knowledge base where there are all known cases about this application
Q. We like the idea to print legacy system reports into text files. However our problem is huge volume of document and it is over two millions. Excel seems to be too slow in cleansing
A. Try importing text file into custom MS SQL Table and then use where constructions to filter our page headers, etc.
Q. We are exporting customer addresses from GPS Dynamics 5.5 on Pervasive 2000. We cannot find the table name. Could you give us a clue?
A. It is RM00102. See paragraph above. You need to switch series to Sales and sort by Table group technical name
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). help@efaru.com. We have local presence in Chicagoland, Southern California, Atlanta, South West Michigan and Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico and Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian and Chinese. We feature our expertise is in International Business. We provide second opinion in SB1 data migration, customization and reporting
by: Andrew Karasev
Finding The Rhythm Of Business Acumen Why Website Design Firm Is Essential To Business Success Tips To Find A Book About Starting A Business Online Dating Business: A Dream Job How To Discover If Working Your Own Business Is The Right Choice For You Common Colossal Mistakes People Make On Their Business Cards Selecting The Best Seo Company For Your Small Business Need An Agreement For The Sale Of Business? Mlm Secrets To Help Your Business Be Successful How To Keep Time On Your Side When Running A Home Business How To Gain A Superior Position In Business With Engineering Expertise Online Accounting Software,online Bookkeeping,small Business Accounting Software,online Accountant Incorporate The Best Modus Operandi For Business Payroll Services