SAP Business One Data Migration Projects

When you are implementing new ERP software one of the first steps is data conversion. Of course conversion might need several attempts in test followed by final import. Project typically has three procedures. First one is export from legacy accounting followed by cleansing and importing into SAP B1. We'll talk about these three procedures in some details in paragraphs below. There are ISV products but in this paper we will concentrate on Data Transfer Workbench as we believe that in initial import it should cover all your needs:

1. Export from legacy system. In our practice we use two methods. First method is exporting tables into text files. This method is ideal when you have ODBC or direct connection to legacy database and familiar with table structure. For example if old app sits in MS SQL Server, Pervasive SQL or Microsoft Access you may consider direct table export. However if you are dealing with cheap application it might be hosted in proprietary DB and connection might be too tricky. In such situations we recommend printing key reports into text files with following cleansing in Excel

2. Data cleansing. In the case of Business One DTW you need to fill out so-called CSV templates. Please review them in Program Files SAP Data Transfer Workbench Templates directory. DTW workbench is simple tool and everyone is €experienced' in Excel but it of course takes time to format your exported files exactly as templates require several times in test phase. There is alternative to CSV. You can create ODBC connection to legacy tables in SQL Server and create cross-platform SQL View which got to be formatted exactly as template with first row containing column headers. Migration is iteration and if you abstracted bugs filtering in the view then second iteration is simple (weed out buggy row in the view and go ahead to push the button). If you remember about second method of pumping out data into text reports then cleansing requires additional step where you may use Excel for cutting off such rows as Page Header, Blank Line and Report Title

3. Import into Business One. When you are ready with CSV templates or direct ODBC connection then this step is simple with following review of €error messages'

Let's now take few questions

Q. In our case we need real time ongoing integration from PHP MySQL Linux based ecommerce portal (shopping carts). Would DTW do the job?
A. Probably in €quasi real time'. Scenario like the following: you are exporting ecommerce shopping carts as text file and schedule DTW integration to fire every ten minutes. If you need €real time' then you can program XML Web Service in MS Visual Studio with SDK code samples (available in C# and VB)

Q. We feel that Data Transfer Workbench is too generic and we need something more advanced. Would you recommend something?
A. Probably take a look at iBolt or Scribe

Q. We are in data conversion from Great Plains DOS (it seems to be version 9.2). There seems to be an issue to export historical sales orders. We got the export but columns seem to be shifted and it is not usable
A. We recommend you to do it in Pervasive SQL Control Center with precise SQL Select statement. Restrict the number of exported columns to the ones that are required. The problem with GPA DOS is that it hosts several tables in one DAT file producing conflicts in rows

Q. We are abandoning QuickBooks and need documents extraction from there. What is your recommendation?
A. We have done several projects by printing QB reports into text files and weeding not related rows in SQL custom table

Please visit here for more details related to Sap Business One Singapore.