Custom sort multiple column and delete rows without Excel

Help Studio
1

Good Day! I’m pretty new with UIPath and do have a showstopper as I can’t find the right path.
I do have an Environment with UiPath and Chrome. I do have an file where I have sort the culumns to get the latest entry like in Excel custom Sort.
invoiceNo ASC, last time stamp DESC, Receiving Date DESC. Goal is to have the latest entry of an invoice on top. Next step would be to delete all dublicate invoiceNo to have a unique table which can be used to type the invoiceNo into a Web page and update the current status (like new time stamp, invoice declined etc.
Unfortunately I do not have an Office package installed. It would be great if you could give me a hint how to solve this with UiPath activities.

2

You do not have to have Excel installed on your computer to use Excel activities. UiPath has got two different kinds of activity groups. One is the Excel Scope that requires Excel to be installed on your machine, and the other is the Excel Workbook that does not require you to have Excel installed.

If you look for Read Range activity, it shows two results. One within the Excel group and the other in the Workbook group. You will need to use the second one.

image

  • To build your process, use the Read Range Workbook activity to pull the data into a Data Table.
  • Once you got data pulled into a Data Table, you can use Data Table activities to manipulate your data the way you want it.
  • You can also add new columns to the Data Table and use them to record the status of the Invoice (declined, timestamp, etc.) as you process each record through your Web application.

image

  • After you are done with data manipulation (sorting, removing duplicate records, adding new columns, etc.) , you can loop through your Data Table and process the Invoice record through your web application and update the status to new columns you added to the Data Table.
  • Once the process is complete, you can use the Write Range Workbook activity to write the Data Table to a new sheet in your Excel.

Hope this helps.

3

as mentioned above

  • read range from the workbook can be used for reading the excel - but in some cases it can have side effects
  • sorting, deduplication … we can check also for a LINQ statement

May we ask you to share some sample data with us along with a clear description of the expected output. thanks

4

Dear Andy and Peter,

thank you for your help. I tried the steps from Andy but hanging with the sort mechanism as the ‘sort data table’ allows only one column. In my case I would need three combined in one step, to have the latest status, in the first case ‘Gezahlt’ with time stamp 01.02.2022 and receiving date 02.12.2021 on top to be able to delete the other two rows with a delete activity.
In this example row 47 should be kept and row 48 and 49 should be deleted (same with 51 and 53)

grafik
grafik1095×349 93.6 KB

Result should be a clean table with no duplicated invoice numbers (1) and the latest stamp date (2) and if there are two equal stamp dates then the third culumn receiving date (3) should be the latest.
Goal is to go through the table with a for each row, enter the invoice date in a web App and scrape changes in status and stamp date.

grafik

5

i will have later a closer look on it.

the group finding we can do with group by:

the sorting on multiple columns we can do with LINQ

6

Thank you Peter, will dive into linq, guess this is exactly what i need

7

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