Writing datatable into multiple excel

Help
, , ,
1

I want to write datatable into different excels. for e.g if there is 2600 records present in datatable. I want a excel file which should not contain records more than 500. so, in this case - 6 excel sheet will be created and last excel sheet will have 100 records and other five will have 500 records each.

2

You can use enumerable to select first 500 rows and copy to a new data table or you can use a write range activity with enumerable directly in the datatable property which is to select the first 500 values @Nikhilesh_Mishra

3

can you please explain in detail. i can write first 500 records. but what about next 2100 records. @HareeshMR

4

Hi there @Nikhilesh_Mishra

Simply keep a counter and switch sheets (or create them using write range activity) once the counter reaches 500 and reset the counter when moving on to next sheet.

use a for each loop as the data set is not large.

5

Hi
Welcome to uipath community
Hope these steps would help you resolve this
—use excel application scope and pass the file path as input
—inside the scope use a read range activity and get the output with a variable of type datatable named dt
—now use a BUILD DATATABLE ACTIVITY and create a table with same structure of column as in excel and get the output with a variable of type datatable named Finaldt
—followed by that use clear datatable activity and mention the datatable as Finaldt
—now use a while loop and mention mention condition like this
Counter < dt.Rows.Count OR (Counter-dt.Rows.count) < 500
Where counter is a variable of type int32 with default value as 0 defined in the variable panel
—now inside the loop use a IF Condition like this
Counter < 500
If true it will go to THEN part where use a assign activity like this
Finaldt = dt.asenumerable().Take(500).CopyToDatatable()
Or it will go to ELSE part where use another IF condition like this
Counter < dt.Rows.Count
If true it will go to THEN part where use a assign activity like this
Finaldt = dt.asenumerable().Skip(counter).CopyToDatatable()
Or it will go to ELSE part where we can leave it empty

—next to this outer IF condition use a assign activity like this
counter = counter + 500
—Then next to this assign activity inside the while loop use a Excel application scope and pass the file path of the excel as input
—inside the scope use write range activity and mention the input datatable as Finaldt and sheetname as ”sheet”+sheetcount.ToString
Where sheetcount is int32 variable defined in the variable with default value as 1
And with range as “A1” and enable add headers property in the property panel of write range activity

—then again use a assign activity to increment the Sheetcount value like this
Sheetcount = sheetcount + 1

Hope this would help you
Kindly try this and let know for any queries or clarification
Cheers @Nikhilesh_Mishra

2 Likes
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.