Convert Excel table <Horizontal to Vertical>?

Help Community
1

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

image

Thanks!

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

2

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.

Check this post as well:

1 Like
3

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

4

@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

1 Like
5

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

1 Like
6

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 !

7

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

image
image540×614 17.6 KB

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

Thank you !

8

@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

1 Like