topic RE: Query Excel File Using SQL (OLEDB Object) in Product Forum

Query Excel File Using SQL (OLEDB Object)

Anonymous — Thu, 31 Aug 2017 13:23:00 GMT

Have connected using OLEDB to an excel file with many worksheets. Is it possible to use INNER JOIN to combine these and output the result as a collection in blue prism? This isn't working: "SELECT * FROM [Report$] INNER JOIN [OOH$] ON [MPRN]=[MeterPointRef] WHERE [MPRN]=1236405" Cheers!

I'm not sure of the syntax

John__Carter — Thu, 31 Aug 2017 14:42:00 GMT

I'm not sure of the syntax but it's definitely possible, and I think you can even join sheets in separate files. Google is sure to have examples.

As with regular SQL, you need

JiriPospisil — Tue, 05 Sep 2017 15:34:00 GMT

As with regular SQL, you need to specify tables when joining them: ""SELECT * FROM [Report$] INNER JOIN [OOH$] ON [Report$].[MPRN]=[OOH$].[MeterPointRef] WHERE [Report$].[MPRN]=1236405

Hello, We are trying to fire…

NiveditaDamle — Thu, 21 Feb 2019 10:26:00 GMT

Hello, We are trying to fire Inner Join Query on 2 different excel sheets using OLEDB. We tried to do Inner Join like query mentioned above. But getting error. ""Pivot$"" is not a Valid Name. Here Pivot$ is second worksheet name. We just want to understand how many connections needs to be setup in this case. Currently we are setting up 2 different connections for each Worksheet.

RE: Query Excel File Using SQL (OLEDB Object)

BelenRodriguez — Tue, 22 Dec 2020 15:48:00 GMT

¡Hello!

You can put:

"SELECT * FROM [Report$] INNER JOIN [OOH$] ON Report$.MPRN = OOH$.MeterPointRef WHERE Report$.MPRN = '1236405'"

I did a lot of this querys,

My problem now is when I want to do more than 1 INNER JOIN. Anyone can did this type of query in BP?

------------------------------
Belen Rodriguez
------------------------------

RE: Query Excel File Using SQL (OLEDB Object)

pitchaya.yamprai — Mon, 28 Dec 2020 14:04:00 GMT

I tried with create 3 sheets in one Excel file xlsx. Then

The OLEDB connection string is: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="&[File]&";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Create these one connection to the file.

Query Join like this work for me.

SELECT S1.[a], S2.[bb], S3.[ccc] 
FROM ( 
    (  
        [Sheet1$] AS S1
        INNER JOIN [Sheet2$] AS S2  
        ON S1.[a] = S2.[aa] 
    )  
    INNER JOIN [Sheet3$] AS S3 
    ON S2.[aa] = S3.[aaa] 
)
WHERE S3.aaa = 3