Covert Datatable Column from character to numeric

Hello,

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.

Any help would be appreicated!

row.Item(“AVG_BET1”) = Convert.ToInt32(row(“AVG_BET1”))

image

if you want change excel row into general format try out the below syntax

row.Item(“AVG_BET1”) = Convert.ToInt32(row(“AVG_BET1”)).ToString(“General”)

Hope it solves your issue

Update us whether you got your desired solution or not
Thanks
Robin

@Robinnavinraj_S

I’m not trying to change the row from numeric to general. I’m trying to change it from general to numeric.

Essentially I think I need to change it from a character value to numeric value. Could you provide any guidance on how to do that?

Hi Robin

Can you provide a sample excel or how that excel value look like ? Without data we xant give extact value

Thanks
Robin

@Robinnavinraj_S

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)

Hi @Kevin_Legendre,

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.

image

To use this activities you need to install an external package from the marketplace: UiPathTeam.Excel.Extensions.Activities

ExcelColumnTypeChange.xaml (5.5 KB)

Hope so you’re issue would be solved.

Regards,
@90s_Developer

hi @Kevin_Legendre
Try out the below syntax to convert

row.Item(“AVG_BET1”) = Convert.ToInt32(row(“AVG_BET1”)).ToString(“0”)

Thanks
Robin

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.

1 Like

hi @Robinnavinraj_S

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.

image

image

Excel results

Is your organizations allows you to use third party packages ??

you could use the .OrderBy() or .OrderByDescending(), which would allow you to use conversions during the sort.

Here is an example, where you assign to an Array of DataRows

dtSorted = dtHighavgbet.AsEnumerable.Where(Function(r) Integer.TryParse(r("AVG_BET1").ToString.Trim, Nothing) ).OrderBy(Function(r) Convert.ToInt32(r("AVG_BET1").ToString.Trim) ).ToArray

Then use dtSorted.CopyToDataTable in the Write activity

EDIT: fixed parenthesis issue

Yes they do allow for the use of third party packages.

@Kevin_Legendre,

Then, you can use this package UiPathTeam.Excel.Extensions.Activities and follow this workflow.

Or, you can use BalaReva.EasyDataTable Package and use Change Column DataType activity as below
image

Regards,
@90s_Developer

1 Like

Thanks for the reply. I get the following error when I try that in an assign activity.

image

dtSorted = dtHighavgbet.AsEnumerable.Where(Function(r) Integer.TryParse(r("AVG_BET1").ToString.Trim, Nothing) ).OrderBy(Function(r) Convert.ToInt32(r("AVG_BET1").ToString.Trim) ).ToArray

I think I had an extra parenthesis

1 Like

@90s_Developer

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?

image

@Kevin_Legendre,

Please refer to the below screenshot

image

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

Regards,
@90s_Developer

Perfect, that worked for me. Thank you for the help with this!

@Kevin_Legendre

Respect✌️

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