Regex in EFM

[Former Member]

Dear all,

I'm writing a custom bank statement format in EFM, using this blog as a guideline. I'm using the 'old' EFM-style, although for functionality, I don't believe this makes a difference.

I'm running into two issues with my file:

1) I cannot seem to format the date in an acceptable format.
2) I can't make EFM to stop reading the row as soon as a match has been found.

My is structured as a .csv and has the following formatting:

"Date","Time","Time Zone","Description","Currency","Gross","Fee","Net","Balance" -- etc.
"1/1/2018","00:11:46","Europe/Berlin","Foo","EUR","1,23","-0,01","1,22","1.000,00" -- etc.
"5/2/2018","02:52:01","Europe/Berlin","Foo","EUR","2,34","-0,02","2,32","1.002,32" -- etc.
"10/3/2018","08:51:34","Europe/Berlin","Foo","EUR","-2,34","0,02","-2,32","1.000,00" -- etc.
"15/3/2018","03:18:41","Europe/Berlin","Foo","EUR","45,67","-1,23","44,44","1.046,76" -- etc.

I can filter the first line, using regex:

(?<=\n).*

This is not a problem.

The date, I assume needs to be cut up in pieces. I can use the following Regex to highlight the first field, without the double quotes, but 1/1/2018 does not seem to be a correct format for EFM, it would have to be 01/01/2018.

(?:(?<=(?:[^"]*"){1})([^"]+)

In the attached screenshot, you can see that this turns line one (1/1/2018) into 18-/2-1/.

efm-preview.png

The second part of the problem is illustrated in the second screenshot. My bankfile only offers one field for positive and negative values. I try to split this, so I can use the fields 'DebitAmountLC' and 'CreditAmountLC', by creating two fields that both look for the 11th doublequote until the next doublequote:

(?<=(?:[^"]*"){11})([^"]*)

And filter for field 'debit': reference '11' (itself) must be bigger than or equal to 0

For field 'credit': reference '11' must be smaller than 0.

The result is that both fields are entered and only with the negative amount, it fills in 0 for debit (I was hoping it'd be blank). For positive (debit) values, it also fills the field CreditAmountLC, thus ignoring the filter.

efm-preview2.png

I expect that this has to do with EFM not stopping on the line, after finding the first match with the regular expression (as indicated by the lines continuing to be coloured after the first match).

Any help on how to get the right amounts in would be highly appreciated.

[Former Member]

Dear Mrs. Kucbelova,

Thank you for replying!

I've tried to reproduce the template in the new designer, but unfortunately, I can't get it to do a proper test run. As far as I can tell, the functionality of the new designer is the same as the old one, but I find the UI of the old version much easier.

Please find below both the 'old' and 'new' style files, as well as the data I'm working with. I'd highly appreciate it, if you could take a look.

[Former Member]

Dear Mr.Hurenkamp,

thank you for providing us the files based on which we were able to prepare a working solution, provided by email.

I hope this will help you to cover the business need.

In case you have any further feedback or question, feel free to contact us.

Kind regards

Eva Kucbelova

SAP Business One Solution Management

[Former Member]

Dear Mr. Hurenkamp,

thank you for raising this issue.

As you are referring to the 'old' EFM designer, may I please ask you whether your issue would persist in case of new designer? In case you cannot test it, would you may be agree on sending us the interim file which you are working on so we could check on our side? It will be probably easier to investigate.

Thank you in advance,

Eva Kucbelova

SAP Business One Solution Management