Quick Guidance Please

[Sudharsan_Ka]

Hi All

I have used this query to return the rows but no values are returning in the output of this
dt.AsEnumerable().Where (Function(D) DateTime.ParseExact(D(“Data validarii CN”).ToString,“yyyy-MM-dd HH:mm:ss”,CultureInfo.InvariantCulture).ToString(“MM”) = now.AddMonths(-1).ToString(“MM”)).CopyToDataTable

So the task is to get filter only the rows which are in the date of currentMonth -1

Here is the input.

@ashwin.ashok @ppr @Yoichi
input.xlsx (106.4 KB)

Regards
Sudharsan

[Nithinkrishna]

@Sudharsan_Ka

dt.AsEnumerable().Where (Function(D) DateTime.ParseExact(D(“Data validarii CN”).ToString,“yyyy-MM-dd HH:mm:ss”,CultureInfo.InvariantCulture).ToString(“MM”) = now.AddMonths(-1).ToString(“MM”)).CopyToDataTable

Hey @Sudharsan_Ka

Kindly try this statement,

dt.AsEnumerable().Where (Function(D) DateTime.ParseExact(D("Data validarii CN").ToString,{"yyyy-MM-dd HH:mm:ss","MM/dd/yyyy HH:mm:ss"},Globalization.CultureInfo.InvariantCulture,Nothing).ToString("MM") = now.AddMonths(-1).ToString("MM")).CopyToDataTable

I was checking the input sheet and last row date was in custom format due to some reason (may be a manual error)

Either you need to update code as above or make the excel format proper.

Hope this helps.

Thanks
#nK

[Sudharsan_Ka]

it throws source contains no row error in the expression @Nithinkrishna

[ppr (Peter Preuss)]

@Sudharsan_Ka

dt.AsEnumerable().Where (Function(D) DateTime.ParseExact(D(“Data validarii CN”).ToString,“yyyy-MM-dd HH:mm:ss”,CultureInfo.InvariantCulture).ToString(“MM”) = now.AddMonths(-1).ToString(“MM”)).CopyToDataTable

lets rewrite to Query syntax and do checking on month instead of string base

(From d in dt.AsEnumerable()
let dp = DateTime.ParseExact(d("Data validarii CN").toString.Trim,“yyyy-MM-dd HH:mm:ss”,CultureInfo.InvariantCulture)
Where dp.Month = now.AddMonths(-1).Month
Select r=d).CopyToDataTable

But when your data is away from last month so result is empty

Also have alook here:

[FirstAid] Handling of The source contains no DataRows exception

This FirstAid Tutorial will describe how a the source contains no DataRows EXCEPTION can be handled. Introduction Let’s have a look at the following filter data table scenario: Name CCode Tom US Charlotte FR Seema IN Jean FR Assignment: Filter all rows on a particular Country Code like FR, UK, ES, UK… Ensure that all CCode data column values are trimmed Ensure that the Filter check is case insensitive we can implement it e.g. with the help of a LINQ statement: dtData.As…

[Nithinkrishna]

Hey @Sudharsan_Ka

Is your variables right please check, I have used my own test var.

Thanks
#nK

[Sudharsan_Ka]

yes it is @Nithinkrishna

[Nithinkrishna]

Hey @Sudharsan_Ka

Check my run please,

I’m just using the same query which I posted above. (Just updated it as per my data table variable)

Thanks
#nK

[ppr (Peter Preuss)]

@ppr

Where dp.Month = now.AddMonths(-1).Month

For a more reliable check over the years also have a look on following filter strategy:

Where dp.toString("yyyy-MM").Equals(now.AddMonths(-1).toString("yyyy-MM"))

[Sudharsan_Ka]

Now it is returning empty string @Nithinkrishna @ppr

[ppr (Peter Preuss)]

@Sudharsan_Ka

Now it is returning empty string

such a case just debug and inspect the datatable content (this counts, not the excel)
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

it could be tha case that different Date formats will be present within the datatable after read range. Just check this also

[Sudharsan_Ka]

I am too sorry for the wasting your time @Nithinkrishna @ppr it’s my f… blender mistake in a hurry.

The Linq i shared it self printing the values. i have passed the wrong variable to print in the message box

Anyway thanks both of you for sharing your ideas

Regards
Sudharsan

[Nithinkrishna]

Cool @Sudharsan_Ka