Modify a row from a Uipath datatable

datatable
excel
robot
studio
uipath

#1

Hello this is my stage
I have a datatable that I got from an excel with fixed columns , I use the activity UiPath.Excel.Activities.ReadRange , I need to process deleting some columns,and part of that processing is in some of the rows, the Product column may come in white, I need to filter those columns and fill that column with the ND value.

This is an example with part of the excel:
image


#2

Does your table have fixed columns? Do you know the column names well ahead?
Or would that be varying too?


#3

Yes, the table has the name of the columns , in this case it is the named Producto
image
The number of the row can be random , so what I need is to find those rows that have that blank column and fill it with the ND value


#4

You can use something like this. After this For each row activity your datatable would have the ND values in the blank columns.
image


#5

Thanks @kaderms , I was looking for many post and could not find something similar ,
I really appreciate it


#6

@kaderms maybe you can help me with something else,
The Column SubProducto is equal to 1, the final process of the robot is to generate a csv with the datatable , and I need to format this value to a decimal with precision of 2

I’m using this code but it does not work
String.Format("{0:0.00}" , row("SubProducto").ToString)

but I still get the same result 1 and the one I need is 1.00


#7

Fixed your code.
String.Format("{0}.00" , row("SubProducto").ToString)

But if you only need a .00 at the end, you can do this :
row("SubProducto").ToString + ".00"

That is simpler.


#8

It is not to add, it is to format there are cases where the number can be 789.97569, but if I need to have the precision of 2


#9

you can do this :
Decimal.Round(cdec(row("SubProducto").ToString), 2).ToString


#10

it does not work for me, because I also need that to the whole values ​​like 1, it adds them the 2 decimals, giving as a result 1.00


#11

I thought you needed that.
Can you give a few examples of how you want your data to be transformed to.


#12

Like the Format Cells of excel
This is the initial data
image

image

I need this final data
image


#13

I think if you remove second .ToString from your first solution it could work. Definitely, when you apply this format your variable should be numeric data type


#14

There is no straight forward solution that I could figure out. You can try either of the suggestions below :

  1. Loop through rows and columns of data table and convert them using the same logic that I had posted earlier : (This is for one column, follow the same for all the columns in your Datatable)
  1. Open Excel application on using excel activities, select the range and perform formatting actions on the excel application with click / type into / send hotkeys activities. Once the excel sheet is formatted into decimals as needed, you can use Read Range activity again to read all the content into data table.

Here is a thread that you might find useful for this option


#15

By the way, make sure you use “ralt” instead of “alt”. I recently discovered that pressing the “alt” key would get it stuck in down position, so using “lalt” or “ralt” worked much better as the alt keystroke in a TypeInto.


#16

Hi @ClaytonM…I have a data table that i filtered and it became a datarow (In the screenshot, I am trying to filter based on date)…the other columns earlier had values in Percentage for e.g. 76.5% but now it shows as 0.765. Any idea how to keep the original persisted as I need to perform further actions on the filtered datatable/datarow?


#17

Hi.

Excel by default will change percentages to decimals so arithmetic can be used on them. Are you not able to do the same and treat all your percentages like a decimal? (.765*100 = 76.5)

If you want to keep the % character and treat it like a string instead with the original data, then you would need to originally store it wtih a ’ character infront… I think.

The other option is to store the original data in a CSV where the format is maintained. EDIT: And, if you are saving the data to a new file, then you would want to format the columns the way you want, then save as a CSV, and the formats will be maintained.

To be honest, though, I would just treat the % like a decimal because % should be a number. Basically, when you filter it just filter it based on the percent as a number (like Percent<.8, which would be less than 80%)

But, it’s up to you on how your approach this.

Let me know if you need further ideas.

Regards.


#18

Thanks Clayton I will try it out and update the thread.


#19