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
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.
@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.
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.
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.
And
DateTime.TryParseExact(…
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.
Anil_G
ashokkarale
jinal.shah
Gautham_Pattabiraman
postwick
chandreshsinh.jadeja
vrdabberu
Ajay_Mishra
sven.wullum1
Vyshnavi_Nalumachu