Extract columns and add to collection

[mark_rajkumar1 (mar)]

Hi there,

I wan to extract values from deposit column from the table and its total amount which is in the amount column. Meaning to say i wan the total amount for each deposit value and wan to put them in collection. I need a workflow.

[yedukondaluaregala]

@mark_rajkumar1

1. Take One assign activity
   To: lstAmount (dataType list of string)
   Value: New list(Of string)

2. use for each row in datatable

3. use condition to check the deposit is not null

4. then take append item to list activity, provide list variable in List section and in value section pass currentitem(amount).tostring

after completing all iteration you can get the list of all amount with only deposit having data.
with out checking deposit column just you need to get all the amount data you can remove the condition in for each row..

Happy Automation!!

[ashokkarale]

@mark_rajkumar1,

Please share what approaches you tried and what issues you faced so it would be easy to help on it.

[mark_rajkumar1 (mar)]

@ashokkarale i have not done yet because i am not sure how to do it. @yedukondaluaregala will prefer to see a workflow for clearer picture.

[yedukondaluaregala]

Testing2.zip (121.0 KB)

Use this Zip file and change excel with required excel file, if you want all data irrespective deposit column just remove that if activity and place append item to collection activity in for each row..body

[Melroy_Dsa]

Extract_Columns_Add_To_Collection.zip (127.2 KB)
@mark_rajkumar1 ,

I’ve used Linq in the above code, first to get a list of all the Deposit values, and then put them in a for each loop, I added another linq query to all the amount values of that particular deposit value till it encounters a blank cell. After which I stored the Deposit value as a key and The total amount as a value in a dictionary.

Hope this Helps!!

[mark_rajkumar1 (mar)]

Hi @yedukondaluaregala and @Melroy_Dsa possible to show workflow as screenshot. I am unable to extract zip.

[Melroy_Dsa]

@mark_rajkumar1,

This is the Linq I had used in the 1st assign activity

Or

dt_input.AsEnumerable() _
.Where(Function(row) Not String.IsNullOrEmpty(row("deposit").ToString())) _
.Select(Function(row) row("deposit").ToString()) _
.Distinct() _
.ToList()

The Dictionary is of type: String, Object

The Linq of the assign statement inside the for each:

Or

dt_input.AsEnumerable() _
.SkipWhile(Function(row) row("deposit").ToString() <> currentText) _
.TakeWhile(Function(row) Not String.IsNullOrEmpty(row("amount").ToString())) _
.Sum(Function(row) Convert.ToInt32(row("amount")))

The Log Message:

And this is the input I had used:

[yedukondaluaregala]

check this