Convert Excel table <Horizontal to Vertical>?

[Hyde]

Hi,
Could you teach me if I need to convert my excel table from Horizontal to Vertical.
What Activities suitable for my issue? (Incase my data over 100 rows.)

Thanks!

*remark: it’s ok if result of vertical data without ColumnA ( ID01,NAME01,DEPT01,POSITION01,ID02 …)

[sarathi125 (Parthasarathi)]

Hi @Hyde,

You can do it in c# with the code available in this link, use invoke code activity from your workflow and try it.

How to transpose a datatable to get the second datatable in C#?

Check this post as well:

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…

[Hyde]

Thanks I will try to understand it.
But it’s very difficult for beginner.

[Anil_G (Anil Gorthi)]

@Hyde

Please try this

1. Build a datatable with two columns and of string type and name as newdt
2. Use for each row in datatable and loop original table dt
3. Use another for each activity inside with in argument as dt.Columns and change type argument yo column( in new activity type argument is auto configured)
4. Inside the second loop use add datarow activity with datatable as newdt and array row as {currentitem.ColumnName + currentRow(0).ToString,currentrow(currentitem.ColumnName).Tostring}
5. After loop write the data back to excel to view

Hope this helps

Cheers

[Nitya1 (Nitya Tomar)]

Hi @Hyde

Try this-

1. Use the “Read Range” activity to read the horizontal table from the Excel file.

• Specify the range of cells that contain the table.
• Store the output in a variable, let’s call it dtHorizontal, of type DataTable.

2. Create a new empty DataTable variable, let’s call it dtVertical, to store the vertical table.
3. Use a “For Each Row” activity to iterate through each row in dtHorizontal.
4. Within the loop, use a “For Each Column” activity to iterate through each column in dtHorizontal.
5. Inside the nested loop, create a new DataRow variable, let’s call it newRow, by using the dtVertical.NewRow() method.
6. Assign the values from the current row and column in dtHorizontal to the corresponding columns in newRow.
7. Use the dtVertical.Rows.Add(newRow) method to add the newRow to dtVertical.
8. After the loops, you will have the vertical table stored in the dtVertical variable.
9. To write the vertical table to a new Excel file, use the “Write Range” activity.

• Provide the dtVertical variable as the input DataTable.
• Specify the target range or sheet where you want to write the table in the Excel file.

Thanks!!

[Hyde]

Thanks for your help,
But my UiPath Studio Community don’t have activity “For Each Column”
Do I need to update or do something ?

Thanks !

[Hyde]

Thanks, I’ trying your solution.
but I stuck on ArrayRow (Code Error)

Can I give column name from original in excel file ?
Do you have advice me.

Thank you !

[Anil_G (Anil Gorthi)]

@Anil_G

{currentitem.ColumnName + currentRow(0).ToString,currentrow(currentitem.ColumnName).Tostring}

@Hyde

This should be the array row value

In place of zero you can give the id column name …other columns are automatic depending on which column it is cusing currentitem.columnname

And in second for loop please check if the type argument is datacolumn…if not please select it

Cheers