How to extract data in a specific column? Data that falls between two words

Help Studio
, ,
1

How to extract data from a column that is between two lines as in the Image example?
I need to extract to an Array date information that is between the “Data de Vencimento” and “Total Geral”.

image
image227×533 14.1 KB

2

Hi @Marcelo_Melo

You can try this expression to get as string

String.join(",",dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray())

To get as array

dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray()

If you want duplicate you can use False Instead of True

If you dont want duplicates use True

Regards
Sudharsan

3

Hi @Marcelo_Melo

You can add the If Condition for checking if that current row item can be converted to DateTime or not.
So It will perform the action only on DateTime .

4

@Marcelo_Melo
To get the dates between you try like this

System.Text.RegularExpressions.Regex.Matches(String.join(",",dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray()),"(?s)Data de Vencimento,(.*?),Total Geral")

This will give you all the dates that are available in that specific colum which are between “Data de Vencimento” and “Total Geral”.

To use each match you can use for each and can use all the matches like this

image
image709×341 11.2 KB

If you need only one match you can try with this

System.Text.RegularExpressions.Regex.Match(String.join(",",dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray()),"(?s)Data de Vencimento,(.*?),Total Geral").Tostring
5

Hi @Marcelo_Melo

How about the expression?

System.Text.RegularExpressions.Regex.Match(StringInput,"(?s)Sample(.*?)Date").Groups(1).ToString.Trim

Check out the XAML file

GetValueInBetweenDt.xaml (7.2 KB)

Output

image

Regards
Gokul

1 Like
6

Check the updated one


System.Text.RegularExpressions.Regex.Matches(String.join(",",dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray()),"(?<=Data de Vencimento,).*(?=,Total Geral")

Regards
Sudharsan

1 Like
7

use this in the for each @Marcelo_Melo

1 Like
8

Hi!
Thank you for your help.
I got it with the suggestion System.Text.RegularExpressions.Regex.Matches(String.join(“,”,dtDataVeic.DefaultView.ToTable(False, “Column-3”).AsEnumerable().Select(Function (a) a(0 ).ToString).ToArray()),“(?s)Expiration Date,(.*?)Grand Total”)
the return follows below:

Uploading: image.png…

9

image
image719×424 34.2 KB

10

image
Is there a way to not extract the lines with the words “Data Vencimento” and “Total Geral”?
How to use the assign activity to be able to use only the lines extracted individually?

My intention is to find only the rows extracted individually to select a CheckBox that exists for each Row

image
image982×304 37.2 KB

11

DT.Asenumerable.Where(Function(r) Not (r(ColumnName).ToString.Trim=“Data Vencimento” OrElse r(ColumnName).ToString.Trim=“Total Geral”)).Copytodatatable

12

Have you tried with my workflow @Marcelo_Melo , Its working ah?

13

Have you tried this one @Marcelo_Melo ?

14

Yes, I tried and the error is as follows:

image
image698×466 28.5 KB

15

Hi.
Shows this error
image

16

Have you checked this XAML file @Marcelo_Melo

It will as you expected

17

HI @Marcelo_Melo

Use “)” at the end of the pattern

Try with this

System.Text.RegularExpressions.Regex.Matches(String.join(",",dt.DefaultView.ToTable(true, "Your Column Name").AsEnumerable().Select(Function (a) a(0).ToString).ToArray()),"(?<=Data de Vencimento,).*(?=,Total Geral)")

Regards
Sudharsan