Regular expressions in Format Definition add-on

[Former Member]

Hello experts,

I have a question about regular expressions. I am a newbie in regular expressions and I could use some help on this one. I tried some 6 hours, but I can't get solve it myself.

Summary of my problem:

In SAP Business One (patch level 42) it is possible to use bank statement processing. A file (full of regular expressions) is to be selected, so it can match certain criteria to the bank statement file. The bank statement file consists of a certain pattern (look at the attached code snippet).

:61:071222D208,00N026
:86:P  12345678BELASTINGDIENST       F8R03782497                $GH
$0000009                         BETALINGSKENM. 123456789123456
0 1234567891234560                                             
:61:071225C758,70N078
:86:0116664495 REGULA B.V. HELPMESTRAAT 243 B 5371 AM HARDCITY HARD
CITY 48772-54314                                                   
:61:071225C425,05N078
:86:0329883585 J. MANSSHOT PATTRIOTISLAND 38 1996 PT HELMEN BIJBETA
LING VOOR RELOOP RMP1 SET ORDERNR* 69866 / SPOEDIG LEVEREN     
:61:071225C850,00N078
:86:0105327212 POSE TELEFOONSTRAAT 43 6448 SL S-ROTTERDAM MIJN OR
DERNR. 53846 REF. MAIL 21-02

- I am in search of the right type of regular expression that is used by the Format Definition add-on (javascript, .NET, perl, JAVA, python, etc.)

Besides that I need the regular expressions below, so the Format Definition will match the right lines from my bankfile.

- a regular expression that selects lines starting with :61: and line :86: including next lines (if available), so in fact it has to select everything from :86: till :61: again.

- a regular expression that selects the bank account number (position 5-14) from lines starting with :86:

- a regular expression that selects all other info from lines starting with :86: (and following if any), so all positions that follow after the bank account number

I am looking forward to the right solutions, I can give more info if you need any.

[YatseaLi · Accepted solution]

Hello Hendri,

Q1:I am in search of the right type of regular expression that is used by the Format Definition add-on (javascript, .NET, perl, JAVA, pythonetc.)

Answer: Format Definition uses .Net regular expression.

You may refer the following examples. If necessary, I can send you a guide about how to use regular expression in Format Defnition. Thanks.

Example 6

Description:

To match a field with an optional field in front. For example, u201C:61:0711211121C216,08N051NONREFu201D or u201C:61:071121C216,08N051NONREFu201D, which comprises of a record identification u201C:61:u201D, a date in the form of YYMMDD, anther optional date MMDD, one or two characters to signify the direction of money flow, a numeric amount value and some other information. The target to be matched is the numeric amount value.

Regular expression:

(?<=:61:\d(\d)?[a-zA-Z]{1,2})((\d(,\d*)?)|(,\d))

Text:

:61:0711211121C216,08N051NONREF

Matches:

1

Tips:

1. All the fields in front of the target field are described in the look behind assertion embraced by (?<= and ). Especially, the optional field is embraced by parentheses and then a u201C?u201D (question mark). The sub expression for amount is copied from example 1. You can compose your own regular expression for such cases in the form of (?<=REGEX_FOR_FIELDS_IN_FRONT)(REGEX_FOR_TARGET_FIELD), in which REGEX_FOR_FIELDS_IN_FRONT and REGEX_FOR_TARGET_FIELD are respectively the regular expression for the fields in front and the target field. Keep the parentheses therein.

Example 7

Description:

Find all numbers in the free text description, which are possibly document identifications, e.g. for invoices

Regular expression:

(?<=\b)(?<!\.)\d+(?=\b)(?!\.)

Text:

:86:GIRO 6890316

ENERGETICA NATURA BENELU

AFRIKAWEG 14

HULST

3187-A1176

TRANSACTIEDATUM* 03-07-2007

Matches:

6

Tips:

1. The regular expression given finds all digits between word boundaries except those with a prior dot or following dot; u201C.u201D (dot) is escaped as \.

2. It may find out some inaccurate matches, like the date in text. If you want to exclude u201C-u201D (hyphen) as prior or following character, resemble the case for u201C.u201D (dot), the regular expression becomes (?<=\b)(?<!\.)(?<!-)\d+(?=\b)(?!\.)(?!-). The matches will be:

