How to get Amount and do total with from the following text?

[Vidhi_Patel]

11/15/2022 15:20:10 transactionID transaction Date Amount Type
Line up Position Page down Line down
56698975 20-04-2017 00:00:00 -26937 Online
37094339 12-05-2017 00:00:00 224765 Online
21463277 11-04-2017 00:00:00 -245840 Offline
51150782 29-01-2017 00:00:00 173566 Online
97780992 25-03-2017 00:00:00 -276276 Online
87387732 13-01-2017 00:00:00 -126520 Online
70401268 06-09-2017 00:00:00 116441 Online
98768414 18-08-2017 00:00:00 150070 Online
33407473 13-02-2017 00:00:00 192071 Online
95746337 01-04-2017 00:00:00 198716 Online
78511742 23-05-2017 00:00:00 -292681 Online
45980947 30-05-2017 00:00:00 83804 Online
85228152 24-07-2017 00:00:00 75230 Online
88047654 27-06-2017 00:00:00 89967 Offline
67493785 07-06-2017 00:00:00 136473 Offline
25750190 04-03-2017 00:00:00 47488 Online
61880498 25-03-2017 00:00:00 262895 Online
87262253 14-01-2017 00:00:00 103066 Online
78118635 26-04-2017 00:00:00 -124403 Online
77738358 10-01-2017 00:00:00 287511 Online
82858659 17-08-2017 00:00:00 -265325 Online
69980765 24-04-2017 00:00:00 11761 Online
91528635 27-08-2017 00:00:00 -82321 Offline
18908192 12-02-2017 00:00:00 -113450 Offline
57810392 01-06-2017 00:00:00 180636 Online
52831866 29-07-2017 00:00:00 -277526 Online
52503843 02-08-2017 00:00:00 144453 Online
64160749 12-03-2017 00:00:00 30015 Online
37425592 24-05-2017 00:00:00 59621 Online
88153940 20-04-2017 00:00:00 252309 Online
37780506 24-06-2017 00:00:00 47840 Online
65128239 10-09-2017 00:00:00 172057 Offline
23799793 29-08-2017 00:00:00 -280620 Online
54628800 20-01-2017 00:00:00 86182 Online
52932654 27-01-2017 00:00:00 -186498 Offline
85041057 10-03-2017 00:00:00 -122467 Offline
27857903 01-02-2017 00:00:00 -179025 Offline
68816013 20-01-2017 00:00:00 -215378 Online
81130056 13-02-2017 00:00:00 281822 Online
21357694 16-03-2017 00:00:00 -12244 Online
57538278 19-04-2017 00:00:00 247189 Online
11213484 05-07-2017 00:00:00 208188 Offline
34375388 12-08-2017 00:00:00 117275 Online
60817491 28-02-2017 00:00:00 -169018 Online
83779075 18-04-2017 00:00:00 -172136 Online
29729807 06-05-2017 00:00:00 43197 Online
38099243 30-04-2017 00:00:00 255653 Offline
69046738 10-07-2017 00:00:00 -205416 Online
40122795 19-05-2017 00:00:00 -95742 Online
46707781 24-08-2017 00:00:00 -189646 Online

[Gokul001 (Gokul Balaji)]

HI @Vidhi_Patel

You can try with Regex replace method to remove the 2nd line from the input

Check out the expression

System.Text.RegularExpressions.Regex.Replace(YourString,"\n[A-Za-z].*","")

Check out the XAML file

RegexReplace.xaml (7.2 KB)

Regards
Gokul

[Gokul001 (Gokul Balaji)]

HI @Vidhi_Patel

Check Out this Updated XAML file with Sum the Amount

RegexReplace.xaml (8.0 KB)

Regards
Gokul

[Yoichi]

Hi,

FYI, another solution:

System.Text.RegularExpressions.Regex.Matches(yourString,"[\-\d]+(?=\s+\S+\s*$)",System.Text.RegularExpressions.RegexOptions.Multiline).Cast(Of System.Text.RegularExpressions.Match).Sum(Function(m) Decimal.Parse(m.Value))

Regards,

[Vidhi_Patel]

Thank you sir, beginner like don’t understand complex string manipulation but i would like to learn more from you!

[Vidhi_Patel]

Thanks a ton @Gokul001 , but can you help understand ,
(From d in DtInput.AsEnumerable Where Not (isNothing(d(3)) OrElse String.IsNullorEmpty(d(3).toString.Trim)) Select v = CDbl(d(3).toString.Trim)).Sum(Function (x) x)

this line form the code? or is there any short tutiorial or blog for understand it.

[Gokul001 (Gokul Balaji)]

Check out this tutorial @Vidhi_Patel

How To use Arithmetic operator using LINQ

Hello Everyone, Hope you are doing good I would like to present this post who are looking for the expressions related to Arithmetic operator Min | Max | Sum | Average Introduction Aggregate expressions are used to make calculations over a specific set of records. For example, to calculate the total Quantity of all records, or the total number of records in the dataset. Sum operator SUM function can be extremely useful to calculate the sum of the items in a list/Collection LINQ Expression …

[Vidhi_Patel]

Greatful for this tutorial references of tutorial and another suggested post are cool as well thanks for sharing this, Happy automation!