How can I get a part of text from the .txt file?

Help Studio
, ,
1

Hi all,

I have a file where it can have any number of lines in a text file. I need to pull the “Net sales - taxable 57” from the file, especially “57” count from the file. How can I achieve that?

Q1
Q1292×528 7.11 KB

2

Please try the below expression
add read text file activity and the output of the activity will be the input string for the below expression
system.text.RegularExpressions.Regex.Matches(“inputstring”, “Net sales.+57”).count

3

HI @adoration50

  • Read text file and store the data in the variable “Strtext”
  • Use this expression in the assign activity
System.Text.RegularExpressions.Regex.Match(Strtext,"(?<=Net sales - taxable\n).*").Trim

Can you send the text file here?

Regards
Sudharsan

4

Hi @adoration50

How about this expression

System.Text.RegularExpressions.Regex.Match(YourString,"(?<=Net\s.+sales\s.+\W\staxable\n).*").Tostring.Trim

Regards
Gokul

5

Updated expression @adoration50

System.Text.RegularExpressions.Regex.Match(Strtext,"(?<=Net sales - taxable\n).*").ToString.Trim

image
image1250×298 21.2 KB

6

Quick20221027.txt (4.0 KB)

7

Hi @adoration50

Check out this expression

System.Text.RegularExpressions.Regex.Match(YourString,"(?<=taxable\n).*").ToString.Trim

image

Regards
Gokul

8

Please try the below sequence to get the count.
Expression : system.text.RegularExpressions.Regex.Matches(str_text, “Net sales - taxable.+\n.+57”).count

image
image1383×687 28 KB

Sequence2.xaml (4.5 KB)

9

Hello @adoration50
You can use the Regex Matches to get all data from the text file which has Net sales- taxable.
Kindly refer to this Xaml file, you may get Some ideas.
Forum_RegexMatches.zip (3.0 KB)

System.Text.RegularExpressions.Regex.Matches(InputTxt,"(?<=Net sales - taxable)[\s\d]+")

image
image867×661 18.8 KB

Output
image

10

@adoration50
Also Try this
System.Text.RegularExpressions.Regex.Matches(InputTxt,“(?<=Net sales - taxable)[\s\d]+”)(0).ToString.Trim —> for 1st match

System.Text.RegularExpressions.Regex.Matches(InputTxt,“(?<=Net sales - taxable)[\s\d]+”)(1).ToString.Trim —> for 2nd match
and vice versa

11

TMPOS.xlsx (16.4 KB)
2. How to write to EXCEL (B21:O28) after extracting the value.
3. Finally compare the EXCEL data (B12:O19).
4. The same display Y, different display N

12

Hi @adoration50 ,

Could you let us know if the Excel Template is static or does it differ from time to time ?
Would it be always 3 sections of Tables in it or it varies ? Is the Comparison to be done only with the Previous Table or all of the tables ?
image

A more dynamic solution could be provided if you could let us in on some more information.

13

@adoration50 , to write in excel, just read the data and and as you need to write it to specific column just put the range, it will be done.

Happy Automation

14

Compare only with the table above
123

15

@adoration50 ,

Since it is mentioned as a Fixed Layout/Template, we wouldn’t need much of a dynamic logic to be implemented.

For the data extraction part, we could use the below regex :

Net Sales - Total\r?\n(.*)

image
image761×218 13.7 KB

As the same format applies for all the data as well, we could maybe make this as generic by using the Field names in an Array, thus dynamically extracting all the field values and storing it in the form of a Dictionary.

We’ll then add the values in Dictionary to the Datatable, (Also Adding Data Columns at first).

We could then write this Datatable to the specific Range, i.e starting from Range B21.
The Comparison could be done by using a Formula for decing the Y or N. THe formula is as below :

"=IF(AND(B12=B21,B13=B22,B14=B23,B15=B24,B16=B25,B17=B26,B18=B27,B19=B28),""Y"",""N"")"

We can then use Fill Range Activity with the formula above to compute Y or N for the remaining cells.

Check the workflow below :
Regex_ExtractTableValues_AddToExcel.zip (19.1 KB)

Let us know if there are any issues.

16

How to Fix Methods Not Loading Correctly?

001
001674×504 135 KB

output.txt (3.6 KB)

17

@adoration50 ,

Could you try updating the Packages to the Latest version and Check ?

Also, send the Screenshot of the Dependencies used.