SAP Business One Data Migration Recommendations: Export, Cleansing and Final Import

Try to speculate about the idea to keep your legacy accounting running for lookup only in case when old customer calls you.  These projects typically have three phases: export from legacy accounting, cleansing and import into Business One.  Let’s review them one by one:

1. Extracting documents.  There are several methods to think about.  If it is easy enough to establish ODBC connection then all you need to know are table names and field descriptions.  Such tools as Microsoft Access or SQL Server with Linked Server construction could be considered as possible tools.  Some applications deploy proprietary DB platforms where ODBC connection might be too complex to establish.  In these situations we often recommend printing text reports into files and then cleansing them in Excel or custom SQL Server DB.  Let’s give you couple of successful cases from our practice.  Old versions of Great Plains DOS and Dynamics were hosted in Btreve database and here in order to establish ODBC connection you need DDF files.  We also like to do the job in the case of GPA DOS in Pervasive SQL Control Center.  Second example is QuickBooks where we usually print text reports and save them as files

2. Data Cleansing.  When you are extracting table rows via direct connection then cleansing is minimal.  However for SAP B1 migration is normally done via filling CSV templates and these templates require exact format which could be accomplished in Excel.  In the case of reports it is possible to sort records in Excel and weed out such formatting rows as date, page number, blank line and header.  When report has thousand pages then we recommend importing its rows into custom SQL Server table and do the job there

3. Final Import.  Be prepared to do data move to Business One several times until the quality is good enough.  Compiling CSV files might be time consuming especially when you need to repeat the whole process again and again.  In our consulting practice we found a trick allowing you avoiding these repetitions.  DTW works as with CSV files as well as with ODBC.  In the second technology we recommend you to create SQL View exactly resembling template format including the row with column headers.  When you see the next problem with the records then all you need to do is to fix the issue in the view logic and you are good to go and try migration again

Let’s take few questions from our customersQ.  Why don’t we just write SQL scripts and do data conversion by sending rows directly to SB1 tablesA.  From the mathematical standpoint the idea should theoretically work.  However the structure is complex and if you mistreat few fields then there is the risk of data integrity compromise

Q.  We still believe that we need to move ‘everything’ from our legacy application to SAP BO.  The problem is that old software works by subscription as soon as we cancel the contract we will lose the way to connect and lookup its transactionsA.  It is possible but the budget might be too high.  It is difficult to believe that you do not have the rights on your own records.  Think about exporting tables into something like Microsoft Access, Text or Excel for possible future inquiries

Please call us 1-866-304-3265, 1-269-605-4904, help@efaru.com.  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