How to Filter Date Column with #N/A Value

Help Activities
, ,
1

Hi All,

My Scenario is to Filter the date column With #N/A Value and Update with 0.00.

Please find attached snipet:

image
image175×664 5 KB

I Used below code using Linq:
io_monthlyrollDT.Select(“3/31/2021=‘#N/A’”).ToList().ForEach(Sub(row) row(“3/31/2021”)=“0.00”)

I am getting error like this:
RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.Data.SyntaxErrorException: Syntax error: Missing operand after ‘’‘’ operator.
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression…ctor(DataTable table, String expression, Type type)
at System.Data.DataTable.Select(String filterExpression)
at UiPathCodeRunner_b3058715d7b44cdea69f4c763d8e793d.Run(DataTable& io_monthlyrollDT)
— End of inner exception stack trace —
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object parameters, Object arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object providedArgs, ParameterModifier modifiers, CultureInfo culture, String namedParams)
at UiPath.Activities.System.Utilities.InvokeCode.CompilerRunner.Run(Object args)
at UiPath.Activities.System.Utilities.InvokeCode.NetCodeInvoker.Run(String userCode, List1 inArgs, IEnumerable1 imps, Object args)
at UiPath.Core.Activities.InvokeCode.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

please help me how to reslove?

Thanks in advance,
Surya.

2

@surya_teja.k

It’s better to view your datatable, As I tried the same by keeping few #N/A and it is showing as empty insted of #N/A

image

So if you are getting the same in your case too, then use below expression

dt.AsEnumerable.Where(Function(row) String.IsNullOrWhiteSpace(row(“3/31/2021”).ToString.Trim)).ToList().ForEach(Sub(row) row(“3/31/2021”)="0.00")

This will result you as below

image

Hope this may help you

Thanks

3

Hi Srini,

I am getting the below error if i am using this query.
dt.AsEnumerable.Where(Function(row) String.IsNullOrWhiteSpace(row(“3/31/2021”).ToString.Trim)).ToList().ForEach(Sub(row) row(“3/31/2021”)=“0.00”)

RemoteException wrapping System.ArgumentException: Column ‘3/31/2021’ does not belong to table DataTable.

4

@surya_teja.k

In Read Range activity enable add headers

Or you can replace with index of the Column too

Hope this may help you

Thanks

5

Hi Srini,

Thanks for replying, i have used with index, i am not getting any error but upadate is not happening.
May i know how this value is getting filtered.
#N/A.

please find the query which is used in invoke code.
io_dt.AsEnumerable.Where(Function(row) String.IsNullOrWhiteSpace(row(7).ToString.Trim)).ToList().ForEach(Sub(row) row(7)=“0.00”)

please find the attached snipet for reference after running with index.
image

6

@surya_teja.k

Can you remove your filter and check
Also can you share your sample excel file to see the issue?

Thanks

7

Ya, its removed srini. there is no filter no w its just a filter syombol.

8

@surya_teja.k

Can you share where still #N.A is there?

Usally if you check the expression, as i told you before #N.A is showing as empty rows

so I placed string.IsNullOrWhiteSpace which will check for Null values

Hope this will understand, as I asked you if you can provide a sample excel, so that I can check the issue

Thanks

9

Thanks for replying.
Please find the sample excel.
Filter.xlsx (5.4 MB)

sheet1 contains which need to be filtred.
sheet2 contains output after filtering.

Thanks
surya

10

@surya_teja.k

If you are using Invoke Code for the expression then make sure that your datatable argument is in/out and make sure you are passing the same datatable variable to write range activity

Still it is not updating then better share your xaml file with project.json file

Hope this may help you

Thanks

11

yes did the same, please find the same
Main.xaml (6.7 KB)
project.json (1.4 KB)
Thanks
surya

12

@surya_teja.k

The output of the invoke code is io_dt, but you are trying to write the old dt, replace dt as io_dt in Write range and see

Mark as solution if this helps

Thanks

13

How can we use io_dt its an invoke code argumet right?

I tried creating both variable and argument. but still didnt work can u share your xaml file?

14

@surya_teja.k

Can you try to use Workbook activities instead of Excel application scope activities and check
It’s working

Hope this may help you

Thanks

15

I tried same issue over there, please share a xaml file it will be helpful.

Thanks,

16

Hi Please check the workflow.

project.json (1.4 KB)
Main.xaml (9.3 KB)

and share the changes where i have to do.

Thanks
suryateja