How to? New datatable column as substring of existing column without for each

Help Activities
, , , , ,
1

Hello together,

i have the following datatable (here only an example - 60000+ rows):
column 1 column 2
A 1000
B 2000
C 3000

I want to have the following:
column 1 column 2 column 3
A 1000 10
B 2000 20
C 3000 30

I tried it with linq but not successful. I do not know how to select the field in the row of a specific column.

(
From row In DT.AsEnumerable()
Let a = row.ItemArray.Select(Function (x) x.ToString.Substring(0,2)).ToArray
Select TestDB.Rows.Add(row(“Column1”),row(“Column2”),a)
).copyToDataTable

Any other approach except “for each” is also welcome. Thank you in advance!

2

Hi @PeCour

  • Assign TestDB = DT.Clone
  • Add a column (“Column3”) to TestDB using the Add Data Column Actvity
  • Use below LINQ

TestDB =

(
    From row In DT.AsEnumerable()
    Let a = row("Column3").ToString.Substring(0,2)
    Select TestDB.Rows.Add({row("Column1"), row("Column2"), a})
).CopyToDataTable

Modifying the LINQ a bit

(
    From row In DT.AsEnumerable()
    Let a = row("Column3").ToString.Substring(0,2)
    Select TestDB.Rows.Add(row.ItemArray.Append(a).ToArray)
).CopyToDataTable
1 Like
3

Hi @PeCour ,

Maybe you could also achieve the same result with DataColumn.Expression property as shown below :
image

DT.Columns("OutputColumnName").Expression = "SUBSTRING(InputColumnName,1,2)"
1 Like
4

How to Update Data Column Values of a Data Table | Community Blog

1 Like
5

Thanks to both of you!

@kumar.varun2: This worked well.

@supermanPunch: I tried it but it did not work as wished. The processing should work as well, I believe (Immediate). But, it did not save the values in the empty column.

6

Oh, I did not find that blog entry. When I have questions I always use google, the community blog entries are seldom on the first-page search results. The forum dominates.

But it looks great! And very good explanation.

7

@PeCour ,

Could you maybe provide us with the Screenshots of it’s usage in your xaml, Just for Debugging purposes if possible and also confirm if there was no Errors produced ?

8

Hello People,

I believe replacing ‘Column3’ with ‘Column2’ in the above LINQ query as below will work:
From : Let a = row(“Column3”).ToString.Substring(0,2)

to Let a = row(“Column2”).ToString.Substring(0,2)

@PeCour, try the above one

Regards,
Kalyan

1 Like
9

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