I’m attempting to convert a column within a data table from character to numeric. I’ve used the read csv activity to read the data into a data table. In the CSV the column I’m looking to format at is general format.
I’ve used the assign activity below to try and convert the column but it doesn’t seem to be working.
I’ve attached a sample excel file of the column I’ve read into a datatable and now trying to convert it to numeric type. Thank you. Sample.xlsx (283.4 KB)
I have attached the XAML file which will convert the excel column type from General to Number.
Also, not only the number you can change to whatever format is available in the dropdown.
To use this activities you need to install an external package from the marketplace: UiPathTeam.Excel.Extensions.Activities
It might be more helpful to know what is not working and why you need to convert it. Do you get an error? Are you trying to get rid of the green tick marks in Excel?
Generally, you work with row items as strings until you need to make a calculation, and there’s always a chance there are end spaces or alphas. So, when you make a conversion using Convert.ToInt32(), you should use row(“AVG_BET1”).ToString.Trim rather than using the object type… and, also, use an IF condition before to check if it’s a number with Integer.TryParse(row("AVG_BET1").ToString.Trim,Nothing)
This will prevent conversion errors.
If it’s the green tick marks you want to fix, while I have fixed this before, I can’t remember the best approach to solve it. But I seem to remember using an Excel scope to update the values successfully.
Unfortunately that still didn’t work for me. To give you a little more background I’m trying to sort the “AVG_BET1” column from largest to smallest within my original data table which is dtFinalData. To do this I used the Sort Data Table activity and output the results to a new data table dtHighavgbet.
Next I want to write range the sorted results to an excel file. so I use the write range activity. The issue is the AVG_BET1 column within the results file does not sort correctly because it is formatted as general instead of a number. Screenshots below to explain.
Perfect, thank you this was helpful. I decided to use the BalaReva.EasyDataTable Package. But when I put in my column name or index it gives me the following error. Any advice?
While you enter the values, it needs to be mentioned as an Array format enclosed by curly braces separated by comma {value1,value2}.
Example:
Column Index: {index0, index1}
Column Name: {“Column1”,“Column2”}
Input Datatable: the datatable value from read range
Output Datatable: this should be given in the write range.
It’s not that only one column can be formatted, you can format any number of columns at the same time and that’s why it requires the values in array format.
NOTE: Any one of the inputs is enough, either Column Index or Column Name