How to read each 20 rows with condition and create new sheet

Hi guys,
I am having trouble with more then 800+ records in excel, how to base on a condition to get 20 rows in new sheets.
E.g workflow, open excel sheet read through excel, get a variable from a col lets say “1” in first row, if next row same col contain 1 i add into collection or save into data row, then if i save up to 20 records i create a new sheet with the variable name, if the records matching is not right, create new sheets with added collections, replace row.item to new variable keep looping.
My current workflow is open application> assign new list = New List(Of System.Data.DataRow)> for each records if col = “1” add to collection and assign the row.item = “1”, when next row contains “1” add into collection, if records stored up to 20 create new sheets and keep looping, if next row is not “1” write new sheet and assign row.item = new variable to loop through the excel.
but i have a problem with current workflow about the collection, the collection will always print all the existing records to another new sheets, can’t really get 20 records. can anyone help me out.
Thank you,
Aaron

Hy @Aaron_Ko,

You could try someting like this:

Inside an ‘Excel Application Scope’ use the ‘read range’ activity
Use for ‘each row’ activity to loop through your data.
Add this rows to another data table
Use a counter, when it reaches to 20 use the break to exit the loop.

Is it clear to you?

Regards

yes this is what i have in my current workflow

Hy @Aaron_Ko,

First of all I suggest you to re-organize your workflow. Inside the ‘For Each Row’ activity use a flowchart instead of an Sequence. Use flow decisions instead of multiple ifs one inside of another. It is difficult to read and understand what you want to do.

Can you share your entire project file so I can have a look at it?

Regards

Do you mean things like you see in here?

1 Like

Hi william thanks the following attachment is my current workflow example… i don’t know why the add collection only print all existing data…
example with 20.zip (44.2 KB)
thanks you

Yes something like this, if i use C# do i need to recreate a new process base on C# and use invoke method?

@Aaron_Ko

also have a look here (we did without invoke code etc)

1 Like

no, you dont need to recreate anything nor use c#, as the methods are the same for vb.net… just use Assign activities.

1 Like

Hy @Aaron_Ko,

Please try this expression to get the top 20 records from your data table, use an assign activity.

new_DataTable=old_DataTable.AsEnumerable().Take(N).CopyToDataTable()

If it sucesseds please like my post and mark my answer as solution.
Any questions please let me know

Thanks

1 Like

thanks for all the reply i need to study it and try it out thank you guys.

i tried your method i got a error of AsEnumerable is not a member of datatable.

@Aaron_Ko
have a look here:

1 Like

thank you this works for the row convert to Data table.
i am still struggling with the data
my current workflow have a do while, inside of the do while loop i have a for each loop with 3 if else, first condition ForEachLoopOutPut=1 then add to collection, second condition when it is 20 records write and break clear collection ,third condition next variable is not matching, write new sheet and break,then clear collection.
Outside of for each loop i have assign my datatable to DT.AsEnumerable().Skip(foreachloopoutput).CopyToDatatable() and keep looping.
But when it reach to the end of the row, the remaining collection are not able to write into new sheet, and i don’t know how should i write the do while condition to break it out of the loop… could you please help me out .

Hy @Aaron_Ko,

Please check my workflow, no complicated code, just some good old logic.

DataTable_Select_Top_20_Test.zip (30.7 KB)

It is ok to you please mark my answer as a solution, it helps me
Any questions please let me know

Thanks

Thank you william for you example you get 20 rows and write to new sheets, but what if records have 21, how to apply remaining records and write new sheets? thanks

Aaron

Hy @Aaron_Ko, you must use the good old same logic, using the counter to extract data, something like this:

You can wrap this workflow, to return a DataTable as Out Orgument, and as input, the first and last position you want to extract.

Is it clear for you?
image

Regards

The easiest way i can think of is to first create the batches of new datatables that will hold your batch size or less if not enough for the last one and after writing all to new excel files like this: Main.xaml (31.8 KB)

1 Like

thanks for sharing your idea, but i am not able to open your main file…

Thank you,
yes i have something really similar with yours but i am using add to collection instead of add data row activity to collect my datarow from excel, i assigned DT=DT.AsEnumerable().Skip(foreachloopoutput).CopyToDatatable() when my counter is = 20 or when row.item is not match, but this method seems like not able to write the remaining records (e.g 801 records, current workflow can print 801/2 , but remaining 1 record not able to print, and the robot always occurs error because the for each loop can’t stop even it reaches the end of the excel row.)
sorry for late reply and thank you.
Aaron