Just wondering if you have any idea why the bot is writing the datatable to an excel file using “write range” function, some of the date is written in US format and som date are in local format. Particularly for the date from 12 of the month.
I have checked the format cell of the input file while reading the data out and it seems to be the correct format.
Appreciate any suggestion/helps.
Welcome to the UiPath Community
Please read this post it will be useful for you
Use this code in invoke Code and it will work for both the scenarios
inputDT.AsEnumerable.Where(Function (x) Not String.IsNullOrEmpty(x(0).ToString)).ToList().ForEach(
row(1) = If(row(1).GetType.ToString.Equals("System.DateTime"), CDate(row(1).ToString).ToString("dd/MM/yyyy"), row(1).ToString)
Instead of row(1) you can provide the column name also like row(“columnName_1”). This is only for the first date column only.
For your reference
Date Issue.xaml (5.3 K…
I tried to print out the datatable and the date seems to be correct. Not sure why it happens when datatable is written back to excel with the “write range” function. Also, only happen if the date is from 12 and before.
Can you share the sample excel file?
Try to debug it and look at the value of DT in immediate panel.
You can use the code I mentioned in the post above to convert the date format.
I tried the code and it’s given the same result.
The datatable is absolutely fine. Problem only occurs when datatable is written to excel. Unfortunately, I’m not able to share the excel file.
Use Excel Application Scope and Read Range to read the input file. In the Properties check the preserve format option.
Did you check written excel file ? formats of written cells are consistency ?
If you can’t find any problem , then can use ‘invoke vba’ activity
‘invoke vba’ should be used in excel application scope.
vba file extension: ~.vba
Range(“A1:A30”).HorizontalAlignment = xlRight
The file Im reading from is CSV. It appears that this is not an option.
Right click on the csv file and open it in notepad. Check the format of Date. Share the screenshot.
Yes. All I did is to dump the datatable to the empty excel file.
Are you using the Excel Application Scope to write the excel File?
Try with, Write Range (Workbook) Activity
That works… Just doesn’t make sense though. Do you have any explanation? Appreciate that.
Well it’s cause another problem though. If I use that, all other parts of the sheet don’t get updated.
I added few word.
It is a method that can be used before a fundamental solution
I hope you find a solution.
Try with this code in Invoke code
row("Date") = Date.ParseExact(row("Date").ToString, "dd/MM/yyyy", Globalization.CultureInfo.InvariantCulture).ToString.Split().First
Use Excel Applicaiton Scope for writing the excel file.
Hope it solves your problem
Please change the column name according to your input file. In my input file the date column name is ‘Date’
Error BC30451" ‘datatable’ is not declared. Only thing I change is the datatable name to what I used instead of inputDT. Column name is correct.
you need to change the arguments also.
Click on edit arguments and change the arguments
Refer the below image
Result is still the same as the original issue.
Im thinking of using Write range with the scope to trigger other fields in excel file and use Write range workbook again to write the correct format.
I don’t feel it’s right though just don’t get the issue.
Did you use Excel Application Scope to Write The output excel file?
If No, Please use Excel Application Scope and write range to write the excel file after invoke code.