Convert from 1 datatable to another datatable

Help Activities
, , , ,
1

hi is there any way to transfer data from frm DT1 to DT2, DT1 has 2 columns but only need values from the column below, and sequence is not same as DT2.

DT 1:
d
b
a
c
e
d1
b1
a1
c1
e1

DT2
a | b | c | d | e
a1|b1|c1|d1|e1

2

Hi @TyraS

=> Build Data Table dt1

image
image617×526 9.21 KB

Output → dt1

=> Build Data Table dt2

image
image602×426 7.84 KB

Output → dt2

=> Use the below code in Invoke Code activity

Dim row As DataRow = dt2.NewRow()

For Each item As DataRow In dt1.Rows
    Dim value As String = item(0).ToString.Trim

    If value <> "" Then
        Dim columnName As String = value.Substring(0, 1)

        If DT2.Columns.Contains(columnName) AndAlso value.Length > 1 Then
            row(columnName) = value
        End If
    End If
Next

dt2.Rows.Add(row)

Follow the below screenshot to add the arguments:

image
image1252×862 25.9 KB

=> Use the dt2 datatable wherever needed.

Output:

image
image152×205 2.37 KB

Please check the below workflow for more understanding:

Main.xaml (12.3 KB)

Regards
PS Parvathy

3

Hi @TyraS

please try this:

if solution works for you please mark as solution
thanks
Nishi

4

Hi @TyraS, nice solution from Parvathy, works great for this exact sample. One thing worth knowing if your real data ever changes shape

that code figures out the column using value.Substring(0,1), the first character of each value. It only works because in this example the column names are single letters (a,b,c,d,e) and the values happen to start with that same letter (a1,b1..). If your actual column names are longer than 1 character, or the values dont start with the matching letter, the row(columnName) check silently fails and that value just gets skipped, no error, just missing data, wich is the annoying kind of bug to catch later

if you know DT1 always comes as two blocks in the same order, first N rows are the labels, next N rows are the values matching that same order (like in your example, positions 0-4 are labels, 5-9 are the values for those same labels in order), a more robust way is pairing by position instead of by first letter

this doesnt care what the column names look like or what the values start with, it just trusts the position, label at index i pairs with its value at index i+half. Only works if that fixed pairing pattern always holds though, if the order ever gets scrambled Parvathy’s approach (or a dictionary keyed by label instead of substring) would be safer. depends on how consistent your real source data is