DataTable entered in an Excel file using Write Range doesn’t look the same as the original data.
I have an original Excel File with lots of data inside. I retrieve this data using Read Range to get DataTable object from it.
Then, I use Write Range inputting that DataTable. The action itself works fine, but some numbers are displayed weirdly.
For example, in original Excel, a part of the data looks like
but when I Write Range this using this DataTable, now it looks like
This is merely a part of the problem, and the same problem occurs under other columns where the currency values are displayed improperly.
I don’t understand why this happens. What is confusing is, it doesn’t happen to all rows under the same column. As you can see, some values are copied correctly, but others get so many decimal places values.
Is there any way to fix this? Is this even a UiPath problem?
By the way I click on those corrupted cell on Excel, go to Format, and change the “Currency” or “Number” format to display up to 2 decimal places, but even that doesn’t work. It starts working after I click into those cells and click somewhere else to exit the cell. I am so lost. Can anyone please help?
I attached a .gif file so you can see. I hid other confidential data, but even Excel formatting doesn’t work after Write Range.
Once after write range activity get the datatable with variable named dt use a FOR EACH ROW activity and pass the variable dt as input and inside the loop use a assign activity like this row(“yourcolumnname”) = “‘“+ row(“yourcolumnname).ToString
Concatenating the value with single quotes can set that column to text format and won’t change to any other format
Then we can use Write range activity
Cheers @tomato25
I am a bit lost with your answer. How can I get datatable using Write Range? I thought Write Range is to write datatable into Excel, but not to get DataTable?
—Get the datatable with READ RANGE ac
—Use FOR EACH ROW activity and pass the datatable
—inside the loop use assign activity
—concatenate with single quotes using assign activity
—then net to for each row loop use WRITE RANGE ACTIVITY where pass the datatable as input and write that to an excel
And an example for single quotes with each row data in a datatable
You are writing the values as text instead of numbers. Excel has a known bug where even if you format a number which was earlier formatted as text, it takes a double click on the cell to show as number.
In the read range activity, have you checked Preserve Format checkbox under options?
I’m having a similar issue, I am reading from a csv, then writing to an xlsx. That part of the process is working fine. Then I read from the XLSX and write back to a different tab, at that point, my number column is showing up as an infinite #### and stopping UiPath from running the read range activity.
This is the xlsx that I wrote from the Csv, column D is the number column.
Here in Column E is the write range activity output after filtering the table:
I tried it with both preserve format checked and unchecked. I am using the excel application scope. Any ideas how to get this number column that wrote from csv to xlsx just fine to write from one tab to another?
I was able to fix this issue in a very non-technical way… maybe there’s a reason why this worked, but it seems like the old unplug it and plug it back in. I removed the read and write activities and just put new ones with the exact same inputs and settings in their place… for some reason, new activities with the same inputs and settings fixed the issue. Second time this happened with UiPath… after not being able to duplicate the issue with a different work book I figured why not just try to delete the non-working activities and put new ones… and Voila!
Never Mind, The error is back… I can’t read this range that I just wrote in Excel?
This doesn’t make any sense and I need help! I was put in charge of building a test case for UiPath for our organization, but I can’t find anything on this error and don’t know how to fix it. Any help, please? Again, I read a range from a csv, wrote that to an xlsx doc. Filtered, then re-wrote that range to another sheet in the same document… why is the range I just wrote showing as infinite # signs and then causing an error with read range even when I’m checking the box for preserve format. I also tried doing it in the workbook activity… same issue.
