Exporting Data from Great Plains, all the versions, including DOS

It is obviously the easiest question, if you are on the current version 2010/11.0, as every consultant can help you. It is also relatively straight forward export procedure, if your release is on Microsoft SQL Server platform. In this case all you need to know is review tables structure, we’ll touch it below, open Management Studio and begin your Select statement exercises. If you are on former Select on MSDE, we recommend you install SQL Client workstation and do the work in Query Analyzer. And now we are coming to unhappy folks, who have to deal with Pervasive SQL Server 2000/Btrieve or Ctree and related DDF files generation, running SQL queries either via ODBC connection or directly in Pervasive SQL Data Manager. These versions are 7.5, 7.0, 6.0, 5.5, 5.0, 4.0 and also previous application, Great Plains Accounting for DOS, Windows and Mac 9.5, 9.2 and earlier. Let’s begin with the guys who are in the obsolete technology disadvantage:

1. GPA for DOS export. Historically it was installed on Btrieve, but when Pervasive Software acquired Btrieve Technologies, this database was modernized and renamed into Pervasive SQL Server 2000. If you are on GPA, this probably means that you acquired the software licenses somewhere prior to 1995. If this is your case, then you probably got it on WindowsNT or Novel and since then moved it into Windows 2003 or Windows 2008 Server 32 bit edition with Pervasive SQL 2000 database engine. Now to data extraction. If you are not happy with printing reports to the file and then to do text file parsing in Excel, then our recommendation is consider Pervasive SQL Control Center and do either table export or SQL Query. In either case you need DDF files (Data Definition Files – Field, File and Index). The method to produce DDF files is described in ODBC manual for Great Plains Accounting. There might be a problem for you, as the methods requires original Floppy (obsolete Diskette format). When you are done with DDF generation, next step is to create connection to your company from Pervasive Control Center. This is relatively simple, as you need to copy DDF files to your GPDATA (if you have only one company) or specific company folder and declare it as the path to your database (Pervasive uses files system to connect to its tables). There are some shortcuts to know. Pervasive SQL syntax is slightly different from Microsoft Transact SQL. It is recommended to wrap field names into double quotes. Also it is often found that you have million or more lines to export for the single table. In this situation consider export it via SQL Query (where only required fields are specified). By unknown reason (and this is history at this point, so nobody cares about the reason) export duplicates lines (while producing correct number of field descriptions) with zeros in duplicates. Consider opening it via Write and add Field descriptions (like C1, C2, etc.) – this technique should allow you to open the file in Excel, or import it into MS SQL Server Custom table via Data Import (SSIS). Another thing to take into consideration. There might be several tables, residing in the same Btrieve file. Good example is Historical Sales Orders, all its tables: Lines, Tax, Order are sharing the same file ORDHST.DAT. Such technology might lead you to the feeling that data is not structured, corrupt, etc. This is not the right assumption. In the case of Orders the structure is accurate on the table level for the information about Order line with legitimate Inventory Item. Other records, such as Order Header are partially encoded and we recommend you to produce header for the order from the lines with connection to Customer Master Table (ARCustomer stored in CUSMAS.DAT). Well, in our opinion GPA data conversion is not an easy one and you can always contact us to get further help. Again, think first about the scenario, where you print various reports to extract the data into partially structural report text file and then try to week out such lines as Page Number, Report Headers, etc.

2. Great Plains Select on Pervasive SQL. Well, here you are in much better shape, considering DDF files generation, connecting via such tools as MS SQL Server Linked Server (please, note that in SQL 2008 there seems to be better to use SSIS). There is GenDDF.Set file, sitting in your user workstation folder. Catch it with the mice and drop it on Dynamics.exe. DDF files will be produced based on the metadata in Dynamics.dic Dexterity logic dictionary file. Then select Files, Fields and Index files with DDF extension and copy them into your company folder on the server. Now you are ready to connect to your company tables via Pervasive SQL Control Center, or via MS Access Linked Tables, Excel (via ODBC), or even create SQL Server 2000 Linked Server. What you need to review is Dynamics GP Tables Structure. Open user interface, go to Tools -> Resource Description -> Tables and enjoy the discovery. You should know such facts, as Voided transactions are stored in the same table as legitimate ones, plus such negative documents, as Sales Returns are shown with positive amounts (you have to produce minus sign in your SQL statement, based on the document type)

3. Dynamics GP 8.0, 9.0, 10.0, 2010/11.0. Please, read previous paragraph to get familiarized with GP table structure, open SQL Server Management Studio and enjoy your expertise in Select statement. You may also extract GP objects via Web Services call (based on eConnect), but we believe this is sort of luxury, as the job could be done directly in SQL Query Analyzer

4. For additional information, please feel free to call us 1-866-528-0577 or 1-630-961-5918 (this number works for international customers) or email us help@albaspectrum.com We serve you USA/Canada nationwide via remote support (web sessions and phone/Skype conferences). Local service is available in Western Michigan, Chicagoland, Southern California (LA, Orange County, San Diego), Houston area of the state of Texas

Andrew Karasev, http://www.albaspectrum.com Great Plains Dynamics GP and eEnterprise Certified Master, Microsoft MVP and consultant with 10 years and plus experience and expertise. Alba Spectrum, 1-866-528-0577, help@albaspectrum.com If you are thinking to implement Dynamics GP in challenging environment, we recommend you to give us a call