Transpose - Excel Data using LINQ

Hello Friends,

I am having excel data as shown below,
image

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”

@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

image

Hope this helps

Cheers

1 Like

Hi @Anil_G

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

I am getting below run time error,

@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

1 Like

@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)

@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
image

Hope this helps

cheers

1 Like

Excellent, this one is perfect. :clap: :clap: :clap: :+1:

1 Like

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