Filtering Columns and removing the leading Zeros

[Sisay_Dinku (Sisay YDG)]

Hi team

when I try to use this query for filtering columns
dt_input.AsEnumerable.where(function(x) x(“Customer”).ToString.Equals(“CALISUN”) And x(“Store #”).ToString.Equals(“12”) And DateTime.parseExact(x(“Start Date”).ToString,“MM/dd/yyyy 00:00:00”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable
I receive the below error

Also, I want the leading zeros to be removed from the PO # column

[fernando_zuluaga]

Hi @Sisay_Dinku

give a try with the following

dt_input.AsEnumerable.where(function(x) x(“Customer”).ToString.Equals(“CALISUN”) And x(“Store #”).ToString.Equals(“12”) And DateTime.parseExact(x(“Start Date”).ToString,{"M/d/yyyy","dd/MM/yyyy","d/M/yyyy","MM/dd/yyyy","MM/dd/yyyy hh:mm:ss", "dd/MM/yyyy hh:mm:ss"},system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable

to remove leading zeros you can loop and assing a new value like this

CurrentRow("PO #").ToString().TrimStart("0"c).ToString()

Regards!

[postwick (Paul Ostwick)]

If the Start Date column is already string in MM/dd/yyyy format then why are you converting it to datetime then back to string to compare it to a string? This makes no sense.

What data is in the Start Date column and what is its datatype?

Anyway, the error is because you have a value (or values) in that column that don’t match the format you’ve provided.

[Sisay_Dinku (Sisay YDG)]

@fernando_zuluaga I see a compiler error with the query you just sent me.
@postwick the “Start Date” column is in date format in my excel.

[fernando_zuluaga]

Please share your error

[postwick (Paul Ostwick)]

@Sisay_Dinku

the “Start Date” column is in date format in my excel.

There is no such thing. Excel doesn’t have datatypes. So you’ve used Read Range or one of the Excel activities to read the spreadsheet into a datatable. That means the column in your datatable is string. This code converts that string to datetime then back to string. It makes no sense:

DateTime.parseExact(x(“Start Date”).ToString,“MM/dd/yyyy 00:00:00”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”)

Again, the issue is that you have a value (or values) in your datatable that do not match “MM/dd/yyyy 00:00:00” and therefore cannot be converted to datetime using that expression.

Anyway, you should just be using the Filter Datatable activity.

[Sisay_Dinku (Sisay YDG)]

@fernando_zuluaga

Here is your latest query. With the previous one I do see error at this stage.

Note: the column names and and the datable variable names are different.

[fernando_zuluaga]

You are doing something wrong, please check it, else share here your code because it is working for me, also please hover the red alert to display the error message and provide for information

dt1.AsEnumerable.where(function(x) x("Customer").ToString.Equals("CALISUN") And x("Store #").ToString.Equals("12") And DateTime.parseExact(x("Start Date").ToString,{"M/d/yyyy","dd/MM/yyyy","d/M/yyyy","MM/dd/yyyy","MM/dd/yyyy hh:mm:ss", "dd/MM/yyyy hh:mm:ss"},system.Globalization.CultureInfo.InvariantCulture).ToString("dd/MM/yyyy").Equals("01/04/2023")).CopyToDataTable

Regards

[Sisay_Dinku (Sisay YDG)]

With this query -->>
dt_DataSource.AsEnumerable.where(Function(x) x(“Customer Name”).ToString.Equals(“PACIFIC SUNWEAR OF CALIFORNIA”) And x(“Warehouse Id”).ToString.Equals(“09”) And DateTime.parseExact(x(“Start Dt”).ToString,“MM/dd/yyyy 00:00:00”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable

I see the following error

RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
at lambda_method(Closure , DataRow )
at System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable1 source, DataTable table, Nullable1 options, FillErrorEventHandler errorHandler) at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable1 source)
at lambda_method(Closure , ActivityContext )
at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

[Sisay_Dinku (Sisay YDG)]

@fernando_zuluaga

Here is what I read when I hover over the red alert
dt_DataSource.AsEnumerable.where(Function(x) x(“Customer Name”).ToString.Equals(“PACIFIC SUNWEAR Of CALIFORNIA”) And x(“Warehouse Id”).ToString.Equals(“09”) And DateTime.parseExact(x(“Start Dt”).ToString,{“M/d/yyyy”,“dd/MM/yyyy”,“d/M/yyyy”,“MM/dd/yyyy”,“MM/dd/yyyy hh:mm:ss”, “dd/MM/yyyy hh:mm:ss”},system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable

value of type ‘1 -dimensional array of string’ cannot be converted to ‘string’

[Sisay_Dinku (Sisay YDG)]

Hi team,

With the same query in the above I can filter the columns, including the date column using an input file. But with the input file I am currently working on, the date part cannot get filtered and it fails with the following error.

RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
at lambda_method(Closure , DataRow )

[supermanPunch (Arpan)]

Hi @Sisay_Dinku ,

Check the below post on Debugging the Incorrect values present in the Datatable.

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", Culture…

We’ll then be able to identify the difference in formats used and the formats present and then add the formats to the ParseExact() method.

[Sisay_Dinku (Sisay YDG)]

dt_input.AsEnumerable.where(function(x) x(“Customer”).ToString.Equals(“CALISUN”) And x(“Store #”).ToString.Equals(“12”) Andalso DateTime.parseExact(x(“Start Date”).ToString,“MM/dd/yyyy 00:00:00”,system.Globalization.CultureInfo.InvariantCulture).ToString(“dd/MM/yyyy”).Equals(“01/04/2023”)).CopyToDataTable

The issue has been resolved after changing And to Andalso in the date part.
Thanks all for the effort. Much appreciated!