I have certain set of records for example, say 50000 rows and I need to Copy set of 350 records to new excel sheet. and I need to repeat this activity until I finish with 50000 records. Every time only 350 records, so 50000/350 it comes around 142 sheets.
Can anybody help here, thanks in advance.
Hi @Pramod_Mangale , you have to use Read Range activity for reading the file with 50000 records and then you have to use the append range activity to append the data in chunks and in the sheet name you can write the name of the sheet, and it will append the data in excel
Like we can split the data and read them
The sequence of activities will be like
— use a Build datatable activity and create a same structure datatable as in excel with same column name and order of columns
and get the output as Finaldt
And followed by this use CLEAR DATATABLE ACTIVITY and mention the input as Finaldt
—the inside excel application scope use Read range activity we can set the range like this
Which will give us first set of datatable and name it as dt1 and here in the property panel enable add headers
—then use again a READ RANGE activity and disable the add headers property and mention the range like this
“A1000001” so that it will start from were we left
Where get the output as datatable named dt2
—now use a For each row loop and pass dt2 as input
And inside the loop use Add Datarow activity and mention the ArrayRow as row.ItemArray and datatable as final dt
So all the records from dt2 will now be added to Finaldt
—then finally use MERGE DATATABLE activity where mention the source as dt and destination as Finaldt
So Finaldt will be our datatable with all records from excel
Though it takes the same time or even less than normal excel application scope and read range activity, it will work without any fail or error
Hope this would help you
Kindly refer to the XAML attached here. It may give some idea for your requirement
Forum_ExcelSplitRangeWrite.zip (131.8 KB)
It is working but it have small loophole that is it is creating sheets for non blank rows also, In sample sheet you provided it should only create 4 sheets. But it is creating till the end of the rows in original sheet.
Awesome, It is working Thank you
@Pramod_Mangale Pleasure is mine.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.