Excel Automation Help Required

Help Activities
, , ,
1
POL1 POL2 POD1 POD2 Dest1 Dest2
QINGDAO NINGBO VANCOUVER, BC PRINCE RUPERT,BC VANCOUVER, BC TORONTO, ON
QINGDAO NINGBO PRINCE RUPERT,BC VANCOUVER, BC PRINCE RUPERT,BC MONTREAL, QC
QINGDAO NINGBO HALIFAX, NS VANCOUVER, BC HALIFAX, NS CALGARY, AB

based on the above table my result should be look like the below. given output for First row only. Should i use For each row? but how i configure the process that once POL1-POD1-DEST1 processed then POL1-POD1-DEST2 should be next row in the Output?

I am new to UI path, how i can automate this task?

POL POD DEST
QINGDAO VANCOUVER, BC VANCOUVER, BC
QINGDAO PRINCE RUPERT,BC TORONTO, ON
2

@soumya.chatterjee Please explain little bit more.

3

Edited now.

4

@soumya.chatterjee so you want to get 3 columns from 6 columns

5

Hi @soumya.chatterjee ,

Could you also let us know on what basis or condition is the Output created ?

Why is HALIFAX, NS not shown in the output ?

6

that was example output for Row1 only, need for all 3 rows.

7

Yes but in a sync way, like after POL1-POD1-Dest1, i need POL1-POD1-Dest2 in next output row and same for the others rows as well.

8

@soumya.chatterjee use filter activity
go in output coulumns
and give 3 columns that you want you keep
image

Above you need to give input datatable and output datatable

9

Hi @soumya.chatterjee ,

Does this code fixes your issue, please check the xaml below.
I’ll suggest whenever you’re downloading the the data, just after that use the logic from the XAML below, to get your Input Data for the process.
So that it would be processed one after the other one.

ExcelSeparation.xaml (9.2 KB)
TestExcel.xlsx (10.8 KB)

10

Hi @soumya.chatterjee

Can you try this-

  1. Read the input data from the table into a DataTable variable using the “Read Range” activity. Make sure to specify the range or sheet name correctly.
  2. Create an empty DataTable variable to store the output data. You can use the “Build DataTable” activity to define the structure of the output DataTable with columns “POL”, “POD”, and “DEST”.
  3. Use a “For Each Row” activity to iterate through each row of the input DataTable.
  4. Inside the loop, extract the values of “POL1”, “POD1”, “Dest1” from the current row using the row index and column names. Add these values as a new row to the output DataTable.
  5. Extract the values of “POL2” and “Dest2” from the current row, and update the “DEST” column of the previously added row in the output DataTable with the value of “Dest2”.
  6. Repeat steps 4 and 5 for each row in the input DataTable.
  7. After the loop completes, you will have the desired output DataTable with the correct rows and columns.
  8. If needed, you can write the output DataTable to a CSV or Excel file using the “Write Range” activity.

Thanks!!

11

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