:86:GIRO 6890316

ENERGETICA NATURA BENELU

AFRIKAWEG 14

HULST

3187-A1176

TRANSACTIEDATUM* 03-07-2007

You may lose some real values like u201C3187u201D before the u201C-u201D.

Example 8

Description:

Find BP account number in 9 digits with a prior u201CPu201D or u201C0u201D in the first position of free text description

Regular expression:

(?<=^(P|0))\d Text: 0000006681 FORTIS ASR BETALINGSCENTRUM BV Matches: 1 Tips: 1. Use positive look behind assertion (?<=PRIOR_KEYWORD) to express the prior keyword. 2. u201C^u201D stands for that match starts from the beginning of the text. If the text includes the record identification, you may include it also in the look behind assertion. For example, :86:0000006681 FORTIS ASR BETALINGSCENTRUM BV The regular expression becomes (?<=:86:(P|0))\d

Example 9

Description:

Following example 8, to find the possible BP name after BP account number, which is composed of letter, dot or space.

Regular expression:

(?<=^(P|0)\d)[a-zA-Z. ]*

Text:

0000006681 FORTIS ASR BETALINGSCENTRUM BV

Matches:

1

Tips:

1. In this case, put BP account number regular expression into the look behind assertion.

Example 10

Description:

Find the possible document identifications in a sub-record of :86: record. Sub-record is like u201C?00u201D, u201C?10u201D etc. A possible document identification sub-record is made up of the following parts:

u2022 keyword u201CREu201D, u201CRGu201D, u201CRu201D, u201CINVu201D, u201CNRu201D, u201CNOu201D, u201CRECHNu201D or u201CRECHNUNGu201D, and

u2022 an optional group made up of following:

 a separator of either a dot, hyphen or slash, and

 an optional space, and

 an optional string starting with keyword u201CNRu201D or u201CNOu201D followed by a separator of either a dot, hyphen or slash, and

 an optional space

u2022 and finally document identification in digits

Regular expression:

(?<=\?\d(RE|RG|R|INV|NR|NO|RECHN|RECHNUNG)((\.|-|/)\s?((NR|NO)(\.|-|/))?\s?)?)\d+

Kind Regards

-Yatsea

[Former Member · Accepted solution]

Yatsea,

Thank you very much for your elaborate answer. It gives me a good view of making Format Definition work. I think I still have to practise a lot, so I would be grateful if you could send me the guide about how to use regular expression in Format Definition.

I took a close look at Format Definition and it is built in some tree structure. If a certain rule matches a whole line, then will the regex underneath it only make a selection in that same row? Maybe I can make it more clear by a picture:

