DT expression not working

Help Activities
, ,
1

Hello,
I need to update a DT column values for all the DT rows.
I’m working with more than 70k rows, so the for each activity is not the best solution (it takes too long) and I’m working with the DT.columns(“ColumnName”).Expression assign activity.

The rule i’m trying to have is DT.columns(“X”).Expression = “[Column A]+[Column B]”

What i have is that the resulting DT does not have all the column X fields populated (even if column A and B have the field populated).

Could you help me?

1 Like
2

Hi @Marzimo0404

Check this for linq query.

3

give a try at stabilizing the string concatenation:
DT.columns("X").Expression = "[Column A]+ '' +[Column B]"

4

Hey @Marzimo0404

Just try the below,

DT.AsEnumerable.Select(Function(row) newDt.Rows.Add(new Object(){ row("ColumnA").ToString, row("ColumnB").ToString, row("ColumnA").ToString + row("ColumnB").ToString })).CopyToDataTable

I’m assuming you are doing string concatenation if not you may need to do the conversion accordingly if required

Hope this helps.

Thanks
#nK

1 Like
5

As a general introduction:
How to Update Data Column Values of a Data Table | Community Blog

For a LINQ approach, ensure the prepared dtResult which has column X on the end

(From d in dtData.AsEnumerable
Let cj = d("Column A").toString & d("Column B").toString
Let ra = d.Itemarray.Append(cj).toArray
Select r = dtresult.Rows.Add(ra)).CopyToDataTable
1 Like
6

Yes i had a look at the mentioned guide already.
I also tried what you suggested before, it doesn’t change the result.

The weired thing is that in an old version of the script it worked perfectly, i can’t figure out why it’s leaving some fields blank

1 Like
7

maybe you can share some more details on the failed rows.

However, did you tried the shared LINQ?

8

Thank you for your time, how should I use this command?
Actually is not working, but maybe is my fault.

Yes the concatenation string is because i need it as a key for a join dt.
In an old code the script was working perfectly

1 Like
9

Hey @Marzimo0404

Could you please post the query you are actually using in code, so that it will be easy to troubleshoot and suggest ?

Thanks
#nK

10

Yes I tried before writing here. I’m posting the script in the following answer

11

Ok, so i Have a DT table called “DTOrigine”, i’m adding a new column “ChiaveBilancio” and i want to fill it with a defined rule:

image

DTOrigine.columns(“ChiaveBilancio”).Expression–>“[Commodity]+[Trade_Type]+[TRANSACTION_TYPE]+[Calculation_Type]+[MarketCodeIdx2]+[CONTROPARTE_CONSUNTIVO]+[Ptf_Bilancio_Da]+[Gestore]”

1 Like
12

i have a total of 70K rows but just 19k are filled in

1 Like
13

Would suggest to focus on the LINQ approach (as more cols are involved instead of mentioned on the topic start)

“[Commodity]+[Trade_Type]+[TRANSACTION_TYPE]+[Calculation_Type]+[MarketCodeIdx2]+[CONTROPARTE_CONSUNTIVO]+[Ptf_Bilancio_Da]+[Gestore]”

(From d in dtData.AsEnumerable
Let colSet = new String(){"Commodity","Trade_Type","TRANSACTION_TYPE","Calculation_Type","MarketCodeIdx2","CONTROPARTE_CONSUNTIVO","Ptf_Bilancio_Da","Gestore"}
Let jc = String.Join(" ",colSet.Select(Function (x) d(x)))
Let ra = d.Itemarray.Append(ca).toArray
Select r = dtresult.Rows.Add(ra)).CopyToDataTable
1 Like
14

Hello,

i modified your code with my DT as follow:
(From d In DTOrigine.AsEnumerable
Let colSet = New String(){“Commodity”,“Trade_Type”,“TRANSACTION_TYPE”,“Calculation_Type”,“MarketCodeIdx2”,“CONTROPARTE_CONSUNTIVO”,“Ptf_Bilancio_Da”,“Gestore”}
Let jc = String.Join(" ",colSet.Select(Function (x) d(x)))
Let ra = d.Itemarray.Append(jc).toArray
Select r = dtresult.Rows.Add(ra)).CopyToDataTable

Uipath reports:
Assign: Input array is longer than the number of columns in this table.

15

check and ensure the correct dtresult col structure

1 Like
16

Ok so maybe it’s my fault, i did not explain well how my starting DT is.

the starting DT has about 40 columns, i need just the mentioned before to create a unique key in a new column (as shown in previous screen).

I guess that with your code that’s not possible since i’m taking into account just a subset of columns

17

Have you checked if you have cell with null value(s) where column X is empty?

E.g. the first three rows in the table below have null values:
With the expression "[Dosage]+[Drug]+[PatientID]" in Sum column you get:

image

If you modify the expression to check for null and replace it with an empty string, you will get:
Expression: "ISNULL([Dosage],'')+ISNULL([Drug],'')+ISNULL([PatientID],'')"
image

So your null safe expression should be:

"ISNULL([Commodity],'')+ISNULL([Trade_Type],'')+ISNULL([TRANSACTION_TYPE],'')+ISNULL([Calculation_Type],'')+ISNULL([MarketCodeIdx2],'')+ISNULL([CONTROPARTE_CONSUNTIVO],'')+ISNULL([Ptf_Bilancio_Da],'')+ISNULL([Gestore],'')"
1 Like
18

No, as it is exactly adressing it

dtData is your origin datatable

just prepare dtResult by:

  • dtResult = dtData.Clone
  • Add data Column activity and add the datacolumn (X) for the concatenated string on the end

Then run the LINQ

1 Like
19

This is what UiPath shows:
Assign: Exception has been thrown by the target of an invocation.

20

Did you mean to reply to @ppr or me? My suggestion is just to modify your Column.Expression:

DTOrigine.columns("ChiaveBilancio").Expression = "ISNULL([Commodity],'')+ISNULL([Trade_Type],'')+ISNULL([TRANSACTION_TYPE],'')+ISNULL([Calculation_Type],'')+ISNULL([MarketCodeIdx2],'')+ISNULL([CONTROPARTE_CONSUNTIVO],'')+ISNULL([Ptf_Bilancio_Da],'')+ISNULL([Gestore],'')"
1 Like