Small and medium business | Business Central, N...

Import Bank Statement CSV - Question?

(1) Share

Report

Posted on by LanaAbs

Hi BC gurus,

Have any of you come across a bank statement format when both Debit and Credit columns have a value:

Debt	Credit
0	1810000
1185000	0
552.74	0
0	3165.64

When importing such a statement the “0”s in the Credit column overwrite the imported Debit.

I tried to create a Transformation Rule to replace “0” with blank but couldn’t yet find the way for it to work only for “0” amounts.

Have you ever encountered such a thing, any ideas on how to make it work without customization?

Thanks!

Categories:

Dynamics 365 Business Central

All responses (10)

Answers (0)

Suggested answer

Sahib Dino 131 on at

Like (0)

Report

Import Bank Statement CSV - Question?

Yes, this is a common scenario when working with bank statements that contain both Debit and Credit columns, especially in CSV or Excel formats, where zero values can overwrite actual values during import in Business Central.

Issue:

When importing, "0" in Credit column is being read as an actual value and overwriting the Debit amount — or vice versa. You're trying to ignore "0" values only during the import mapping phase, ideally without customization.
Suggested answer

SG-01081133-0 2 on at

Like (0)

Report
Import Bank Statement CSV - Question?

Step 1 Column Definition:

In Column Definitions, only one amount definition is found. To modify the Column Definition to recognize 2 columns for the amounts, add Column 14 for Credits. To save changes, length must be added to each column definition. When Assisted Setup creates definitions, it does not assign a length to all the columns, but saving as a user, length is required.

Mapping 2 columns to one field in BC

Field Mapping needs to be updated to accept an amount from either of the amount columns into the one field in BC.

On Column 13, select Optional (only one field will contain a value on each line). Set the Multiplier to negative one (-1.00) to change the sign of the amount coming in from the bank file (This step is optional otherwise keep it Positive). Select Overwrite Value so that if Column 14 contains the amount, it will overwrite the empty value in Column 13.

Add Column 14, select Field ID 7 (Statement Amount). Select Optional (only one field will contain a value on each line).

Now you can test.
Joe Pirozzolo 22 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

Hi LanaAbs,

Did you ever manage to figure this one out?

I'm having the same issue.

Thanks... joe
LanaAbs 7 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

I am already doing this. But as I mentioned, when Credit Amount is being imported, the "0" there overwrites the imported Debit amount.
Community Member on at

Like (1)

Report

RE: Import Bank Statement CSV - Question?

You do need to combine the 2 columns into 1 column and use the positive and negative amounts.
Suggested answer

darcnus 241 on at

Like (1)

Report

RE: Import Bank Statement CSV - Question?

What about using code to net the two columns into a new field and working from positive and negative figures?
LanaAbs 7 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

No, then it replaces all zeros with blank, i.e. if there's an amount 30000, it will be transformed into 3.
JAngle 105 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

Will a transformation rule to set 0 to blank work?
LanaAbs 7 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

Thank you Josh. Unfortunately the supplied link is not helping.

As I said, the problem is when Debit is xxxxx and Credit is "0" (not blank)

In Mapping I specify both Debit and Credit to go into "STatement Amount" and they are market as optional.

So after Debit Amount is imported, and the turn goes to Credit Amount, the "0" overwrites the imported Debit Amount.
JAngle 105 on at

Like (0)

Report

RE: Import Bank Statement CSV - Question?

This thread has the answer: community.dynamics.com/.../633678