Delete record from excel

In excel sheet I have 373571 records in this in one column name abc contain zero value so I need to delete all row which abc column contain zero value

1 Like

@Palaniyappan

Hi
instead of deleting them we can get the rows of that column without 0 like this
where get the datatable variable with dt as output from READ RANGE ACTIVITY

dt = dt.Select("[yourcolumnname] <> ‘0’").CopyToDatatable()

then use WRITE RANGE ACTIVITY with same dt as input

Cheers @Aditya10989

1 Like

due to large data I am not able to get this in read range I am executing this last 20 minutes
not remove value -345,.1,-$.1
remove value 0,.01,-.01,.009,-$.009 like this
means only remove those value which value almost zero

@Aditya10989

Try below select query.

         newDT = inputDT.Select("[ColumnName] > '0'").CopyToDataTable
1 Like

thanks for reply before this I need to insert data in excel scope read range I still showing executing read range even after 15 mint

1 Like

@Aditya10989

It’s because excel file contains more than 3Lakhs records and will take more time for this. It’s expected only.

May I know RAM size in that machine ?

1 Like

8 GB RAM.Now what I need to do in this case.In my excel sheet contain 3 lakh records after removing zero it left 17 thousand record. This record I need to process

1 Like

@Aditya10989

Other alternative is to split the data and filter it and then merge it.

1 Like

@lakshman thanks do you have any sample code

1 Like

@Aditya10989

I did it long back in one of the process and that to in client machine. So i don’t have any example workflow now.

Hi @Aditya10989

Since you have a large data set, I feel you can do the filtering and deleting part within the excel sandbox since it can handle large number of rows.

Either write a macro that can does this for you and execute the macro with UiPath
Or Do the UI automation to filter and remove the cells.

Macro is the most advised way and the best way for efficiency and stability,

This article will help you do the macro part

1 Like

@Aditya10989 Due to large amount of data it may take time using uipath to read the data and process it . Instead of that write a python code to process the data and invoke it in uipath . It will complete the process in sec so its better to use that .

2 Likes

@Aditya10989 Let me know if you want any help for python code .

1 Like

Hi @Aditya10989,

I have developed new activity called “Delete Row” . It deletes the contiguous row.

Below I have show the screen shot of the design.

DeleteRange.zip (31.1 KB)

Regards
Balamurugan.S

1 Like

@balupad14 thanks for this right now I am open application DeleteRange I am getting error “Error Detecting project version”.
I instal, the package in my system.now what I need to pass in property in Find activity and Delete Rows activity…?

1 Like

thanks for help can you please suggest me how to execute macro in uipath…?

1 Like

You can use the execute macro activity for that

1 Like

@Varun_Raj I searched on google How to create macro in uipath. for this we need to create macro in every excel sheet.Excel will changed every month end.We need to apply macro in every excel sheet So never Client create a macro for every excel sheet.

@Aditya10989,

You can create one excel and write the macro and save it as “xlsm” file and use it to execute the macro on the different excels.

You just need to refer which execute the macro should execute on in the macro source code with help of

Workbook function.

1 Like