I want to remove the excel rows completely for which the STATUS column has some particular values. I have achieved usin g a seperate datatable and then writing it back to another sheet, but would like your inputs if possivble by .net code which I can use in Invoke Code activity.
Thanks in advance
Hey @Ritika_Singh
You can use .NET LINQ query for the same.
dt_Result = dt_Input.AsEnumerable.Where(Function(row) row("Status").ToString.Trim.Equals("Success")).CopyToDataTable
This is just a model query & you can customize it as required
Hope this helps
Thanks
#nK
Thank you for the solution but I want it through a C# code or .net code , this way I have implemented but in this have to write into another sheet, but I want it in same sheet
Hey @Ritika_Singh
Just write the resultant datatable into the same sheet using Write Range activity.
Pass the same sheet name in the above activity. It will update in the same sheet fulfilling your need.
Hope this helps.
Thanks
#nK
Tried but did not get the expected result coz resultent dt has less rows than original so getting messed
Hi
Hope the below steps would help you resolve this issue
Use a simple assign activity like this
Suppose if you want to have only the column status having value as “success”
dt = dt.AsEnumerable().Where(Function(a) a.Field(of String)(“Status”).ToString.ToUpper.Contains(“SUCCESS”)).CopyToDatatable
But before writing this to a excel with write range make sure that you have deleted the already records and then write on top of it
Else what will happen if the new datatable has less records and if being passed to write range activity, though it overwrites the extra records will be still there
So delete the records first and then write the data
For that use a INSERT/DELETE ROWS activity and pass
Noofrows - dt.Rows.Count+2
Position - 0
Then use a write range activity with dt and add headers property enabled
Hope this would help you
Cheers @Ritika_Singh
Hey @Ritika_Singh
If you use inner join - you may get less rows as you said.
Please try with left join type and the same will work.
Thanks
#nK
Position - 1 and it is slow coz my excel has 30k records and it will keep increasing so this approach is slow for me
Well not really it won’t take much of time not more than 5-6 seconds for 30k records
@Ritika_Singh
Hi,
if possivble by .net code which I can use in Invoke Code activity.
FYI, the following will work.
dt= dt.AsEnumerable().Where(r=>!(keywords.Any(s=>r["STATUS"].ToString()==s))).CopyToDataTable();
Sample20220131-3.zip (2.9 KB)
Regards,
If I dont want to use datatable like a script I am looking for which can be invoked from UiPath
The problem is data will keep increasing on this sheet now its 30k in next executions it will have more rows of data added to it
I have used this way but lil slow as my data is huge, I have a code to delete the excel sheet as below:
**Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
xlApp = New Microsoft.Office.Interop.Excel.Application
wb = xlApp.Workbooks.Open(filepath)
xlApp.DisplayAlerts = False
TryCast(wb.Sheets(sheetname), Microsoft.Office.Interop.Excel.Worksheet).Delete()
xlApp.DisplayAlerts = True
wb.Save()
wb.Close()**
Something similar to this code can I have with If condition and all to remove the rows directly from the excel sheet which meets the STATUS column conditions
Hi,
Can you try to add Microsoft.Office.Interop.Excel to xlDeleteShiftDirection like Microsoft.Office.Interop.Excel.xlDeleteShiftDirection?
Regards,
I tried but it is not removing the rows
Hi,
Does the sample which I attached in the above post work in your environment?
Regards,
ashokkarale
Anil_G
Yoichi
yangyq10
postwick
chandreshsinh.jadeja
aravindbalineni123
Parvathy
aya
PRASHANT_GABHANE