Nested for loop Linq Query

[Rohith_97 (Prabhu, Rohith)]

Hello,

Can someone help me with a Linq Query for the below.

I have a datatable (InputDT)
In each row few columns (say col2, Col3, Col4) has multiple comma separated values.
Values of Col2 : 1, 2
Col3 : A, B, C
Col4 : d

Now there should be a single value from this comma separated values in each row of result.
And there will be Col2Col3Col4 data rows in result

Ex :
Input DT :
Xz100 | 1,2 |A, B, C | d

Result DT
Xz100 | 1 | A | d
Xz100 | 1 | B | d
Xz100 | 1 | C | d
Xz100 | 2 | A | d
Xz100 | 2 | B | d
Xz100 | 2 | C | d

@ppr

Thanks in advance!

[lrtetala (Lakshman Reddy)]

Hi @Rohith_97

resultDT = (From row In InputDT.AsEnumerable()
                             From col2 In row("Col2").ToString().Split(","c)
                             From col3 In row("Col3").ToString().Split(","c)
                             From col4 In row("Col4").ToString().Split(","c)
                             Select InputDT.Clone().Rows.Add(row("Col1"), col2.Trim(), col3.Trim(), col4.Trim())).CopyToDataTable()

[vrdabberu (Varunraj Dabberu)]

Hi @Rohith_97

=> Read Range Workbook

Output → InputDt

=> Use below syntax in Assign activity:

resultDt = (From row In InputDt.AsEnumerable()
            From col2Val In row.Field(Of String)("Col2").Split(","c)
            From col3Val In row.Field(Of String)("Col3").Split(","c)
            From col4Val In row.Field(Of String)("Col4").Split(","c)
            Select InputDt.Clone().LoadDataRow(New Object() {
                row.Field(Of String)("Col1"),
                CInt(col2Val.Trim()),
                col3Val.Trim(),
                col4Val.Trim()
            }, False)).CopyToDataTable()

resultDt is of DataType System.Data.DataTable()

=> Write Range Workbook resultDt back to excel in new sheet.

FLOW:

XAML:
Sequence4.xaml (8.5 KB)

Regards