I have to merge 2 excel files. My merging process is correct however it is not merging my columns with dates value correctly.
When I change my column type to DateTime in excel it works. But is there a way to do it with the UiPath itself?
MDAIMPORT.xlsx (9 KB)
Please a look at this one.
In the release Version 2019.2.0. I have added a new activity called Change Cell Type. It changes the format of the value like below.
In the Cell Format , can specify the custom format what we have in the excel Like below image.
Sample : UiPathGoExcel.zip (18.4 KB)
This is the video to configure the package in local.
Here is the link to know about the formats.
@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…
Before merging the date is in this format:
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
dtdate.AsEnumerable().ToList().ForEach(Sub(row) row("ASN Shipment Date")=Datetime.FromOADate(cdbl(row("ASN Shipment Date"))).ToString("dd/MM/yyyy"))
Hope this helps…
It works but the format is still Generic. Is it possible to change it to DateTime?
@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.
I am getting error could you please share workflow I really appreciate
@Aleem_Khan - In the import tab, add system.globalization.
you can add the full code as system.globalization.cultureinfo.invariantculture
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 …
and your invoke code arguments is empty…so I add that and the error is gone…
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
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.