One Regex for multiple group values

Help
1

I have following string and want to extract multiple values (all marked as bold/green) using single Regex. Is it even possible?i want to store these values in group and then write them in Excel.

* 26/10/19 AAAA/5555555 Z**ZZZ200000-1** 888/8 555 25252
                 8888888
        D shipments: **AAAA BBBB 25,00**

        D shipments: **CCCC DDDD 20,00**

                 Additional services:
               - HAZARDOUS GOODS SURCHARGE CH **124.00**
               - Subsidy for reduction of farm fees **123,00**
                                                                   **1000,00** 06
                 GTW:00/99999999 **NNNN-UUUU-SSSS-TTTT**
2

([0-9]{2}/[0-9]{2}/[0-9]{2})|(ZZZ[0-9]{6}-[0-9])|([A-Z]{4} [A-Z]{4} [0-9]{2},[0-9]{2})|([0-9]+.[0-9]{2})|([0-9]+,[0-9]{2})|([A-Z]{4}-[A-Z]{4}-[A-Z]{4}-[A-Z]{4})

3

@Robott - can you share an example of what you want the output to look like? I’m not sure I understand. For example, do you want an output to looks like 2610195555555…0099999999 all in a single cell? Or do you want each set of numbers in it’s own cell like 26|10|19|5555555|…|00|99999999 ? Or something completely different?

4

Hi Dave, each in a seprate cell, creating like a row

5

so your passing row wise and you want output from each line using only one regex?

6

do one thing for eachline we want to know what do you want to extract from them!

7

yess

8

the one i sent will put them in groups with one expression, just some chars were lost in the forum format, look at that one:
([0-9]{2}\/[0-9]{2}\/[0-9]{2})|(ZZZ[0-9]{6}-[0-9])|([A-Z]{4} [A-Z]{4} [0-9]{2},[0-9]{2})|([0-9]+[\.,][0-9]{2})|([A-Z]{4}-[A-Z]{4}-[A-Z]{4}-[A-Z]{4})

1 Like
9

Hi Bcrorrea,
Thanks, yes i could save them in groups. How can i add them into csv/excel as row in each cell? any idea?

10

use Add Data Row activity

11

you will use Matches activity and the result will be Match.Groups and their indexes, run it and with debugger you will be able to get all values found…

12

Add Data row.xaml (7.7 KB)

image
image303Ă—558 10.2 KB

Cheers @Robott

13

Hi Pardeep,

it add everything under one column. But i would like is that each value is under different column.

Write now output is like
1
2
3
1
2
3
1
2
3
But it should be
1 2 3
1 2 3
1 2 3

14

actually no, in Matches will have each one with a column in your excel, so you cant use a loop there for every add data row… you need to use the loop to create the Array Row and after you add a row:
Matches(0)=26/10/19
Matches(1)= ZZZ20000-1
…

15

if you are storing like this
regexout1 = 1
regexout2 = 2
regexout3 = 3

then do this in array row {regexout1(0).ToString,regexout2(0).ToString, regexout3(0).ToString}

cheers @Robott

16

so far i have come…
But if u see the xlsx, it is not a proper row… it should will actually 1 row with 3 columns.

Add Data row.xaml (10.2 KB)

17

Can you please explain

18

I don have system now I’ll see this mro

19

I assume this is one record to be parsed

Why not make a regex that will parse all needed elements in one go like this
(?<gr1>[0-9]{2}\/[0-9]{2}\/[0-9]{2}).*?(?<gr2>ZZZZ[0-9]{6}-[0-9]).*?(?<gr3>[A-Z]{4} [A-Z]{4} [0-9]{2},[0-9]{2}).*?(?<gr4>[0-9]+[\.,][0-9]{2}).*?(?<gr5>[A-Z]{4}-[A-Z]{4}-[A-Z]{4}-[A-Z]{4})

Then get each element as named group like this

For Each match
element1 = match.groups(“gr1”)
element2 = match.groups(“gr2”)
datatable.addrow
Next

Cheers

20

It is not just one record. It is above 100

Can you please tell me what variable types to use here? i assume you mean to use assign activity.