Transpose excel data

[suraj.l]

hi everyone,
I need to transpose the data from row to column and column to row where rows and columns are both dynamic.
this need to be perform without excel shortcut keys, linq query, and macro vba.
if possible through looping and data table activities.

Note- Rows and columns are dynamic.

[supriya117 (Supriya Allada)]

Hi @suraj.l

Check this:

How to Transpose Excel data or Data Table?

Hello UiPathans Here we discuss how to Transpose the data in Excel or a data table What is meant by Transpose? Input [image] Output [image] Transpose can achieve by two methods → Modern activity- Using Copy/Paste range Activity.(For Excel Processing) → Novigo Solutions. (For Datatable Processing) Modern activity- Copy/Paste range Activity. By Using modern activity we can achieve the transpose method. For classic users can select Show Modern from the Activit…

or

How to move row items to column(Transpose) in excel using UiPath

Hey Rajesh, here is an easy way to transpose row values to columns. Assuming you already have a datatable (lets call it dt_source) with one column of relevant values, which you want to transpose to columns. Initialize a new empty datatable (for transposed values, lets call it dt_target) Iterate through dt_source with a For Each Row activity Inside the For Each Row Body insert an Add Data Column activity Fill the properties with the target datatable (dt_target) and the ColumnName (that would …

[suraj.l]

hi @supriya117 i don’t need activities as of they are third party packages might get error in future as i mention in post through looping or any datatable activities it is possible or not please let me know.

[sasi_poosarla (sasi p)]

@suraj.l

In modern Activities we have a activity copy/Paste Range in that Enable Transpose

Hope It Helps!!

[Gokul_Jayakumar (Gokul Jai)]

Hello @suraj.l
Try this by using Modern Excel activity
Using Copy/Paste Activity :

• Utilise Excel Modern Activities to perform data Transpose.
• Excel Process Scope → Use Excel file → Copy/Paste Activity

• Select the Source and Destination Sheets .
• Next, select the “All” option and activate the Transpose option.

To Enable Modern Activity:
Activity>Filter>Modern (Select it)

[suraj.l]

hi @Gokul_Jayakumar
i had used earlier modern activities sometimes it won’t work proper so i switched my every process in classic activities it works easily.
as i mention in post through looping or any data table it is possible or not please let me know.

[suraj.l]

Hi @sasi_poosarla
I had used earlier modern sometimes it won’t work proper so i switched my every process in classic activities it works easily.
as i mention in post through looping or any data table it is possible or not please let me know.

[Gokul_Jayakumar (Gokul Jai)]

@suraj.l , You can access the Modern activities from Classic Process.

@Gokul_Jayakumar

To Enable Modern Activity:
Activity>Filter>Modern (Select it)

Try the above one. It will display the Modern activities along with Clasic.
For only the transpose step you can use it.
Otherwise you need to use the LinQ Expression.

[supriya117 (Supriya Allada)]

@suraj.l

If you want to iterate through datatable, check this

How to move row items to column(Transpose) in excel using UiPath

HI @sangeethaneelavannan1 Thank you, I have seen this video, but it’s very confusing, I had to close the video after a while, the flow of his explanation is confusing as he was using an already created variable and not creating the one on the go, it’s hard to catch up on that one. A clean text explanation with steps would be really helpful. Thank you.

[ppr (Peter Preuss)]

@suraj.l

if possible through looping and data table activities.

@suraj.l

through looping or any data table it is possible or not please let me know.

Yes is possible as also indicated by others with Link sharings:

For Excel:

• read in - without add Headers
• use first column - all rows as column names and construct a new datatable schema
• then iterate over all columns with colindex > 1 and use all row values as values

For DataTable:

• in general same logic, we just set first column name with a default as it is the turning point

Excel case (Modelling sample)
Preparation

DataTable: an empty DataTable initialized with dtResult = new DataTable
ColumnName = row(0).toString.Trim

Transponation:

[Gokul_Jayakumar (Gokul Jai)]

@suraj.l
Refer to this Xaml file you may get some idea.

Transpose.xaml (11.4 KB)

[suraj.l]

@Gokul_Jayakumar
this is helpful for row to column also provide xaml where rows are dynamic.