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

Import Bank Statement CSV - Question?

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

I have the same question (0)

All responses (12)

Answers (0)

JAngle 133 on at

Like (1)

Report

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

Was this reply helpful? Yes No
LanaAbs 17 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
JAngle 133 on at

Like (1)

Report

Will a transformation rule to set 0 to blank work?

Was this reply helpful? Yes No
LanaAbs 17 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Suggested answer

darcnus 247 on at

Like (2)

Report

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

Was this reply helpful? Yes No
Community Member on at

Like (2)

Report

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

Was this reply helpful? Yes No
LanaAbs 17 on at

Like (1)

Report

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

Was this reply helpful? Yes No
Joe Pirozzolo 24 on at

Like (1)

Report

Hi LanaAbs,

Did you ever manage to figure this one out?

I'm having the same issue.

Thanks... joe

Was this reply helpful? Yes No
Suggested answer

SG-01081133-0 12 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
Suggested answer

Sahib Dino 196 on at

Like (1)

Report

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.

Was this reply helpful? Yes No