How to Filter Excel Data by Today's Date

excel
activities
help
uipath

#1

I need help filtering data that’s exported to excel from an application by today’s date. Can someone help me with the proper VB I can use in the FilterOptions field of a Filter Table activity? Or if you know of a better way to filter the data coming in by today’s date, please let me know.

Thank you!


#2

Hello,

I have not done this using the FilterOptions but you can use an Assign activity to set an array of rows or datatable that meets your criteria.

Here’s an example,
arrayRows = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).ToArray()
or
dtNew = dt.AsEnumerable().Where(Function(row) cdate(row(“column”).ToString) = Now).CopyToDataTable()

The difference of the two examples are that an array of rows let’s you update values and it updates to the original datatable, whereas if you copy to a datatable then you are changing the values in the new table.

Note: if your date is in the format dd-MM-yyyy, where month is after day then instead of cdate() use ParseExact which can be found elsewhere on this forum.

You can research more into this method by searching with keywords like vb.net, LINQ, or lambda.

Good luck!


How to filter date range and how to retrieve data from the specified column that has been filtered based on the selected columns
#3

Hello,

Is there a way to filter a DataTable by a range of two dates…? I would like to filter by MM/yyyy but it does not work.

Thank you!


#4

Yeah you can do that. You just need to convert the date string into a date time format. See above on my previous post for more details.

Basically, you can do CDate(datestring) or if it’s in a format where the day is before the month, then you would need to use Date.ParseExact() — there is better details on that by searching online or the forums.

Regards.


#5

Thanks for your support ClaytonM. I did some searches in this community and the web but im still some confused… How could I specify the month/yyyy in order to match.
How should I update this:
=datetime.ParseExact(“30/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).ToArray() rather than =Now).ToArray()

I guess something like:
>=datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)) AND <=datetime.ParseExact(“31/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).ToArray()

Sorry to bother if its too simple, thanks for supporting!


#6

No problem!
yes, you can use that with Now > and Now <=
which will compare if Now is between the 2 dates.


#7

Thank your for your fast reyply. Im trying to do something like this:
ExcelSAPDT.AsEnumerable().Where(Function(row) cdate(row(“Fe.contabilización”).ToString()) >= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)) AND <= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).toArray() but it says “XML name expected” Hope you can support me…

kind regards


#8

3 things:

  1. You have 1 too many ")"s
  2. You need to compare the row("Fe.contabilización”) to both the dates
  3. If you only use one date like “01/07/2018” then you should be only comparing after or before that date, not between… otherwise you are just checking if the dates are equal, unless that’s what you want.

Assuming you do want that, you would have the below:

ExcelSAPDT.AsEnumerable().Where(Function(row) cdate(row(“Fe.contabilización”).ToString()) >= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture) AND cdate(row(“Fe.contabilización”).ToString()) <= datetime.ParseExact(“01/07/2018”,“dd/MM/yyyy”,System.Globalization.CultureInfo.InvariantCulture)).toArray() 

Hope that helps. Let me know if there are still problems.

Regards.


#9

Thank you!! Really appreciated, it works.

Kind regards!