SQL Script To Insert Creditor Bank Details From CSV

Microsoft Dynamics GPIntegration Manager is a very good tool, but it doesn’t allow the import of all record types into Microsoft Dynamics GP. One of the main types of record I need to import when implementing a new client is the Creditor (Vendor to the American readers) EFT details.

While the EFT data is stored in one table and can therefore be imported using Table Import, I find that configuring Table Import for each client is a bit of a annoyance, so I developed a SQL Script a while ago which I figured I might as well post here for easy access.

As always when using SQL to update tables in Microsoft Dynamics GP, make sure you have a good backup of the database before you begin and check the imported data afterwards. Read on for the script…

CREATE TABLE #AddressElectronicFundsTransferMaster
	(VENDORID VARCHAR(15)
	,ADRSCODE VARCHAR(15)
	,EFTBankCode VARCHAR(6)
	,EFTBankAcct VARCHAR(8))
GO

BULK INSERT
	#AddressElectronicFundsTransferMaster
FROM
	'R:\DynamicsCentral\IM\CreditorBankDetails.csv'
WITH
	(FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n')
GO

INSERT INTO SY06000
	(AddressEFTMaster.SERIES
	,AddressEFTMaster.CustomerVendor_ID 
	,AddressEFTMaster.ADRSCODE
	,AddressEFTMaster.VENDORID
	,AddressEFTMaster.CUSTNMBR
	,AddressEFTMaster.EFTUseMasterID
	,AddressEFTMaster.EFTBankType
	,AddressEFTMaster.FRGNBANK
	,AddressEFTMaster.INACTIVE
	,AddressEFTMaster.BANKNAME
	,AddressEFTMaster.EFTBankAcct
	,AddressEFTMaster.EFTBankBranch
	,AddressEFTMaster.GIROPostType
	,AddressEFTMaster.EFTBankCode
	,AddressEFTMaster.EFTBankBranchCode
	,AddressEFTMaster.EFTBankCheckDigit
	,AddressEFTMaster.BSROLLNO
	,AddressEFTMaster.IntlBankAcctNum
	,AddressEFTMaster.SWIFTADDR
	,AddressEFTMaster.CustVendCountryCode
	,AddressEFTMaster.DeliveryCountryCode
	,AddressEFTMaster.BNKCTRCD
	,AddressEFTMaster.CBANKCD
	,AddressEFTMaster.ADDRESS1
	,AddressEFTMaster.ADDRESS2
	,AddressEFTMaster.ADDRESS3
	,AddressEFTMaster.ADDRESS4
	,AddressEFTMaster.RegCode1
	,AddressEFTMaster.RegCode2
	,AddressEFTMaster.BankInfo7
	,AddressEFTMaster.EFTTransitRoutingNo
	,AddressEFTMaster.CURNCYID
	,AddressEFTMaster.EFTTransferMethod
	,AddressEFTMaster.EFTAccountType
	,AddressEFTMaster.EFTPrenoteDate
	,AddressEFTMaster.EFTTerminationDate)

	(SELECT
		4
		,VENDORID
		,ADRSCODE
		,VENDORID
		,''
		,1
		,3
		,0
		,0
		,' '
		,EFTBankAcct
		,''
		,0
		,EFTBankCode
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,0
		,''
		,''
		,1
		,1
		,'1900-01-01 00:00:00.000'
		,'1900-01-01 00:00:00.000'
	FROM
		#AddressElectronicFundsTransferMaster WHERE LEN(EFTBankAcct) > 0)
GO

DROP TABLE #AddressElectronicFundsTransferMaster
GO

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

Leave a Reply

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