Import Bank Statement

If you use Bank Statement Processing in SAP Business One (B1) you could note the Import from File button in the Bank Statement Summary:

To make it available you need to set Bank File Project to your house bank account:

This article is about how to create the file so you can define it in this window.

You can define it with SAP Business One Add On Electronic File Manager (EFM), see some information on SAP Help Portal Electronic File Manager: Format Definition Overview.

You can find some bank statement files released in SAP Business Note, e.g., BSP – CAMT053 File Format Support. But let us create the file from scratch.

To create a new format project you need run EFM and in the start page choose Bank Statement File Format Project and use the new designer (the old one is not maintained, 😉):

 Window with 4 parts will open:

1. Displays the file structure like Source -> Target and the highlighted field definition in the bottom of this part.
2. Displays the mapping tree Source data to Target data.
3. Displays the special mapping conditions, formulas, related fields from source.
4. Displays messages like in SAP Business One when you run any wizard.

So, as you see the Target part is Read Only and cannot be edited. This part is a result of mapping that will fill out relevant B1 tables when you press the Import from File button in application. And that is why you cannot edit it.

The Source part – is your bank statement. So, in common with mapping, you need to define what value from bank statement should automatically go to which B1 field of bank statement.

Usually in this window you first define names of the file and statement. In the picture below I also put my name:

With standard function File -> Save As:

you can now save the file. I recommend that you save your project each time you add changes to avoid losing your changes due to potential errors.

To continue you need to know how the Bank Statement  target tree maps to B1 Bank Statement fields.

Here is a picture with mapping for the header Sequence of Bank Statement:

And here the table for the BankStatmentRow lines:

In case you or your business partners pay several documents in one payment then the MultiplePayments section is used to import data for separate document covered by payment. There are only few nodes in this part and they correspond to the fields in the Multiple Payments window:

So, now when the Target is clear let’s look to the Source.

You can use the XML file from your bank or create a new one directly in this window.

First, let’s go with the easiest way – use XML file form your bank:

Then next window pops-up:

The difference between these options is described in Help: If you import a file or a file structure, the file structure is presented as a tree structure on the mapping tab. If you manually create a structure, a blank tree structure is created with only the root element.

Once you imported the XML file you can see that in your EFM project:

Finally, you need to define the mapping between the source and target. You can do that by simple dragging like below:

Or by adding function in the target node:

You can find/define the Reference No. in the source node:

You also can define different conditions/formulas/constant values in the Target nodes. You can find description of different functions in Online Help.

The second option to define your Source tree is to choose TXT. The only difference with XML is that you need define tags for yourselves.

So, now you can import or Assign format project… your Bank Statement project to your SAP Business One. Pay attention to choose correct Format Type:

Once you have updated House Bank Accounts – Setup with Import File Name the button Import from File in the Bank Statement Summary window is available, and you can enjoy working with Bank Statements in SAP Business One, 😊.