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

Help Activities
, , ,
1

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.

picture
picture952×952 26.3 KB

2

@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

3

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

1 Like
4

Hi,

FYI, another approach:

image
image660×512 24.9 KB

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
5

@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

6

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
7

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

Rows.Add was missing

8

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