I want to append a new column into Excel

[Mengdi_Wei]

Hi all,
I am new to UiPath. I want to append a new column to the very end, and the value of the new column is using the numbers in the existing column (column header name is “ValidatedDistance”) multiple $65.5 (e.g. 19.2 * 65.5 = 1257.6). I want numbers like 1257.6 in the new column. Can anyone help me with that? Thanks!

[arjunshenoy]

Hi @Mengdi_Wei, welcome to the Community.

You can use the following query in the Invoke Code activity:

io_dt.AsEnumerable.ToList.ForEach(Sub(row)
row("New Col") = CInt(Row("ValidatedDistance").ToString) * 65.5
End Sub)

Workflow:

Edit: The ‘Argument’ values for Invoke Code -

Hope this helps,
Best Regards.

[Anil_G (Anil Gorthi)]

@Mengdi_Wei

Welcome to the community

You can use as below…

1. Use add data column to add a new data column to the existing datatable if column is not already present
2. Then use expression to perform this using a assign activity dt.Columns("DeductableCost").Expression = "65.5 * [ValidatedColumn]"

cheers

[Srini84 (Srinivas)]

@Mengdi_Wei

ew column is using the numbers in the existing column (column header name is “ValidatedDistance”) multiple $65.5 (e.g. 19.2 * 65.5 = 1257.6). I want numbers like 1257.6 in the new column. Can anyone help me with that? Thanks!

@Mengdi_Wei

You can use Add Data Column activity
After that, you can use For Each Row and in Properties declare a variable as Index

So, to fill the column you can use Assign activity and write as below

dt_validated.Rows(Index).Item(“StandardMileage”) =Cint(row(“StandardMilege”).ToString) * 65.5

Hope this may help you

Thanks,
Srini

[Vishnuraj_Pandiyarajan2 (Vishnuraj Pandiyarajan)]

@Mengdi_Wei

65.5

Hi @BlueBird1
IF youwanna Multiply one column with your value u can follow this simple.

Step:1 Add Datacolumn (which u wanna toadd in the Table)
Step:1 drag Assign Activity
Left Side:

DT.Columns(“ValidatedDistance”).Expression

Value side

“[Column1]*65.5”

here attached Xaml
test.zip (69.5 KB)

Thanks
VP

[Mengdi_Wei]

It shows an error like the image, I can’t understand why the error is

[Srini84 (Srinivas)]

@Mengdi_Wei

Can you please replace the "(Quotations) correctly, you have copied and pasted the expression that where it was bit different

Thanks,
Srini

[Mengdi_Wei]

Thanks, but for some reason, I couldn’t open the workflow after I downloaded it. I tried using the step you said, but it runs an error on my workflow.

[Mengdi_Wei]

Not quite sure what should I do, but I replace the code you said before

[Anil_G (Anil Gorthi)]

@Mengdi_Wei

The standard milege should be double type

also please open the locals panel and check the error details from there…it would give more info

Did you use expression like this ?

@Anil_G

dt.Columns(“DeductableCost”).Expression = “65.5 * [ValidatedColumn]”

cheers

[Mengdi_Wei]

DT_validated.Columns(“DeductableCost”).Expression = “[ValidatedDistance]*65.5”

I used this one above. Sorry I am not majoring in CS, I don’t know how to open the locals panel.

Now I assign the standard mileage rate to double type, but I don’t know if I should continue to use the “standardMileageRate2023” variable, it seems like if I just use the equation “[ValidatedDistance]*65.5” there is no need to assign a special variable.

[Anil_G (Anil Gorthi)]

@Mengdi_Wei

If you use 65.5 firectly yes you need not use it

Run the bot in debug mode…and when error comes …on the left side you would see locals panel just like activities projects etc…open that and check the error

Even if you use 65.5 directly you are getting the error?

If possible please share input excel and/or the xaml you are using

cheers

[Vishnuraj_Pandiyarajan2 (Vishnuraj Pandiyarajan)]

@Mengdi_Wei

It will definitely work.
Inside the Assign Activity

Just Copy and Paste the expression below in the Assign Acivity

Left Side:

DT.Columns("ValidatedDistance").Expression

Note: DT = Your Datatable

Righ Side:

"[Column1]*65.5"

Note: Column1= Which column u want to multiply with 65.5.

Thanks
VP

[Mengdi_Wei]

Mini Bot Test.zip (71.9 KB)
Here is the input excel (actually the one in the output folder named “validated”)and the xaml I am working on.
I am stuck on the “Test Sequence”.

Thanks

[Mengdi_Wei]

Thanks, VP, but my professor doesn’t recommend I use the expression since we did not learn this before. I really stuck on how to use this expression.

[Mengdi_Wei]

Hi all,
I found a way to solve my “append a new column” question. Thank you all for helping me.
Now, I meet a new question about filter data table, I wonder if anyone of you can help me with that.

Problem on filter data table activity

Hi All, I meet a problem when using the filter data table activity. I want to filter out the rows that only “AbsoluteDifference” > 2, but my workflow does not work. There is nothing shown on the output folder - validated Excel - sheet 2. I wonder if you can help me with that. I put my xaml and input excel here. Thank you very much! [image] [Screenshot 2023-05-10 164849] [image] [image] Question bot.zip (103.1 KB)