Sql query on 2 different workbook

Hi ,
I am trying to put sql query on 2 workbook data table… do i need to make new connection or how to achieve this task?
Even i can put sql query on 2 tables with one connection if tables are in same workbook . Plz help
Thanks
Vaneet

if you want to connect on 2 file .xaml :
you can make new .xaml (connection.xaml) and you put the sql connect activity in that xaml.
then you can just use Invoke Workflow File activity -> invoke the connection.xaml in the destination .xaml file

if you want to connect on 2 workbook :
you can just use 1 sql connect activity, no need more sql connection (only if 2 workbook want to retrieve data from 1 same database, if 2 workbook want to retrieve data from 2 database then you need to make 2 connection).

if this can help you to understand, please mark my reply as solution.
with that you also help people as well :slight_smile:

Thanks wija for instant reply, I have already make connect but one connection is able to connect one workbook at a time…for another workbook i have to make another connection. but how would i integrate in sql query? two table i cant upload attachment as i am new here.
EG in one workbook name details i have (vendorid and amount ) in sheet1 as dt and in other wb as id and i have (vendorid) which is “dt1”.
I am required output of query as (“select vendorid, sum(amount)from dt.[sheet1$] where vendorid in(select vendorid from d t.[sheet1$]”.
And in both connection have have put path of workbook.
Thanks…

One more query is can i execute sql query on workbook datatables without creating connection?

every sql query need connection.

and as long as you work on only 1 database you only need 1 connection.

if you work on 2 databases you need 2 different connection.

and if your workbook 1 is in workbook1.xaml, workbook 2 is in workbook2.xaml and if these 2 workbooks just want to retrieve data from 1 same database…,
then you just need to make connection.xaml -> use invoke workflow file in workbook1.xaml -> use invoke workflow file in workbook2.xaml.

note:
direct the invoke workflow file path to the connection.xaml path

One of my friend was saying that in assign activity you can execute sql query…but do not know how…Can i have your email id so that i can send you my project to show you my problem?

you put your sql query not in assign but in execute query or execute non query activity.

execute query for “SELECT” query, while execute non query for “UPDATE, INSERT INTO, DELETE”

Hi @vaneet_kumar

instead of using SQL query you can read values into data table. then you can use Linq query to get the desired data based on your condition.

If InputData is your Datatable then ==> InputData .Select("[ColumnName] = ‘ColumnValue’")

this will return the records which are matched with the condition.

Regards,
Vijay Kumar C.

Thanks …I do not have separate workflow file in different xaml file…I have assign variable to filepath as a = “c:\wb.xlsx” and other is to b =“c:\wb1.xlsx”…after that i made 2 connections.for assigning filepath to both connection respectively. but when i am trying to execute query on both file path …i have no idea how to do that and refer both data table in one query.
Other approach is i use read range activity and create datatable from both workbook… Is there any other way to execute query on both data table?

Thanks Vijay… I have to use sql query to get this resolve…as it is interview question…

use execute query activity or execute non query activity to put your sql query.

execute query for “SELECT” query, while execute non query for “UPDATE, INSERT INTO, DELETE”

those activities output is a data table variable.

after you get the output data table variable, you can simply just doing your write range to workbook.

those activities is equals with read range activity.
(read range activity is reading workbook, while execute query activity or execute non query activity is reading from sql database. both output are data table variable)

On execute query we can give one connection name…which i have made is as follows
1st connectionstring as “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+a+"; Extended Properties=‘Excel 12.0 Xml;HDR=YES;IMEX=1’;"
2nd as “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+b+"; Extended Properties=‘Excel 12.0 Xml;HDR=YES;IMEX=1’;"

now while executing query i can give one connection name in “existing Connection”… Hence it will read only one wb dt while executing query…how can i integrate 2 connection in execute query.? in one xaml file sequence.

1 execute query is for 1 connection only :slight_smile:

if you have 2 database that make you need 2 connection…, then you also have to use 2 execute query.

Hmmmmm…seems typical…so no other option to read table of other workbook from one connection…except two different Xaml file?
Thanks…

Hmm to be honest i’m quite didn’t get what you mean.

U mean you want:
Connect to database -> read the tabel in database -> write in workbook1 -> read workbook1 -> write the report in workbook2 (?)

Can you tell me your flow steps like above?

sql from different sheet.xaml (7.4 KB)

I am giving you attachment of my file …i am trying to achieve sql query as (select vendorid, sum(amount) as sq from dt where vendorid in(select vendorid from dt1)
Thanks
However query that i have written in this file is not according to UIpath in which i am struggling.

I will check later

details.xlsx (7.7 KB)
VendorDetails.xlsx (8.2 KB)
Thanks no issue.