Skip to Content
0

Regex in EFM

Apr 13 at 08:52 AM

19

avatar image

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.

efm-preview.png (25.3 kB)
efm-preview2.png (42.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers