Write range in xls from an extract database: some numbers are not formatted properly

Hello,
I am beginning with UIPath.
I have extracted a database from an outlook email. Then I have done a “write range” in Excel application scope.
It works but I have an issue with the format.
In a same column I have numbers displayed as numbers (so in a correct format) and others that are justified on the left and considered as text. If in enter in one of these cells and press “enter” then the number is formatted correctly.Capture
My question is why do I have different formats in a same column, and how can I solve this problem? I know I can change the format column by column but it will take time and make my automation less relevant…
Thanks for your help!

Hello @Sara_21,
Welcome to the Community!
Check carefully your data from DataTable. Seems that some of numbers can have space between like “7 627”

2 Likes

Hi @Sara_21

Welcome to the community!! :smiley:

So, you have different formats in the same column because there are spaces in between some of those numbers. So when you have a space, it is considered as a string, not an integer.

So to handle this, what you can do is, you can replace the spaces within a For Each Row activity.

For Each Row of DT
row("ColumnHeader") = row("ColumnHeader").ToString.Replace(" ", "")

After the loop, use the write range to write the data to excel. it will resolve your issue :slight_smile:

Let know whether this helps!!

If it helps, make sure to mark my answer as the solution too :slight_smile:

5 Likes

Many thanks, it works!!

1 Like

Thank you, that was indeed the case

2 Likes

Many thanks for your prompt answer, it works!

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.