[Link to picture|http://img233.imageshack.us/my.php?image=treestructurefw3.jpg]

or

<a href="http://img233.imageshack.us/my.php?image=treestructurefw3.jpg">[img=http://img233.imageshack.us/img233/2729/treestructurefw3.th.jpg]</a><a href="http://g.imageshack.us/thpix.php">[img=http://img233.imageshack.us/images/thpix.gif]</a>

I hope you can answer this question as well and I am looking forward to receiving the guide (is it possible to send it like this or do you need my emailadress?)

Thanks a lot,

Kind regards,

Hendri

[YatseaLi · Accepted solution]

Hello Hendri,

In the "General" Tab, there is a atribute "Repetitive", if yes, it select all lines match the criteria. You may refer to the online help of Format Definition for more information.

Sure, I need you mailaddress. You can find mine in my business card. Thanks.

Kind Regards

-Yatsea

[Former Member · Accepted solution]

I need a certain regular expression which I can't figure out myself (I don't even know if it is possible to make it)

I want to make a regex that selects in a certain row and on a certain condition.

It should select the details of the bank statement row:

In the line beneath it should select everything after the 14th character:

:86:P116664495 REGULA B.V. HELPMESTRAAT 243 B 5371 AM HARDCITY HARD
CITY 48772-54314

In the line beneath it should select everything after the 4th

:86:AUTOMATISCHE BETALING ABONNEMENT 43 6448 SL S-ROTTERDAM MIJN OR
DERNR. 53846 REF. MAIL 21-02

I tried different possible solutions myself already but I couldn't find the right regex.

I hope someone has a solution.

Thanks in advance.

[YatseaLi · Accepted solution]

Hello Hendri,

What is the rule?

In what case, it should select everything after the 14th character:

In what case, it should select everything after the 14th character:

Kind Regards

-Yatsea

[Former Member · Accepted solution]

Yatsea,

Thanks for your reply. I see I didn't explain my question quite well. It was quite late yesterday night... Here is another try:

In my bankfile the following pattern is to be found some opening- and closinglines and futhermore lines :61: and :86:

But on line 86 there are two possibilities:

1. after :86: a bankaccount nummer is being displayed, see the next example (P116664495 is account number):

:86:P116664495 REGULA B.V. HELPMESTRAAT 243 B 5371 AM HARDCITY HARD
CITY 48772-54314

2. immediately after :86: a description of the banktransaction is displayed, see the next example:

:86:AUTOMATISCHE BETALING ABONNEMENT 43 6448 SL S-ROTTERDAM MIJN OR
DERNR. 53846 REF. MAIL 21-02

Now I want a regex that looks at line 86 and is able to see whether a bankaccount nummer is available. If so: skip it (do not display) and if not start matching immediately after :86: till :61: occurs.

I hope this is a bit more clear to you (or someone else). I don't have a clue if it is possible so I am curious about possible solutions.

[YatseaLi · Accepted solution]

Hello Hendri,

Thanks for the clarification.Let me specify the qeustion in more detail.

1.after :86: a bankaccount nummer is being displayed, see the next example (P116664495 is account number):

:86:P116664495 REGULA B.V. HELPMESTRAAT 243 B 5371 AM HARDCITY HARD
CITY 48772-54314

What is the structure rule of bank account?

Are all the bank account start with P then following 9 digits? e.g. P116664495

2. immediately after :86: a description of the banktransaction is displayed, see the next example:

:86:AUTOMATISCHE BETALING ABONNEMENT 43 6448 SL S-ROTTERDAM MIJN OR
DERNR. 53846 REF. MAIL 21-02

How do I know it is a description of the bank transation?

Are them some fixed description?

Kind Regards

-Yattsea

[Former Member · Accepted solution]

1.after :86: a bankaccount nummer is being displayed, see the next example (P116664495 is account number):

:86:P116664495 REGULA B.V. HELPMESTRAAT 243 B 5371 AM HARDCITY HARD
CITY 48772-54314

What is the structure rule of bank account?

Are all the bank account start with P then following 9 digits? e.g. P116664495

No not all of them: bank accounts from 'Postbank' do start with a P, the other accounts all start with a 0 (null)

That are the only 2 possible starts of a bankaccount (at least from the Netherlands and that is 95% of all cases)

2. immediately after :86: a description of the banktransaction is displayed, see the next example:

:86:AUTOMATISCHE BETALING ABONNEMENT 43 6448 SL S-ROTTERDAM MIJN OR
DERNR. 53846 REF. MAIL 21-02

How do I know it is a description of the bank transation?

Are them some fixed description?

No there is no use of fixed descriptions, it should select everything after :86: that does not start with P or 0, customers just fill the description in there own way, so no standardisation.

[YatseaLi · Accepted solution]

Hello Hendri,

Definitely possible.

Step 1: You need add a SegmentGroup under Transation, the set the segmentgroup as "Sub-Notes are alternatives" in "Attributes" Tab

Step 2: Insert 2 standard segment under the segamentgroup about, one is to match ":86:P0", the other is to match ":86:AUTO".

-Match the whole line ":86:P0" with Regrex

:86:(P|0)\d{9}.*\n(.*\n)?

-Match the whole line ":86:AUTO". with Regrex

:86:[^P0].*\n(.*\n)?

Step 3: Insert a standard field for BankAccount for case 1.

Get the bank acouunt with Regrex:

(?<=:86:)(P|0)\d{9}

Step 4: Insert a standard field for Transaction Decription for case 2

String start with position and length.

Therefore Format Defintion AddOn is very powerful, you just need to understand the bank statement structure well and regrex well. it is done.

I will m-ail you the BFP and test sample.

Kind Regards

-Yatsea

[Former Member · Accepted solution]

I am looking forward to your example! I do understand the regrex statements better now, but the trick is to translate them in SAP logics