Question

Pattern Matching Help??

Quick Fields

Updated September 2, 2014

asked on August 29, 2014

So I'm trying to capture data from a Bank Statement. The information I'm trying to capture is the Amount, but I ONLY want the Amounts that start with a Transaction Date, have the word Credit Amount: and end with the amount before I move onto the next one. (see screenshot)

This is what my ZONE Field is capturing:

Information 1 Amounts : Treasury Information Report Page 1 of 34
08/01/2014 08:19 AM ET BARNES & NOBLE BOOKS
CUSTOMER ID: BARNE920 Previous Day Composite Report
OPERATOR ID: PLEASANT As of 07/31/2014
Commercial Electronic Officeo" Treasury Information Reporting
Currency:USD
Bank: 121000248 WELLS FARGO BANK, N.A.
Account: 4121612618(NJ) BARNES & NOBLE COLLEGE BOOKSELLERS
Balances
Closing Ledger Balance .00
Closing Collected Balance .00
Opening Available Balance .00
One Day Float .00
Two+ Day Float .00
MTD Average Closing Ledger Balance .00
MTD Average Closing Collected Balance .00
Total Credits 153,370.21
Total Debits 153,370.21
Total Number Credits 282
Total Number Debits 1
Summaries
Type of Credit _.. __.........___._. _ . Number of Items __....._.. __. _ . ... Amount
Total ACH Credits 279 58,498.35
Total Deposits 1 8,217.00
Total Lockbox Credits 1 84,091.44
Total Wire Transfer Credits 1 2,563.42
Credit Totals 282 153,370.21
Type of Debit Number of Items ...............................................................................................................Amount
......................................................................................._....................................._...._............................................................................................ ........................................
Total ZBA Debits 1 153,370.21
Debit Totals 1 153,370.21
Credit Transactions
7/31/2014 169 / MISCELLANEOUS ACH CREDIT �� Credit Amount: 16,255.62
Cust Ref: 00000000000 '��b�b ` Bank Ref: IA000095482301
MSMU AP ACH AP ACH DBT 6283 BARNES & NOBLE COLLEGE
7/31/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 5,258.85
Cust Ref: 00000000000 0*1 Bank Ref: IA104000019725
SAM HOUSTON STAT INVOICE 140730 XXXXX1257 06/01/2014*370278
*1013605
7/31/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 1,786.70
Cust Ref: 00000000000 ( Bank Ref: IA408598111193
SDBOR PROD INSTA INVOICE 140730 A00021808 05/01/2014*364694 *1109548
7/31/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 1,536.45
Cust Ref: 00000000000 V-\W Bank Ref: IA001036786023
NYS OSC ACH AP0000939419 RMR*IV*Combined for 5 Documents\
7/31/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 1,422.75
Cust Ref: 00000000000 Bank Ref: IA500636248382
ST. OF MISSOURI VENDOR PAY E00007291400123 F*VV*500E0660676222
\DTM*003 *20140725\SE*47* 00001\
https://wellssuite.wellsfargo. com/tir/tirservice?KEY=2065 57&actionType=PleaseWait&cha... 8/1/2014 Amount 0

I can do basic Pattern Matching, but this seems fairly sophisticated and I'm not exactly sure where to start so was looking for some feedback as to IF this is feasible and HOW do I accomplish this.

Thanks!

0 0

Answer

SELECTED ANSWER

replied on September 2, 2014 • Show version history

Ok... the pattern is a bit gnarly, but when I run it against your test output it pulls back the dollar values like a champ. Ignore the line break; it's a long pattern, and Answers wants to put it on two lines.

[\d\w/ ]+ACH\s+CREDIT\s+Credit\s+Amount:\s+([\d.,]+)|[\d\w/ ]+TRANSFER\s+CREDIT\s+Credit\s+Amount:\s+([\d.,]+)

A couple of things about it:

Note that it uses \s+ instead of spaces as often as possible. This is because Quick Fields adds an extra space here or there. The \s character class includes new lines (in Workflow, and sometimes in Quick Fields); this shouldn't be a problem here, but it's something to be aware of.
This pattern relies on "ACH CREDIT Credit Amount:" and "TRANSFER CREDIT Credit Amount:" being spelled correctly. If they're misinterpreted, the pattern won't return anything.
This pattern returns just the dollar value at the end. If you need both the dollar value and the date, you can tweak the [\d\w/ ] at the beginning of each portion to look for a date and wrap it in parenthesis.

Remember, your mileage may vary! Make sure to test it plenty.

1 0

Replies

replied on September 2, 2014

Unless there are credit amounts you do not wish to capture, I think the line I posted last week will work for you... it pretty much captures any credit amount, so long as the line of text begins with a date.

1 0

replied on August 29, 2014

Hi there,

I don't use Quick Fields, but would something like this work?

\d{1,2}/\d{1,2}/\d{4}.{1,250}Credit Amount:\s{0,20}(.[0-9,]{1,7}\.\d{2})

... it seems to capture the credit amount in Workflow.

0 0

replied on August 29, 2014

I actually ended up using this...not sure it is the most efficient.

\d+/\d\d/\d+\s+\d+\s\W\s\w+\sACH CREDIT\s+?Credit Amount:?.?\s+(\d*\S?\d+.\d\d)

0 0

replied on September 2, 2014

Ok..so I've had another curve thrown at me. Now I need to be able to do the above (ACH CREDIT OR TRANSFER CREDIT). Below is what is being captured.

Information 1 Amounts : Treasury Information Report Page 13 of 14
Cust Ref: 00000000000 Bank Ref: IA036151891026
36 TREAS 310 MISC PAY 082114 XXXXX0012 RMT*IV*376784
\REF* VV* WV32742310024*(502)582-5854
8/21/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 0.31
Cust Ref: 00000000000 Bank Ref: IA036151891037
36 TREAS 310 MISC PAY 082114 XXXXX0012 RMT*IV*RAYBURNJINV368235
\REF*VV* WV34342310038*(51
MISCELLANEOUS ACH CREDIT Total Credit Amount 231,441.28
8/21/2014 115 / LOCKBOX DEPOSIT Credit Amount: 136,030.19
Cust Ref: 00000823660 Bank Ref: IA000861245871
Float-Zero Day: 56.00 One Day: 135,974.00 Two+ Day: .00
WHOLESALE LOCKBOX DEPOSIT PHILADELPHIA BOX 823660 DEPOSIT 1
8/21/2014 206 / BOOK TRANSFER CREDIT Credit Amount: 44,342.90
Cust Ref: 00000000000 Bank Ref: IA009985406434
WT SEQ#61176 UNIVERSITY OF PENNSYLVA /ORG=UNIVERSITY OF
PENNSYLVANIA SRF# IN14082108553486
TRN#140821061176 RFB# 000006826
Credit Total Credit Amount 411,814.37
Float-Zero Day: 275,767.00 One Day: 135,974.00 Two+ Day: .00
Debit Transactions
8/21/2014 575 / INDIVIDUAL ZBA DEBIT Debit Amount: 411,814.37
Cust Ref: 00000000000 Bank Ref: IA082100000025
ZERO BALANCE ACCOUNT TRANSFER TO 4121396907
Account Net Amount 0.00
Currency:USD
Bank: 121000248 WELLS FARGO BANK, N.A.
Account: 4945348225(NJ) NEW MEXICO STATE UNIVERSITY EB
Balances
Closing Ledger Balance 11,103.02
Closing Collected Balance 11,103.02
Opening Available Balance 11,103.02
One Day Float .00
Two+ Day Float .00
MTD Average Closing Ledger Balance 10,564.48
MTD Average Closing Collected Balance 10,564.48
Total Credits 34.25
Total Debits .00
Total Number Credits 1
Total Number Debits 0
Summaries
Type of Credit Number of Items Amount
Total ACH Credits 1 34.25
Credit Totals 1 34.25
Credit Transactions
_._ ........ ... .. ............ ..
8/21/2014 169 / MISCELLANEOUS ACH CREDIT Credit Amount: 34.25
https: //wellssuite. wellsfargo. com/tir/tirservice?KEY=87627&actionType=PleaseWait&cha... 8/25/2014 Amount 0

The highlighted section are what I need to capture. I can get the ACH part using the following:

\d+/\d\d/\d+\s+\d+\s\W\s\w+\sACH CREDIT\s+?Credit Amount:?.?\s+(\d*\S?\d+.\d\d)

I tried adding |TRANSFER after ACH, but the it only finds the TRANSFER not amount...I need it to find both if they're both there, or each individually should both not be there.

0 0

SELECTED ANSWER

replied on September 2, 2014 • Show version history

[\d\w/ ]+ACH\s+CREDIT\s+Credit\s+Amount:\s+([\d.,]+)|[\d\w/ ]+TRANSFER\s+CREDIT\s+Credit\s+Amount:\s+([\d.,]+)

A couple of things about it:

Note that it uses \s+ instead of spaces as often as possible. This is because Quick Fields adds an extra space here or there. The \s character class includes new lines (in Workflow, and sometimes in Quick Fields); this shouldn't be a problem here, but it's something to be aware of.
This pattern relies on "ACH CREDIT Credit Amount:" and "TRANSFER CREDIT Credit Amount:" being spelled correctly. If they're misinterpreted, the pattern won't return anything.
This pattern returns just the dollar value at the end. If you need both the dollar value and the date, you can tweak the [\d\w/ ] at the beginning of each portion to look for a date and wrap it in parenthesis.

Remember, your mileage may vary! Make sure to test it plenty.

1 0

You are not allowed to follow up in this post.

Question

Question

Pattern Matching Help??

Answer

Replies

Sign in to reply to this post.