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
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()
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.