How do I parse in multiple datetime format strings into DateTime?

Help Activities
1

Hi guys, please help… I have an excel with ~60k rows. I have a column called “Date” and I would like to filter rows between 2 dates. However, I have dates of various formats:

  • 3/8/2018 0:00
  • 4/11/2018 0:00
  • 31/7/2018 0:00
  • 30/10/2018 0:00
  • 1/10/2018 12:00:00 AM
  • 10/1/2018 12:00:00 AM
  • 10/12/2018 12:00:00 AM
  • 4/12/2020 6:22
  • 28/4/2020 9:22
  • 29/10/2019 15:07

Based on some forums, I managed to write an assign formula but it returned with the error - The value of type 1-dimensional array of String cannot be converted to “string”.

My workflow
DateFormats = {“dd/M/yyyy”, “d/MM/yyyy”, “d/M/yyyy”, “dd/MM/yyyy”, “dd/M/yyyy h:mm”, “dd/M/yyyy HH:mm”, “dd/M/yyyy HH:mm:ss tt”, “d/MM/yyyy h:mm”, “d/MM/yyyy HH:mm”, “d/MM/yyyy HH:mm:ss tt”,“d/M/yyyy h:mm”, “d/M/yyyy HH:mm”,“d/M/yyyy HH:mm:ss tt”, “dd/MM/yyyy h:mm”, “dd/MM/yyyy HH:mm”, “dd/MM/yyyy HH:mm:ss tt”}

dt2 = dt1.AsEnumerable().Where(Function(row) Datetime.ParseExact(row(“Date”).ToString, DateFormats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat) >= Datestart AND Datetime.ParseExact(row(“Date”).ToString, DateFormats, System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat) <= Dateend).CopyToDataTable

2

@gerlynn_lxy

have a look here:
Parsing_MultipleFormats_DTConfig.xaml (13.0 KB)

the fourth argument within the ParseExact working with date formats is missing
go eg for a
DateTime.ParseExact(row(0).ToString.trim, arrFormats, cultureinfo, DateTimeStyles.None)

where arrFormats = your DateFormats, cultureinfo is a prepared variable

for shortening the code just import the namespace as below:
grafik

3

Thank you for the quick reply, sorry I’m new but I am trying to filter the dates into a new datatable, may I have more guidance on how to achieve it?

4

Hi @gerlynn_lxy,

My suggestion is instead of putting this much of effort in filtering dates in different formats. Write a VBA script to convert all the date-time values in the Date column to a common date-time format.
Then it would be very much easy to filter them out.

Cheers!!
Prajwal

5

Just give me a few mins I will adopt for you

6

Thank you @ManiPrajwal_K, the original excel is in a consistent format. However, when i used read range, it converts “custom” format to “general”. When i tick “preserve format” in read range, it takes too long too load.

7

Interesting @gerlynn_lxy … may I know the process outline.
Like if your task is to filter out the rows between 2 required dates, then you can make it much more simple by using VBA script, as you said the original excel sheet maintains consistent format. Instead of using read range for such a huge amount of data!!

8

@ManiPrajwal_K Thank you for the suggestion. Would it be possible to consult you on some queries because I am not versed in VBA. My intention in using UIPath is to automate the tedious process of filtering multiple columns in multiple files.

  1. Can I write a script in VBA to filter and make use of RPA to “Invoke VBA” to automate the whole process?
  2. The excel file i have is in XLS format, to “Invoke VBA” does this mean that I have to convert the file into XLSX format?
9

@gerlynn_lxy
find a reworked approach here:
FindDates_MultipleFormats_WithinDateRange.xaml (16.6 KB)

First Stage: Configure the DateFormats inside a DataTable (can be later externalized)
Second Stage: Filter out all dates that are compatible with the configured date formats
Third Stage: Create a report Datatable for all non parseable dates
Fourth Stage: do the filtering

As it is defensive, checks the possibilities of parsing and also is handling empty results the code is now more stable and ready for masses

1 Like
10

Yes @gerlynn_lxy you can use UiPath to work on Excel application but here I would like to suggest you go for VBA because the amount of the data is considerably very big. So VBA scripting might speed up your execution and makes it easy to build.

  1. Yes you can write a VBA script to automate the whole process and use Invoke VBA activity in Excel Application scope to execute VBA using UiPath.
  2. No need to change any Excel format.

Hopefully, to get you on the right path, you can consider the following general VBA script to filter out data between two dates.

Public Sub MyFilter()
Dim lngStart As Long, lngEnd As Long
lngStart = Range("D1").Value 'assume this is the start date
lngEnd = Range("D2").Value 'assume this is the end date
Range("B1:B29").AutoFilter Field:=1, _
    Criteria1:=">=" & lngStart, _
    Operator:=xlAnd, _
    Criteria2:="<=" & lngEnd
   End Sub
11

@ppr thank you for the detailed guide. The dates is a column within the datatable. In that case, how can i reference to it? My intention is to extract the whole row by filtering dates in the particular column.

12

Thank you @ManiPrajwal_K for the script, i will give this a try and update you again!

13

@gerlynn_lxy
demo is already doing this. The data sample has currently for prototyping reasons only 1 column but is doing following:

  • check the date column of row if it falls into the date range
  • retrieves / moves the entire row

You can explore by just adding an additional column to the test data and checking the result