I have an excel file with 93K rows . I have to append this file with data from different sources.
Issues : while using Append Range / read Range -I am getting below error :
Tried : With and Without Excel application Scope - No Help
Tried the same Exel with .xlsb - No Help
Tried Updating Binding too … - No Help
However , I noticed that there are several blank rows after the data in the Excel… I tried manually deleting the blank rows - Append Range works Fine
Question : Instead of deleting the blank rows - is there any other approach i can use to fix the process termination exception.
If No, How can i Clear all the blank rows from excel ?
As of now - have achieved it by sending Hotkey go to last row : (ctrl + shift + end and delete key)
- The Data i am working is sensitive so, cannot share the excel …
- Office Version : 14.0.7227.5000 (32 Bit)
- Uipath version : 2018.3.2 Enterprise edition.
You can use “Filter Data Table” activity in uipath. Then in the filter wizard, you can apply operation of “Is Not Empty” and then operate on this newly created datatable. This should resolve the issue.
i thing it is not about black rows, it might be stopping due to huge amount of data.
try the below points :
try to avoid logs for reading and appending activities,
enable the “Private” property in the property panel for read and append activity so
that activities stop logging.
if you’re running append activity in a loop, give some delay like 3 - 5 sec between each cycle.
clear all the collections which are used to store the data frequently.
ex: if your storing the data inside data table, once the data appended clear the data table so that process will not consume much memory and CPU .
kill excel application once processing is done with excel.
i hope above points will help you
@mukeshkala As mentioned by @Vivek_Kumar a small change as you have high number of rows, Post applying filter data table get the count of the table you just filtered and in the append range Activity pass the Count in the Range property. example: “A:”+(Count+1).ToString , as count starts from 0 thus add 1.
I think , I was not clear with my requirement …
I have the source excel having 93k Rows , I have to append this excel with Data from other source.(Datatable from web)
can you please help how a filter datatable will help here …
should i read complete 93k into datatable and again paste it to the same sheet removing the blanks ?
@Vivek_kumar ,Also, How can i Pass Range in Append range ?
Please correct my understanding…
@mukeshkala will your source have fixed no. of rows always?? Ideally not then Read the source get the count of your source table if the count matches the no. of records then do a simple append range if the count doesn’t match use a filter to remove all empty rows, then do a merge or use append range as per your requirement.
So, when you are reading the excel from the web, you can use data scraping and get the entire datatable and then store it into the variable. Let’s assume that the variable name for this is “inputdt”. Then you can use “Filter data table” activity to remove the empty rows with the operation of “Is Not Empty” in the filter wizard. In the ‘input datatable variable’ pass the variable name as “inputdt” and then in the ‘output datatable variable’ pass the variable name as “outdt”. Then work on “outdt”.
Also, for the append range, you cannot pass the range in it. If it’s necessary, then go for “write range” where you can pass the range. All you have to figure out is the right placement of the activity.
@WhenCutEsh Let me try , will update … Thanks …