Update Microsoft Dynamics GP Account Categories From Text File

Microsoft Dynamics GPI’ve recently been working with a client to implement Jet Reports as a replacement for Management Reporter. As part of this implementation, it was necessary to change the account categories in Microsoft Dynamics GP.

This particular client has over 1 million account strings in their chart of account which meant any update could not be done manually. While it could technically be done through Integration Manager this would have meant integrating a 1 million plus line file, which again wasn’t really feasible.

Instead what we did was have the client compile a list of the natural segment (the third segment) along with the new category and I created an update script to use this file to update Account Category Master (GL00102).

The below script creates a temporary table, imports the text file and then updates all the account category on all accounts in Breakdown Account Master (GL00100) based on the third segment.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
CREATE TABLE #IMPORT ( ACTNUMBR_3 VARCHAR(4) ,ACCATDSC VARCHAR(50) ) GO BULK INSERT #IMPORT FROM 'C:\Temp\Categories.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 1 ) GO UPDATE ['Account Index Master'] SET ACCATNUM = ['Account Category Master'].ACCATNUM FROM GL00100 AS ['Account Index Master'] INNER JOIN #IMPORT ON #IMPORT.ACTNUMBR_3 = ['Account Index Master'].ACTNUMBR_3 INNER JOIN GL00102 AS ['Account Category Master'] ON ['Account Category Master'].ACCATDSC = #IMPORT.ACCATDSC GO DROP TABLE #IMPORT GO

As always when running a script which does updates, make sure you have a good backup, test the script in a test company and verify the update before repeating on live.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Update Microsoft Dynamics GP Account Categories From Text File

  1. Michael Nola says:

    Thank you for the post (very helpful). Had done something similar several years back for a client that we need to run the process again because of a large change in all their COA.

    One thing to note is if you perform the update directly via SQL, you will want to do the following to address the Account Category Number that is stored in tables outside of GL00100:

    a. Run Check Links against the Budget Master and Account Master (both under Financials).
    b. Run another SQL script to update the Account Summary History File (GL10111) that sets the Account Category Number equal to the Account Category Number in GL00100 (what you referred to as the Account Index Master in your script).

Leave a Reply

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