I need help in putting this regular expression into code. (String/Haystack below).
Code: Select all
Proforma 6 831 718 Production To Start
Customer ID 040035 Invoice to CALEFRA SERVICES
Sales person DBN - Jerome Delivery method Customer to Collect
Order number JC14217 RBay Caravan Park
Cage 0
Qty Description Colour Size List Selling Royalty Discount Total
1 Corporate Flag -
Single Sided -
Digital
Sundry 1200 x
900
490,00 490,00 0,00 0,00 490,00
1 Wind Cheater Banners Deluxe - Single N/A
Sided - Digital
3m 556,99 556,99 0,00 0,00 556,99
1 Corporate Flag - Single Sided - Digital N/A Custom 0,00 0,00 0,00 0,00 0,00
R 1,046.99
Total
Discount
Sub Total
R 0.00
R 1,046.99
VAT
Grand Total
R 146.58
R 1,193.57
I find the Customer ID, Sales Person, Proforma Number with the regex above. I would then save these contents into a variable called firstStrings for use with the end step
\d{0,5}\s(?<!R\s)\d{1,4}\,\d{1,4}.+$
I then use the above to search for the below values and would then need to delete these numbers and in then place a one comma in their place
490,00 490,00 0,00 0,00 490,00
0,00 0,00 0,00 0,00 0,00
556,99 556,99 0,00 0,00 556,99
So now the entire string looks like this:
Code: Select all
Proforma 6 831 718 Production To Start
Customer ID 040035 Invoice to CALEFRA SERVICES
Sales person DBN - Jerome Delivery method Customer to Collect
Order number JC14217 RBay Caravan Park
Cage 0
Qty Description Colour Size List Selling Royalty Discount Total
1 Corporate Flag -
Single Sided -
Digital
Sundry 1200 x
900,
1 Wind Cheater Banners Deluxe - Single N/A
Sided - Digital
3m,
1 Corporate Flag - Single Sided - Digital N/A Custom,
R 1,046.99
Total
Discount
Sub Total
R 0.00
R 1,046.99
VAT
Grand Total
R 146.58
R 1,193.57
(?<=number\s).*||(?<=Invoice\sto\s).*|(?<=Proforma\s).*(?=\sProduction)|(?<=person\s).*(?=\sDelivery)
Using this regex I get to now select the line item products only and then keep those and delete everything else in the string
Code: Select all
1 Corporate Flag -
Single Sided -
Digital
Sundry 1200 x
900,
1 Wind Cheater Banners Deluxe - Single N/A
Sided - Digital
3m,
1 Corporate Flag - Single Sided - Digital N/A Custom,
Code: Select all
1 Corporate Flag - Single Sided - Digital Sundry 1200 x 900, Wind Cheater Banners Deluxe - Single N/A Sided - Digital 3m, 1 Corporate Flag - Single Sided - Digital N/A Custom,
- Then find the end of the string with $ and delete those commas
- Lastly add a comma to the beginning numbers of each line ^\d{1,5}\K\s
Code: Select all
1, Corporate Flag - Single Sided - Digital Sundry 1200 x 900
1, Wind Cheater Banners Deluxe - Single N/A Sided - Digital 3m
12, Corporate Flag - Single Sided - Digital N/A Custom
Code: Select all
firstStrings.1, Corporate Flag - Single Sided - Digital Sundry 1200 x 900
firstStrings.1, Wind Cheater Banners Deluxe - Single N/A Sided - Digital 3m
firstStrings.12, Corporate Flag - Single Sided - Digital N/A Custom