Excel Transpose


can anyone please help achieve this using uipath or sql or excel?

I want the first table to be transformed into second table.


@pranjali_ramteke Will the Excel be in that format always or will it differ ?

@supermanPunch it will always be in that same format

@pranjali_ramteke Can you provide the Input File ? I can work on it, and try to send you the Output

  1. Read the first table to a datatable.
  2. For Each Row create the following variables:
  • str_EMP = row.item(0).tostring
  • str_EMP_NUMBER = row.item(1).tostring
  • str_DATE1 = DataTable.Columns(2).ColumnName
  • str_HOURS1 = row.item(2).tostring
  • str_DATE2 = DataTable.Columns(3).ColumnName
  • str_HOURS2 = row.item(3).tostring
  • str_DATE3 = DataTable.Columns(4).ColumnName
  • str_HOURS3 = row.item(4).tostring
  1. Then create three separate arrays for the row data to go into the target datatable:

arr_RowData1 = {str_EMP, str_EMPNUMBER, str_DATE1, str_HOURS1}
arr_RowData2 = {str_EMP, str_EMPNUMBER, str_DATE2, str_HOURS2}
arr_RowData3 = {str_EMP, str_EMPNUMBER, str_DATE3, str_HOURS3}

  1. Use the Add Data Row activity three times to add each of these arrays separately to your target datatable.

  2. Next Row in your For Each Row loop.

@ronanpeter thanks will try it out!

Try this
TestCase.xlsx (8.7 KB) NewCase.xaml (13.3 KB)