CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER PART 1

CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER PART 1

This document shows a simple example on how to use SAP Business One Electronic File Manager to be able to import information for reconciliation contained in file csv comma or semicolon delimited in Bank Statement Processing.

In this example we will use a sample comma ‘,’ delimited, however user must be cautious and use another type of csv delimited, semicolon ‘;’ when handling numeric values that use comma ‘,’ as decimal or thousand separators.

Electonic File Manager:

This add-on utility is able to create formats to import bank statement that can be saved as a format file (.bfp) that can be assigned to our house bank as long as data contained in the file are characters belonging to any of the encodings supported

See in the Electronic File Manager: Format Definition window in the root the Encoding drop down list

Business Case

Information related to transactions to be reconciled is provided in a CSV file comma delimited, this CSV file doesn’t follow any of the existing formats for bank statement processing that SAP Business One includes as pre-installed for the localization.

View in a text editor:

Description of the CSV:

The CSV contains a header with the names of the columns and rows with the data

The following data in the file is identified as relevant for reconciliation

Date: Is the statement row date expressed as DD/MM/YYYY

DebitCredit: has the value D for incoming payment or C for outgoing payment

Amount: Is the amount of the incoming or outgoing payment expressed as Integer.2 decimals

Reference: is a reference used as information to help identify transaction

STEP 1 CREATING THE BASIC STRUCTURE

The first thing to do is open a new Bank statement format and browse the sample csv that we will use as model:

See ‘HOW TO ANALYSE PROBLEMS WITH BANK STATEMENT FILE BFP USING ADDON ELECTRONIC FILE MANAGER (EFM)’ in:

https://service.sap.com/~sapidb/011000358700000869822012E

STEP2: DECIDE THE DEFAULT WAY OF DISTINGUISHING ROWS AND COLUMNS

Because we have a csv comma delimited, the easiest way to tell apart columns (Field Separator) is using comma ‘,’ and use carriage return ‘\r\n’ to separate records (Line Breaks) so we are taking advantage of the original csv structure

STEP 3 OBTAIN A WAY TO IDENTIFY THE PART WITH DATA

In this example we can see in the csv file that:

A) Each column should  match a specific row in the in the statement, being this a csv file columns  in all the rows follow same structure

This can be easily obtained creating standard segment ‘rows’ that will be repetitive.

And deciding the mapping to be linked to statement rows

B) The first row does not contain any data

If we check previous screen, we can see that first segment:  ‘Date, DebitCredit, Amount, Reference’ is considered as a segment to be mapped.

This is not what we want, and is caused by the default selection criteria in tab attributes from the initial default definition of segment separator \r\n in the root.

In this case the most efficient way is to choose selection using ‘Regular Expression’:

See: http://www.regular-expressions.info/

The regular expression I used

(?<=.*\r\n).*

Syntax explanation

(?<= ….)  Look after reading from left to right

.*\r\n   a undefined number set of characters followed by a carriage return

.*  an undefined list of characters

So the first row is excluded because the first row doesn’t have any list of characters before it, followed by a carriage return

In case that you want to exclude any empty lines that might exist in the csv at the bottom you can use the less greedy expression + instead of *

(?<=.*\r\n).+

STEP 4 CREATE THE DIFFERENT FIELDS

Date:

General

Type: Standard field (as oppose to conditional field, that we will see later)

Mandatory: yes, because we need this information and we will not accept files with this column empty

Attributes

Locate by:  Separator

In this case this is the easiest way to locate columns is by separator, as we explained before, because we knowthat there is something in the file used as separator between columns, the comma ‘,’

And as well we will use as Separator the symbol coma ‘,’.

We will indicate in the Index in which position as defined by the separator comma ‘,’ is the field to be located, in this case position is 1 because if we leave it blank it will select all columns.

TIP: You should use instead the semicolon ‘;’ as separator when you expect in your statement figures with ‘,’ as decimal or thousand numeric separator.

(Option 1)

