I am capturing data from an Excel file, the format I receive is dd.MM.yyyy, but when I try to convert it to DateTime it tells me that the format is not correct.
What happens is that it interprets it as MM.dd.yyyy and as long as the dates are type 01.01.2024 or 07.02.2024 there is no problem, but when the date is type 06.23.2024 the error occurs; Obviously there is no such thing as the 6th day of the 23rd month.
I have tried to do ParseExact and I can’t do it.
Somebody could help me?
(I leave a screenshot of the last thing I tried and the error it gave me)
I think you have two date formats i.e. dd.MM.yyyy and MM.dd.yyyy.
Try the below syntax:
DateTime.ParseExact(CurrentRow(1).ToString,{"dd.MM.yyyy","MM.dd.yyyy"},System.GLobalization.CultureInfo.InvariantCulture,DateTimeStyles.None).ToString("dd/MM/yyyy")
Hope it helps!!
Generally the dateformat you see in excel and what you get when you read might differ…if you need same as excel then try using display value option …or preserve format option …
It is not good practice to include both kinds of formats where date and month interchange as there would be hogh chances it is misinterpreted
Cheers
1 Like
If you are able to edit the Excel file, you could try setting the date column format to “Text”. This usually helps UiPath to read the value as-is.
@efelantti
The Excel Column format is ‘General’. So it shouldn’t be a problem, right?
@Anil_G
My idea is to work with a single format, so as not to cause mistakes or errors.
Great, it worked, after trying several possibilities, I settled on this one:
DateTime.ParseExact(CurrentRow(1).ToString,("dd.MM.yyyy"),System.GLobalization.CultureInfo.InvariantCulture,DateTimeStyles.None).ToString("dd/MM/yyyy")
I think the key was in ‘TimeStyle.None’
Thank you all for everything.
A curiosity… how can the code be inserted in a drawer that can be seen large and can be copied, as you have done.
I think it has to be specifically “Text” in order to get it exactly like it’s displayed.
@efelantti
In principle I cannot and it is not recommended, because the excel file is on the network in a shared folder of the client, and the client does not want the structure of the excel to be modified.
I should only read the excel, process the data in datatable, and store it in a table in the database.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.