Exporting Data to a Text File

At some point, a Dexterity developer needs to create a customization to export data from Dynamics GP into a text file, which is generally used for input to applications such as Microsoft Excel or legacy applications. The following example of exporting data into a text file uses a custom table that accumulates data as changes are made to the payroll deduction table called UPR00500 in GP. A SQL trigger called BKD_Ceridian_Deductdat_UPR00500 is used to accumulate the data into the table called BKD_Deductdat. To create the trigger, paste the trigger below into the Query window within Microsoft SQL Server Management Studio and select the GP company database.

A window will need to be created in Dexterity from which to launch the export. To accomplish this, open Dexterity, click the “New Resource” drop down button, then click “Form.”

Name the Form, select the series “Payroll,” then click the “New” button to create the window.

In the above example, the “Export All Files” push button has been added to export the data into the text file. The global field “String 132” is used on this window to store the folder location of the text file; “Lookup Button 1” is used to browse the folder locations for the text file.

Sample Code for the Lookup Button 1 Change Script

Sample Code for the BKD_Export_Ceridian Push Button Change Script

When opening a text file at the beginning (where the mode parameter is set to 0) with write access (where the access parameter is set to either 0 or 2), any text currently in the file will be deleted in preparation for writing new data. To avoid deleting existing data, open a text file at the end of the file (using a mode of 1) when using write-access. After opening a text file, the insertion point is determined by the mode parameter.

A carriage return and a line feed will follow each line. To enforce a DOS format regardless of the platform used, use the TextFile_WriteDOS() function. To write characters to a text file without a carriage return and a line feed, use the TextFile_WriteText() function.

For more information on this or other Dynamics GP topics, contact our Dynamics GP support center at gpsupport@bkd.com.

One thought on “Exporting Data to a Text File

  1. avatarDavid Musgrave

    Hey Steve

    Great Post.

    If you have the Support Debugging Tool installed (build 15 or later), you could also just call the Helper Function MBS_Export_SQL_Query_To_File to export SQL Queries to Text Files.

    http://blogs.msdn.com/b/developingfordynamicsgp/archive/2011/06/30/support-debugging-tool-build-15-released.aspx

    All you need to do is provide a SQL Query as a text field and a target pathname and the code will do the rest. Can export as comma, tab or user defined delimited. Details are in Chapter 5 of the user guide PDF.

    David

Leave a Reply

Your email address will not be published. Required fields are marked *