Transpose - Excel Data using LINQ

[Neal369]

Hello Friends,

I am having excel data as shown below,

I need to take transpose of data strictly using LINQ,

I have written below LINQ, but its not working,

(
From row In DataTbl
From col In DataTbl.Columns.Cast(Of DataColumn)
Select OP_DT.Rows.Add({col.ColumnName,CStr(row(col.ColumnName))})
).CopyToDataTable

Build data table - “OP_DT”

[Anil_G (Anil Gorthi)]

@Neal369

Please try this

(From d In dt.Columns.Cast(Of System.Data.DataColumn) Select outdt.Rows.Add({d.ColumnName}.Union(dt.AsEnumerable.Select(Function(x) x(d.ColumnName).ToString).ToArray).ToArray)).CopyToDataTable

As per your tables dt = DataTbl and OP_DT = outdt

Hope this helps

Cheers

[Neal369]

Hi @Anil_G

Thank you for your quick reply,
I tried LINQ provided by you,

I am getting below run time error,

[Anil_G (Anil Gorthi)]

@Neal369

your output dt should contain number of rows in your input plus one columns…can you please check the same…if your input dt is having 6 rows then we need to have 7 columns in the output dt

To get the output datatable automatically…you can use generate datatable as well…please check below
InputString - String.Join(",",Enumerable.Range(1,dt.RowCount+1))

This generates the required output table automatically with column names as numbers starting from 1

cheers

[Neal369]

@Anil_G

I will try with generate datatable automatically also.

As of now, i am getting below output

Category “Travel” is missing in the output as shown with Red line

Attaching the folder,
ezyzip.zip (185.5 KB)

[Anil_G (Anil Gorthi)]

@Neal369

Thats because of union…it removes duplicates …please try this

(From col In DataTbl.Columns.Cast(Of System.Data.DataColumn) Select OP_DT.Rows.Add({col.ColumnName}.Tolist.Concat(DataTbl.AsEnumerable.Select(Function(x) x(col.ColumnName).ToString).ToArray).ToArray)).CopyToDataTable

Hope this helps

cheers

[Neal369]

Excellent, this one is perfect.