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

Microsoft Dynamics GPEarlier 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.

What should we write about next?

Your Name (required) -
Your Email (required) -

(Visited 159 times, 1 visits today)

2 thoughts on “Update Debtor Addresses From CSV To Work Around An Integration Manager Bug

Leave a Reply

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