Trouble writing to excel for date format

Help Studio
1

Hi guys,

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.
image
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.

2

Hi @Ryan_Nguyen

Welcome to the UiPath Community

Please read this post it will be useful for you

3

Thanks Varun.

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.

4

@Ryan_Nguyen

Can you share the sample excel file?
If not
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.

5

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.

6

@Ryan_Nguyen

Use Excel Application Scope and Read Range to read the input file. In the Properties check the preserve format option.

7

Hi Ryan!
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

Sub Main()
Range(“A1:A30”).HorizontalAlignment = xlRight
End Sub

8

The file Im reading from is CSV. It appears that this is not an option.

9

@Ryan_Nguyen

Right click on the csv file and open it in notepad. Check the format of Date. Share the screenshot.

10

You meant this?

image
image208×564 5.58 KB

11

Yes. All I did is to dump the datatable to the empty excel file.

12

@Ryan_Nguyen

Are you using the Excel Application Scope to write the excel File?

If Yes

Try with, Write Range (Workbook) Activity

image

13

That works… Just doesn’t make sense though. Do you have any explanation? Appreciate that.

14

Well it’s cause another problem though. If I use that, all other parts of the sheet don’t get updated.

15

I added few word.

It is a method that can be used before a fundamental solution

I hope you find a solution.

16

@Ryan_Nguyen

Try with this code in Invoke code

inputDT.AsEnumerable.ToList().ForEach(
Sub (row)
	row("Date") = Date.ParseExact(row("Date").ToString, "dd/MM/yyyy", Globalization.CultureInfo.InvariantCulture).ToString.Split().First
End Sub)

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’

17

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.

18

@Ryan_Nguyen

you need to change the arguments also.

Click on edit arguments and change the arguments

Refer the below image

image
image743×412 15.8 KB

19

Result is still the same as the original issue.
image
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.

20

@Ryan_Nguyen

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.