Error Message when attempting to import CSV bank transactions

(0) Share

Report

Posted on by Jshelvey

Hi All:

I have an account that I cannot connect to the Envestnet Yodlee service, and have attempted to define a CSV file import. I created the Data Exchange Definitions, and completed the Bank Export/Import Setup. I also assigned the format to the Bank account.

However when I use the payment reconciliation journal, and create a new journal - when I select the account I want to import into, and run "Import Bank Transactions" I am receiving this error:

Ext. Data Handling Codeunit must have a value in Data Exch. Def: Code=BANKSTMTCSV. It cannot be zero or empty.

0511.error.PNG

However, when I navigate to the settings in Bank Export/Import that column is not empty?

I am not sure if I am looking in the wrong place to correct this but if someone could direct me I'd appreciate it.

Thanks,

John

I have the same question (0)

All responses (13)

Answers (1)

Suggested answer

JAngle 131 on at

Like (0)

Report

Error relates to the data exchange definition itself. Go to the data exchange definition card and add a value to the mentioned field. Codeunit 1240 should do the trick. For CSV the reading/writing xmlport should be 1220

Was this reply helpful? Yes No
Jshelvey 35 on at

Like (0)

Report

Hi Josh, thanks for your response. Updating the card as you explained eliminated the error message. However, my file selection is now not doing anything. When I select my file the screen spins for a moment, and then I am back to the same modal popover asking me to select a file.

Here are the card settings, in case anything jumps out at you. I am planning to import .csv files only.

This is how the file looks, the bank has separate columns for debits/credits

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hello,

Happy New Year. Adding to Josh's reply... If you are using YODLEE, then you should have the YODLEE extension running and your Bank Account 'Bank Statement Import Format' set to YodleeBankFeed (see pic below). Note that this Bank Feed is only supported in the US and Canada and only Banks in these countries are supported. If outside, you could create your own Data Exchange Definition, which I think . If you are not using t

Try checking your Extension Management to see if loaded:

If it is you will find the 2 Data Exchange Definitions in your list:

Here is the link to Yodlee Bank Feed documentation. https://docs.microsoft.com/en-us/dynamics365/business-central/bank-how-setup-bank-statement-service

Now as to your images, I also created a manual import for JP Morgan and my excel file had no header row. Your picture does and you have not defined in the Data Exchange definition. Hear are my Data Exchange pics:

My excel file looked like this:

If you need to see my Data Exchange Definition, email back.

Hope this helps.

Thanks,

Steve

Was this reply helpful? Yes No
Jshelvey 35 on at

Like (0)

Report

Hi Steve, Happy New Year and thanks for the response. The account I need the transactions from is outside of the US and CAN which is why I am setting up the import. I do have the Yodlee extension installed and running, I have our US accounts set up.

I plan to create a new Data Extension from scratch. I tired removing the header line from my file, but experienced the same behavior.

If you are able to share the data exchange definition that would be helpful. I am assuming I have something set not properly that is causing the issue, but unfortunately not throwing an error in BC.

My csv file is a simple one with only 4 columns. Date, Description and then the debit and credit.

Thanks,

John

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

John,

Below is a link to a Data Exchange. I setup your layout and used an Excel template to confirm import (also attaching). Try Import the Data Exchange Definition and use the attached excel to import some test transactions.

Also, make sure you setup the Bank Export/Import Setup for the new Data Exchange Definition. Here is the screen shot for my setup below:

Data Exchange = https://1drv.ms/u/s!AmRbkPn9LCQZnkhjUvcHKDzzMDkC?e=rkYOUo

Excel Template = https://1drv.ms/u/s!AmRbkPn9LCQZnknDCHE_uTex2fqD?e=szOO7t

Hope this resolves evereything.

Steve

Was this reply helpful? Yes No
Jshelvey 35 on at

Like (0)

Report

Hi Steve, sorry late in reply. I was traveling the past few days. I imported your data exchange and am now able to import the data successfully. So this is great.

The only thing I noticed is that BC seems to need a single transaction field. When I export my data from the bank, it lists the debits and credits as separate columns. I can concatenate them into a single column but was wondering if BC could just handle the separated columns.

The highlighted rows below are actually debits that are coming through as zero, which leads me to believe the transaction field in BC is mapped only to the credit column in the excel file.

Here is the excel file:

This is getting off the original topic but if you've any ideas I'd appreciate them. We are deploying BC, previously had Quickbooks.

Thanks,

John

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

John,

I leave my export from the Bank separated into a Debit and Credit Column and the Data Exchange Import resolves this. This is part of the file I sent over. Make sure the Field mapping, on the Detail, is pointing to the fields correctly. They should be. Look at Statement Amount column. In your pic you are showing Transaction Amount column. I think this is the difference.

Thanks,

Steve

Was this reply helpful? Yes No
Jshelvey 35 on at

Like (0)

Report

Hi Steve, I battled with this more today. I did use your Data Exchange and sample import file, but I get this error when I try to run the import.

What I did instead is create a new exchange, and use what you sent as a base. Here are my settings, if you see anything that is off. I was unable to get the two columns to perform and just went with a single transaction column. The file loads fine, and your suggestion on the field mapping solved the issue with the amounts.

The issue I have now is I can't get the Transaction date to read properly. I've tried formatting it but it still comes through as all January transactions.

This is what I get when I import:

This is what the file looks like (I removed the description in the image). The dates seem to be in the correct format, I have no idea why they are being created to January.

Many thanks,

John

Was this reply helpful? Yes No
Suggested answer

JAngle 131 on at

Like (0)

Report

Date definition needs to be MM/dd/yyyy

Note the case is important. Language format is will en-US. Adds this to the definition of the CSV in the bottom form of the overall data exchange definition

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

John,

I saw Josh replied to the error and Data format definition. Did this work?

Steve

Was this reply helpful? Yes No