tProccess from Excel -> datatable -> SQL database

Help Activities
, , ,
1

Being new to UiPath I am struggling a bit with how to update corporate SQL server data from Excel daily file. Now I already have the database connection established, the excel transposed to a datatable. Now I just need to learn how to for each row in data table find matching patient account in SQL table and if column “BEFORE” matches for example last name in sql update from colum “AFTER” in data table. the patient may have 5 rows one being for address change, city, state, etc.
Any help would be great!

2

Hi,

You can get specific DataRow from DataTable by For Each Row. Then check the particular data you want to check by simply go to DataRow index.
For example
dtPatient = data table of patient captured from Excel
dtPatient.Row(0).item(1).tostring .

Hope it helps.

3

Thank you for your response.
What I have done is transform the Excel to a datatable which works, then I call the connection to SQL which works. But I am not sure how to for example match the first row in the new datatable to an existing record in the SQL database and update SQL if datatable has a different value for example the datatable looks like this
OWNID FIELD CHANGED LOCATION ID1 ID2 ID3 BEFORE AFTER
12345 CITY SUNRISE 9999999 MIAMI DALLAS
12345 STATE SUNRISE 9999999 FL TX

So I need a for each to loop through find the same record in SQL Database probably using OWNID and ID1 (patient number) and update record to AFTER field

Thank you

4

Hi,

I think you need to ask UIPath support. In my knowledge, SQL integration requires extension to connect with UIPath. This will capture SQL syntaxes as strings. However, I never use this.

Hope you will get the answer from UIPath.

5

hi you can use execute query activity to fetch the record from your database and use execute non query to update it use assign activity to store you excel value which you want to match in one variable like row(“field”) and perform execute non query a

6

Thank you for you comments. the current way I went was to go Excel to DT, the just get the field changed ie (Phone) and the after value. I am putting everything up in the orchestrator queue, then another project to get the item and using switch for each Field Change case … but it only seems to work for the first item successfully then loops that same item over and over