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) -

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 *