Migrating Data into SAP Business One

Jul 21
08:14

2011

Andrew Karasev

Andrew Karasev

  • Share this article on Facebook
  • Share this article on Twitter
  • Share this article on Linkedin

There are several powerful tools, developed by ISV channel and they are obviously pricy and require consulting budget. If you ask SAP B1 consultant on how we move our old accounting documents and master records into the new system, the answer likely to be that we will use Data Transfer Workbench

mediaimage

And what are the steps to prepare integration?  They are first export data from legacy system,Migrating Data into SAP Business One Articles second save then in predefined CSV templates and the third pull these CSV files via Data Transfer Workbench.  This way doesn’t look like a good option for large scale historical data import.  However do not give up yet, we would like to give some highlights on how it really should be done with free tool, such as DTW:

1. ODBC connection option versus CSV or sometimes called Excel templates.  It is available in Workbench.  Let’s think about advantages in large scale historical data migration project.  First of all you should expect migration to be tried in test environment with multiple attempts until you are satisfied with its quality and results.  Second Excel is not designed to bring over millions of rows.  Such migration volume should be done on the SQL platform.  To our knowledge ODBC connection in WB is outside of the scope in basic SB1 certification classes.  But let’s anyway try to open the hood of the application and see how it could be done

2. Create SQL View which replicates precisely the structure of CSV template, including the row of headers.  You should be familiar with Union construction, where the first select statement is producing the headers and the second Select statement is pulling out the lines of data.  We found some difficulties to create linked server on MS SQL Server 2008 64 bit platform.  If you feel that it is also your issue, consider exporting your legacy documents into text file and then pull them into SQL via data import wizard or SSIS.  When you have data in custom SQL tables, here is the time to create smart SQL View

3. Popular migration paths.  We found out during our five plus years old SAP BO consulting practice that popular abandoned applications are Great Plains Accounting for DOS 9.5, 9.2, Great Plains Dynamics on Pervasive SQL Server 2000 version 7.5, 7.0, 6.0, 5.5, QuickBooks, Peach Tree.  We also figured out that exporting records from your legacy accounting might be quite challenging and this is why we would like to give you some directions.  Let’s take a look at mentioned applications one by one

4. Great Plains DOS, Windows or Mac versions.  This is archaic Great Plains, not Dynamics.  The database platform is Btrieve, however modern installs are mostly found on Pervasive SQL 2000 Server and single user workstation.  In order to connect to Btrieve/Pervasive via ODBC you need to produce DDF files.  The procedure is described in ODBC manual, available on GPA installation CD in PDF format.  We recommend Pervasive Control Center, where you can issue Select statement to save results into text file.  The challenge here is associated with the fact that GPA stores several tables in the same Btrieve file.  This is why when you are looking at Select statement results you may find the rows filled with unknown characters.  If you feel comfortable to work with GPA users to design the filter in SQL View to just get specific table results – great.  Otherwise you need help from experienced consultant

5. Great Plains Dynamics on Pervasive SQL 2000.  Table structure could be analyzed via user interface: Tools -> Resource Descriptions -> Tables.  As it is Pervasive/Btrieve database you will need DDF (Data Definition File) to be available for connection either in Pervasive SQL Control Center or via ODBC compliant tools, such as probably Microsoft Access or SQL Linked Server

6. QuickBooks.  As DB platform is proprietary we recommend printing reports into text files and then begin cleansing from there.  Similar approach is recommended for Peach Tree and MYOB.  Again good strategy is to import the reports into custom SQL tables and then build SQL View to weed out non structured rows, usually page number, report header, blank lines, notes

7. The author is enthusiast of various data import projects.  He participated in migrations to Dynamics GP, AX, SL, SAP B1.  You should understand the risks and advantages to get historical data migrated.  If you are small company with limited budget, consider just having master records and GL beginning balances entered.  In this scenario you keep your old accounting somewhere on the corner for data inquiry only in case if old-good-days but now forgotten customer calls.  Author also believe in the idea that if you find this article via Google, Yahoo or MSN search chances are high that real expert is found.  Internet is the market place and the place to be the arena of competitors.  We do our community service job by publishing technical articles

8. Please call us 1-866-304-3265, 1-269-605-4904 (for international customers, where our representatives pick up the phone in Naperville and St. Joseph, MI call center).  help@efaru.com  We have local presence in Chicagoland, Southern California, South West Michigan, Houston and Dallas areas of Texas. We serve customers USA, Canada, Mexico, Brazil nationwide and internationally via web sessions and phone conferences (Skype is welcomed). Our consultants speak English, Spanish, Portuguese, Russian, Chinese.  Our core expertise is in International Business