Hi @supermanPunch : Few days ago, you had solved my problem related to date formats. Again I am experiencing the same issue because I am dealing 205K rows of data in excel and reading through the UiPathTeams.XLExcel.Activities. Pl. find attached the error and .xaml file herewith for your kind reference and perusal. It seems the problem is in the Assign Activity.
As, I want to minus the excel Account Opening Date from the static date (02-07-2018) assigned in the assign activity. It is perfectly working fine without using UiPathTeams.XLExcel.Activities
Extra_Large_Excel_Files_Handling.xaml (19.6 KB)
You can try this below expression to subtract the values
Convert.ToString((DateTime.ParseExact( “Input String”,“yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture))-(DateTime.ParseExact(“Input String”,“yyyy-MM-dd”,System.Globalization.CultureInfo.InvariantCulture)))
Hope it works
Hi @Dr_Raza_Abidi_PhD ,
First, Could you print the Account Opening Date values and Static Date values using a Write Line Activity.
I suspect when using the
Read Range of the
UiPath.Teams.XLExcel activities, the formats of the date is changed as compared to when read using Normal WorkBook Read Range activity.
We can just note down the new formats and add it to the format list.
As we do not have data the xaml file couldn’t be of much use from our side, and testing needs to be done on your end.
Hi @Gokul001 : I have tried your code but same error I have received.
Error Message: String was not Recognized as a Valid Date Time.
Can you check the date format
Write the data in the message box and share it here @Dr_Raza_Abidi_PhD
@Gokul001 : My Static Date is:
Pl. find attached the .xaml file herewith for your kind reference and perusal.
Extra_Large_Excel_Files_Handling.xaml (19.7 KB)
Kindly Share the dependensies details and the version of them
UiPath Studio Version: 2020.10.7
in case of hugh data we would recommend to do a mass analysis on non parseable dates and get found out which values are blocking.
- set a breakpoint on a relevant location
- debug and get paused
- open immediate panel
run following statement
dtData.AsEnumerable.Where(Function (d) Not DateTime.TryParseExact(d("ETB/NTB").toString, YOURFORMATSTRING, CultureInfo.InvariantCulture, DateTimeStyles.None, Nothing)).toList
have some help from here:
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum
Find some starter help on analysis getting parsed with multiple formats on the same call
FilterDates_NonValidDateFormat.xaml (8.0 KB)
@ppr : Thanks for your reply Pl. find attached my testing excel and .xaml file herewith for your kind reference and perusal.
After debugging the error is under the IF condition under Else part there is an assign activity.
Very grateful if you give me a solution. I am totally stuck in it.
Extra_Large_Excel_Files_Handling.xaml (19.5 KB)
Testing_Data.xlsx (1.9 MB)
did checked the data as described above
we do see the faulty record and can handle it with trim
@ppr : Indeed, I have tried the code as you described above but unable to solve the problem. I shall be very grateful if you embed your code within my .xaml file which I have shared with excel file (Testing_Data.xlsx) with you.
Many thanks & kind regards,
Hi @Dr_Raza_Abidi_PhD ,
As per the Data that was Provided, the dates read by the Read Range activity is converted into an
OADate representation and Hence we were not able to convert the Date properly since it is a Double value. By Debugging we can get to know the value of the Date at Run Time.
For Handling such changes in datetime values, we could use a
Try Catch on the First Conversion in Assign and then in the Catch Block we could use the Conversion from
OADate as Shown in the Image Below :
I have also assigned the
Account Opening Date Column with the Updated Value of Date, since it was also appearing in the
OADate format in the Output sheet.
Check the below Updated Workflow :
Extra_Large_Excel_Files_Handling.xaml (24.3 KB)
Let us know if it is still giving out errors.
in addition to @supermanPunch lets have a closer look on OADates, DateStrings in 1 or more formats.
- An EAS read range vs. WorkBook read range can result to different column values for the date columns within the data table
- depending on some other factors e.g Excel cell formatting (when a new/another file is processed), Local settings of the system etc. … the value of the date column can flicker between OADates double, strings with different formats
As we can see in my analysis job it was not listing any OADates. Arpan encountered OADates Doubles.
With the motivation to setup a stable running Bot we would recommend that you will check the expected data variations and maybe setup a cascade base on TryParseXX logic.
So it can be incorporated to - IF
- value is double → process as OADate
- value is String → check if it parseable with configured formats
- value is different → route it to an alternate branch
From development perspective we would not recommend to derive routing decissions from Exception occurences as long the exception is not in detail checked for the particular root cause.
With the given tools and techniques we do feel that you are well equipped for the implementation task. For further help we are here for sure
Hats off @supermanPunch and @ppr and thanks a lot for your best efforts and resolution provided. I am very delighted and grateful to both of you for your continuous support.
Issue is resolved now.
Many thanks & kind regards,
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.