I have to remove entire row based on some condition (below):
1.Cell in Credit column is empty
2.Transaction Description 1 column containing “SWEEP TRANSACTION”
3.Transaction Description 2 column containing “Amazing” as partial cell value
i have attached the excel file .“Sheet1” - input
and “Sheet2” - output.
Can someone please help me … I have tried different ways but didn’t get success in last 3-4 days.
AhteshamSample data.xlsx (11.1 KB)
Please refer this link
Process rows in excel and write output
With bit modification you can achive your goal
1. Read range activity for excel Sheet1 and save as datatable named InputTable
2. In Assign activity: OutputTable = InputTable.clone
3. For each row in InputTable:
a. row.Item("Credit").ToString.IsNullOrWhiteSpace AndAlso row.Item("Transaction Description
1").ToString.ToUpper like "*SWEEP TRANSACTION*" AndAlso row.Item("Transaction Description
2").ToString.ToUpper like "*AMAZING*"
1. Leave the true portion blank
2. In the false section: Invoke Method (TargetObject = OutputTable; MethodName = ImportRow; Parameters are: Direction = In, Type = DataRow, Value = row)
4. Write range activity: Use OutputTable saving to Sheet2
Note that the above code in the IF statement assumes that you want all 3 conditions to be true to save in the output file. If ANY of the conditions can be true, then change the 2 AndAlso words to be OrElse instead.
Thanks @Dave This one looks good let me try …
i went ahead and ran it myself, and it looks like you want to change the if statement to be “OrElse” instead of “AndAlso”. This way the output from the robot matches what you have in your uploaded example on Sheet2
@Dave I am getting below error.
Am i doing it correctly… ?
@Dave If possible can you please share me xaml file.
Here you go.
md.ahtesham.zip (10.6 KB)
You can fix that error by changing the first portion of the if statement to be
I believe the error is being encountered due to a namespace that needs to be imported - I’m not 100% sure which one though, so the above code can be used without any imported namespaces required
Thanks a lot . It worked … I was struggling with this from last 3 days. Once again thanks a lot … And thank you for helping me solve the error too
You can also get the desire output by using Datatable select
Datatable FilteredResult = YourDatatable.Select("[Transaction Description 1] = 'SWEEP TRANSACTION' and Convert([Credit],System.String)<>' ' and [Transaction Description 2] Like '%Amazing%'").CopyToDataTable
Note- Just would be better to Take Filtered Result in an
DataRow[ ] then put and if condition to check Filtered count is greater then 0 then use CopyToDatatable() otherwise No record exists as per your condition
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.