Excel Validation - SQL or Linq - Uipath

Help Studio
, , , ,
1

Hi Guys,
Can anyone give some inputs how to do this in Uipath?

In a excel sheet, IF “Category” is of Type A
THEN “Resource” should not be “charged” more than once for the “same Date”

If charged, add the duplicate row to a separate excel .
Can we do this with Uipath activities or do we need queries to do it?
Thanks

2

Hi @sybasha,

Please provide sample excel sheet for better understanding.

4

Sample Fourm.xlsx (9.4 KB)

5

hi, please find the sample excel

6

Hi @sybasha,

If the number of rows and columns is small, UiPath activities

Uses 3 datatables and 1 dictionary

  • duplicates (1st)
  • datatable read from excel (2nd)
  • a copy of datatable (3rd) from (2nd)
  • a dictionary of int32 to store the row index

External For Each Row
Internal For Each uses List of index starting from index+1 to last-row-index

Steps:
0. Build the duplicates datatable (1st), headers only

  1. Excel application scope and read range to datatable (2nd)
  2. Make a copy of the datatable (3rd)
  3. External For Each Row on datatable (2nd)
  4. Get resource, category and date from datatable (2nd)
  5. Check that the datatable (2nd) row index is not in dictionary
  6. if row index in dictionary, increment index+1, Continue skip to next datarow which will jump back to step 3
  7. Internal For Each index+1 to last-row-index
  8. Get resource, category and date from copy datatable (3rd) using index+1 from IndexOf
  9. Compare all values of (2nd) versus (3rd), if duplicates condition met, Add Datarow to datatable (1st)
  10. If duplicates condition met, store the row index of (3rd) into dictionary
  11. Auto-return to beginning of internal for each loop
  12. increment index+1 (remove the first index in List of index)
  13. Auto-return to beginning of external for each row loop

14 final. remove any duplicates row on duplicates datatable (1st). Write range on duplicates datatable (1st)

If excel data is large, queries is preferred.

1 Like
7

I love the explanation, but the data is very large. I just uploaded small data.
This is just a part, i need to perform many other validations are excel as well. Looking if anyone can help with SQL queries on it