How to update a datatable via Uipath?

Help Studio
, , ,
1

Hi everyone,
I would like to update the data in a table only if some rows of it have undergone a change.
Of course, I also need to add new lines if they don’t exist.
Is there a direct way to do this with UiPath?

PCODE WP FACTORY_SN Release Date Drop QA First Date FTB Creation Date Totale Test Totale Test Passed Drop QA Bug Open FT Passed First Date FT Passed Last Date
P211765 DTWP00003299 CNI 2023-09-11 2023-08-03 2023-07-26 3 3 0 2023-08-03 2023-08-03

These are the fields present in the database.
Every day the bot downloads a new and perhaps updated extract of the data.
Existing data may be subject to modifications.
New lines that did not previously exist can be added.

With the same Pcode-Workpackage-Factory-Drop QA First Date
I would like the data in the table to be updated only if:
Release Date, Drop QA First Date, FTB Creation Date, Total Test, Total Test Passed,
were modified compared to the copy present in the DB.

Can anyone help me with this mission?
Thanks
Aaron

2

@AaronMark

better write with a stored proc and use it

or else

Loop through each key that you get…and see if it is present in db…if present use update else use insert

cheers

3

Hi Anil_G.
Thank you for your suggestion.
Unfortunately I don’t think I have the technical skills to do what you’re telling me. Or maybe I didn’t understand what you mean.
I currently have two tables on DB. One from yesterday’s draw, one with today’s draw.
In essence, the bot should only act on the lines that have undergone changes compared to yesterday.
When updating the table, I clearly have to add any new rows.
I would like to understand if there is a way to do these operations without creating many tables in the database.
The one from yesterday - The new one - The one with only the differences.
Or if you have any suggestions on the matter.
Unfortunately I’m a little weak on DB management.

4

Hi

First read those database tables in UiPath use DATABASE PACKAGE
Have a view on this on how to use this activity as u said u have the table in a db

Once u have got the two datatable u can use a Assign activity to get the matched or unmatched records using these two linq queries
To Get Matched Records

Out_Matched_Datatable = In_DataTable1.AsEnumerable().Where(function(row) In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

To Get Not Matched Records

Out_NonMatched_Datatable = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(function(r) r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Hope this helped

Cheers @AaronMark

5

Hi Palaniyappan,
I’m not clear about this Query which parts need to be “customized”.
That is, table names yes.
Ex: I run two queries on the two tables and get QueryDT1 (historical) and QueryDT2 (daily extraction)

QueryDT1.AsEnumerable().Where(function(row) Not QueryDT2.AsEnumerable().Select(function(r) r.Field(Of Int32)(QueryDT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of Int32)(QueryDT1_ColName_To_Match.ToString))).CopyToDataTable()

Where you write “QueryDT2_ColName_To_Match” I imagine it must indicate the column to do the match on.
Can there be more than one?
In fact, I should verify that at least 1, of a list of columns, has been modified.

or maybe QueryDT2_ColName_To_Match is a list of columns that I need to plot to a variable?