Update Debtor Addresses From CSV To Work Around An Integration Manager Bug

Earlier this week I posted about a bug in Integration Manager where it added extra zeros into the phone and fax number fields when importing creditors. Well, the same bug also affects debtors but a similar script to update debtors via a CSV is also possible.

The following script is basically the same script I used for creditors with the table and column names changed where necessary;



CREATE TABLE #NewDebtorAddressMaster

   (CUSTNMBR VARCHAR(100)

   ,ADRSCODE VARCHAR(100)

   ,PHONE1 VARCHAR(100)

   ,PHONE2 VARCHAR(100)

   ,PHONE3 VARCHAR(100)

   ,FAX VARCHAR(100))

GO
BULK INSERT

   #NewDebtorAddressMaster

FROM

   'c:\temp\debtors.csv'

WITH

   (FIELDTERMINATOR = ','

   ,ROWTERMINATOR = '\n')

GO
UPDATE

   ['RM Address Master']

SET

   PHONE1 = Left(#NewDebtorAddressMaster.PHONE1, 14)

   ,PHONE2 = Left(#NewDebtorAddressMaster.PHONE2, 14)

   ,PHONE3 = Left(#NewDebtorAddressMaster.PHONE3, 14)

   ,FAX = Left(#NewDebtorAddressMaster.FAX, 14)

FROM

   RM00102 AS ['RM Address Master']

INNER JOIN

   #NewDebtorAddressMaster

      ON #NewDebtorAddressMaster.CUSTNMBR = ['RM Address Master'].CUSTNMBR

         AND #NewDebtorAddressMaster.ADRSCODE = ['RM Address Master'].ADRSCODE
UPDATE

   ['RM Debtor Master']

SET

   PHONE1 = LEFT(['RM Address Master'].PHONE1, 14)

   ,PHONE2 = LEFT(['RM Address Master'].PHONE2, 14)

   ,PHONE3 = LEFT(['RM Address Master'].PHONE3, 14)

   ,FAX = LEFT(['RM Address Master'].FAX, 14)

FROM

   RM00101 AS ['RM Debtor Master']

INNER JOIN

   RM00102 AS ['RM Address Master']

      ON ['RM Address Master'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR

         AND ['RM Address Master'].ADRSCODE = ['RM Debtor Master'].ADRSCODE

DROP TABLE #NewDebtorAddressMaster

If you use this script then, as always when running a script, please make sure you have a good backup of your company database (I define a good backup as one you have restored to ensure it backed up correctly) and know what you are doing in Management Studio with SQL Statements.

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 →