Update Microsoft Dynamics GP Account Categories From Text File

I’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 a Dynamics GP Consultant (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
[sqlpinkUPDATE[/sqlpink]
	['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.

Read original post Update Microsoft Dynamics GP Account Categories From Text File at azurecurve|Ramblings of an IT Professional