How To Bulk Update Segment Descriptions From A CSV

On the Microsoft Dynamics GP Community site a question from Lisa Sorenson in October last year asking if it was possible to use the Table Import (Microsoft Dynamics GP menu >> Tools >> Integrate >> Table Import) feature in Microsoft Dynamics GP to update some Segment descriptions has risen to the top with Steve Cummings linking to a post where the suggestion is to use a CSV file and Word template to generate a mailmerge.

This solution will work, but can be accomplished in much less time and effort by using the SQL command BULK INSERT to load the CSV (formatted as Segment ID, Segment Number and Description), in the below SQL change the highlighted path to the location of your CSV file, into a temporary table and update Account Segment Master (GL40200) from the temp table (which is removed at the end of the script);



CREATE TABLE #SegmentDescriptions

   (SGMTNUMB VARCHAR(100)

   ,SGMNTID VARCHAR(100)

   ,DSCRIPTN VARCHAR(100))

GO
BULK INSERT

   #SegmentDescriptions

FROM

   'c:\temp\Segments\Segments.csv'

WITH

   (FIELDTERMINATOR = ','

   ,ROWTERMINATOR = '\n')

GO
UPDATE

   Segments

SET

   Segments.SGMTNUMB = NewSegments.SGMTNUMB

   ,Segments.SGMNTID = NewSegments.SGMNTID

   ,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31)

FROM

   GL40200 AS Segments

INNER JOIN

   #SegmentDescriptions AS NewSegments ON NewSegments.SGMTNUMB = Segments.SGMTNUMB

      AND NewSegments.SGMNTID = Segments.SGMNTID

DROP TABLE #SegmentDescriptions

The above script is supplied as is with no warranty; if you use the script please ensure you have a backup of your company database before starting.

About Ian Grieve

Ian is a Microsoft Dynamics GP certified consultant specialising in the delivery of Microsoft Dynamics GP projects and currently working for Perfect Image Ltd., a Microsoft Partner and VAR in the North East of England. Ian has worked with Microsoft Dynamics GP since 2003 and, over the nine years since, has dealt with all aspects of the product life-cycle from presales, to implementation, to technical and functional training, to post go-live support and subsequent upgrades and process reviews. In his spare time, Ian runs the azurecurve | Ramblings of a Dynamics GP Consultant blog dedicated to Microsoft Dynamics GP and related products.

View all posts by Ian Grieve →

4 comments on “How To Bulk Update Segment Descriptions From A CSV”

Pingback: Interesting Findings & Knowledge Sharing » How To Bulk Update Segment Descriptions From A CSV

Lisa Sorensen on 19th January 2012 at 00:23 said:

Thanks so much for the script. It seems pretty straight forward but when I attempted to run it at a client site, I received the following message. It is during the process of running the update statement and setting the valies.

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Any idea what could be causing this and how to correct it? Thanks again for the information!

Reply ↓

Ian Grieve on 19th January 2012 at 07:03 said:

Hi Lisa,

I’ve just done some testing and it appears the error is caused by one (or more) of the descriptions being longer than the 31 characters allowed by the Segment Description Master table.

I have updated the script above to only use the first 31 characters of the description from the CSV to prevent the error.

Ian

Reply ↓

Pingback: Update Inventory Item Accounts From CSV | azurecurve