In case that the separator was not just a symbol but something more complex as a pattern we should check the box Regular Expression in Separator, in this example we could set a regular expression   ,.* meaning that the separator between occurrences of same field in segment is the first comma found in the row  ‘,’ followed by the rest of characters in the row.

No need to specify Index in this specific example because the pattern of the regular expression  ,.* cannot happen again in the same segment

(Option 2)

Other options, not practical for this case, would be Locate by Regular expression, used when the only way to identify a particular field in the row is a pattern of text that only by using regular expression is possible to define.

Example regular expression [\d]{2}\[\d]{2}\[\d]{4} that finds text pattern 2 digits + / + 2 digits + / + 4 digits

(Option 3)

Another option would be ‘By position’ which would helpful when the position in the row and length is fixed. In this case we know that Date always starts in the same position 1 and has always the same length 10

(Option 4)

The option Locate by keyword  is not usable because we don’t have a fixed literal string of text that marks the beginning or end of the field

Data Format: Date DD/MM/YYYY

If the data type is left in blank, the data will be read as simple string of text, but in our case we want it to be handled as a date (we want to map this to a Business One Field that is type date)

We use the Format String DD/MM/YYYY so the mapping will be able to identify what is year, month and day

Mapping

We are certain that what we want to do is using this date as ‘statement row date’, so we will map to BankStmtLinedate field in table BankSatementRow

DebitCredit and Amount

We are facing the problem here that the csv has only one column ‘Amount’  that we know is incoming or outgoing only because there is another column in the csv DebitCredit that has value D (Incoming) or C (Outgoing), while in Business One there are 2 target fields.

We need to introduce a logical operator that must map the content of the column ‘Amount’ to one field or the other depending on the value of another column

This is possible by using a logical field for Amount that will map the content to one or other target field in Business One depending on the value D or C of column DebitCredit

Define the field DebitCredit

  It is required to give it a Reference No. otherwise we could not quote its value in operators (we will see this later) I recommend to give always Reference No in case you needed later. It is not necessary but I recommended, using same Name and Reference No to not get confused later

This field does not require mapping as we won’t map the value ‘D’ or ‘C’ to any existing Business One field, it could if needed

The way of finding it is by Separator ‘,’ and index 2 (the second column): As we mentioned this is the easiest way for a csv file

Define the conditional field for Amount

We create a conditional field that will only locate the data in the file

We use the same system of location, this time the index is 3

The data to be mapped is to be considered as a number Numeric and we need to specify how the number must be read: 2 decimals and dot as decimal separator 123456.XX

We see in CSV only figures with 2 decimals but it they had 3 in the CSV they would had been rounded to 2 decimals before being mapped in Business One

Define the results fields IncomingAmount and OutgoingAmount

The conditional field cannot be linked to any target, we need to create from it 2 results fields:

IncomingAmount:

In the Condition tab of this field we set condition that if the value of field DebitCredit is ‘D’ then the value of Amount should be mapped.

The syntax is:

Type1: Reference No This is to indicate that variable for the operation should be taken from the field referred

Arg1-2: DebitCredit This is to indicate the ReferenceNo of the field whose value will be used as variable, in this case the Reference No is DebitCredit

Operator: Equal To

Type2: Constant  This is to indicate that the second variable for the operation is a fixed value

Arg2-2: D as we mentioned before, we know that if the value is D. is incoming payment

The Mapping Settings are to define the target table and field in Business One

In the general tab we do not enter anything except the required name for the field, a convenient Reference No and some information for the developer.

OtgoingAmount:

Similar steps:

Reference

Nothing special here, I provide screen shots just for clarity for attributes and mapping

Because of limits of lenght of the blog, the rest of the explanation will be accesed from another blog:

CREATING A FORMAT FOR A CSV FILE CONTAINING INFORMATION FOR RECONCILIATION USING ELECTRONIC FILE MANAGER

http://scn.sap.com/community/business-one/blog/2012/10/24/creating-a-format-for-a-csv-file-containing-information-for-reconciliation-using-electronic-file-manager-part-2