Convert Column to DateTime Format after Merging

Hi,

I have to merge 2 excel files. My merging process is correct however it is not merging my columns with dates value correctly.

Before merging:

After merging:

When I change my column type to DateTime in excel it works. But is there a way to do it with the UiPath itself?
Thanks!

MDAIMPORT.xlsx (9 KB)

@Hiba_B Can you help me please? :slight_smile: I remember you helped me for this issue: Write full Date Format in Excel column - #13 by Yudhisteer_Chintaram1

Hi @Yudhisteer_Chintaram1 ,

Please a look at this one.

Thank you
Balamurugan.S

1 Like

@Yudhisteer_Chintaram1 - it is writing the date OAdate format which we can convert it to your format.

May I ask , during read range have you checked Presever format? If not can you please check and try.

Update: This is what I meant…

image

Before merging the date is in this format:

After merging:
The wrong date is in Generic Value Format.

I did this to convert it to datetime. It did but after exporting the format is still in “Generic Value”

@Yudhisteer_Chintaram1 - Please check this…just tried for one Column

Invoke Code

dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("ASN Shipment Date")=Datetime.FromOADate(cdbl(row("ASN Shipment Date"))).ToString("dd/MM/yyyy"))

MyOutput

Hope this helps…

1 Like

Hi @prasath17

It works but the format is still Generic. Is it possible to change it to DateTime?
Thanks!

@Yudhisteer_Chintaram1 - Could you please try this…

 dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("ASN Shipment Date")=Datetime.ParseExact(Datetime.FromOADate(cdbl(row("ASN Shipment Date"))).ToString("dd/MM/yyyy"),"dd/MM/yyyy",CultureInfo.InvariantCulture))

For me this is giving the output in dateformat but in M/dd/yyyy format, may be it will work for your case based on the country.

Please try and let me know.

1 Like


I am getting error could you please share workflow I really appreciate

@Aleem_Khan - In the import tab, add system.globalization.

or

you can add the full code as system.globalization.cultureinfo.invariantculture

@Yudhisteer_Chintaram1 - Any luck??

Hi @Yudhisteer_Chintaram1

Use Excel Scope for both reading and writing the excel files. Do not use the workbook activities for reading writing.

For Reading - Excel Scope-> Read Range
For Writing - Excel Scope → Write Range

its not resolve could you please share workflow

@Aleem_Khan - please show me what you have tried ? One trick you can do after adding is …remove and re add the invoke code.

dateformat.xaml (6.2 KB)
MDAIMPORT.xlsx (9.8 KB)
please correct it

@Aleem_Khan - where is the system.globalization in the import tab? which I asked to add …

image

and your invoke code arguments is empty…so I add that and the error is gone…

1 Like

I added system.globalization but still showing error please check in bottom

Please show me what is the error currently you are getting now?? Have you added the Invoke argument as I shown above?? if yes, please show that too

Hi Prashant
Thank you so much for helping me I resolve the problem I just need to know if I need to add new column where should I have to add in this query----
dt.AsEnumerable().ToList().ForEach(Sub(row) row(“ASN Shipment Date”)=Datetime.ParseExact(Datetime.FromOADate(cdbl(row(“ASN Shipment Date”))).ToString(“dd/MM/yyyy”),“dd/MM/yyyy”,CultureInfo.InvariantCulture))

@Aleem_Khan - Look at the below example…you can add datacolumn activity and then use invoke method with method Name SetOrdinal to place the new created column position…in the “Parameters” - You have to set as shown below…

I would suggest to open up a new forum topic, if you are facing an issue.