How To Change Excel File as a DataTable from Wide to Long

Hello everyone,

I couldn’t find how to change datatable from wide to long format. Can anybody show me how to do it in UiPath studio? Thanks. Here is the example.

@mikemanand

Welcome to the community

You can use create pivot table for this look

Apart from that if you want to do it in studio and write then

Build datatable with 3 columns Name Attribute and value

Now read the datat into datatable

Then use for eqch row in datatable activity

Inside that use for each with dt.Columns.Where(function(x) Not x.ColumnName.Equals("Name")).ToArray

Now inside that use add datarow activity on the table created with 3 columns and array row as {currentRow("Name").ToString,currentitem.ColumnName,currentRow(currentitem.ColumnName).ToString}

After all loops write data back to excel

Hope this helps

Cheers

we assume that the input data is read in e.g. with a read range into a datatable - dtData

  • Build datatable activity - dtResult and configure the 3 cols: Name, Attribute, Value

  • Assign Activity
    dtResult =

( From d In dtData.AsEnumerable
From cix In Enumerable.Range(0, dtData.Columns.Count).Skip(1)
Let ra = New Object(){d("Name"), dtData.Columns(cix).ColumnName, d(cix)}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

finaly write back dtResult to an Excel, if needed

Hi,

FYI, another approach:

dt.AsEnumerable.SelectMany(Function(r) dt.Columns.Cast(Of DataColumn).Skip(1).Select(Function(dc) dtResult.LoadDataRow({r(0),dc.ColumnName,r(dc)},False))).CopyToDataTable()

Sample
Sample20240211-2.zip (7.5 KB)

Regards,

1 Like

@mikemanand
We do feel that the question is answered and the topic can be closed:
Forum FAQ - How to mark a post as a solution - News / Tutorials - UiPath Community Forum

Hi @ppr,

I have tweaked your solution a little bit as your solution cause some error. But it works for me at least and I need to check if this can be use as a dynamic.

1 Like

@mikemanand
thanks for the feedback,
the buggy line is now fixed:
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

Rows.Add was missing

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