Can't Find Regex Pattern To Pull Specific Column

user_5768697 0
Viewed 81

Looking for help on correct Regex Code to pull Invoice #, Date, Description, Gross Disc, & Net in separate groups.

Having difficulty finding pattern on the Description Column.

Sample of how far I got with my code is below also:

[0-9A-Z]+\s(\d{4}-\d{2}-\d{2})+\s([A-Z0-9]+\s)

Sample Of Text String:

2020-06-11 Sample Vendor Page 1
A/P Remittance Advice
Direct Deposit 2020-06-11

Cheque # 10353 Vendor # 0828 Sample Company, ULC

Invoice # Date Description Gross Disc Net
===================================================================================
222036 2020-05-06 P2153664 11,237.62 .00 11,237.62
222054 2020-05-06 P2153894 6,582.17 .00 6,582.17
222160 2020-05-01 P2152759 50,895.56 .00 50,895.56
222166 2020-05-01 P2153663 28,046.09 .00 28,046.09
222249 2020-05-05 P2154421 6,030.03 .00 6,030.03
222417 2020-05-05 P2153893 17,676.57 .00 17,676.57
CR01BCFF 2020-06-08 MULTI MCBS 6,872.59- .00 6,872.59-
DM2005R105 2020-06-02 MAY 20 RTN,EXP,N/CH 79.60- .00 79.60-
DM2005R450 2020-06-02 MAY 20 RTN,EXP,N/CH 20.70- .00 20.70-
DM20065533 2020-06-05 VPFP PROGRAM FEES 556.93- .00 556.93-
==========================================
112,938.22 .00 112,938.22

Printed on 2020-06-11 at 13:35
regex
2 Answers

Since you know the patterns for the first two and last three columns, you may use

^\w+\s(\d{4}-\d{2}-\d{2})\s(.*?)(\S+)\s(\S+)\s(\S+)$

Or, if you have to run it on a multiline block of text:

(?m)^\w+\s(\d{4}-\d{2}-\d{2})\s(.*?)(\S+)\s(\S+)\s(\S+)\r?$

See the .NET regex demo

Matches look like

enter image description here

Details

  • ^ - start of string
  • \w+ - one or more word chars
  • \s - a whitespace
  • (\d{4}-\d{2}-\d{2}) - four digits, -, two digits, -, two digits
  • \s - a whitespace
  • (.*?) - any amount of 0 or more chars as few as possible
  • (\S+) - any 1+ non-whitespace
  • \s - a whitespace
  • (\S+) - any 1+ non-whitespace
  • \s - a whitespace
  • (\S+) - any 1+ non-whitespace
  • $ - end of string/line.
user_38329700

Looking at the fact that the description is the only field that can contain spaces, you can match as much as possible space separated strings as long as there is 3 columns left.

Explanation:

  • \S matches any non whitespace character.
  • ^ matches beginning of string
  • . matches any character.
  • + match as much as possible.

here is a demo Notice that used the multiline flag. so that each line is treated as a separate string.

(?m)^([A-Z\d]+) (\S+) (.+) (\S+) (\S+) (\S+)
user_39808200
Related