Query Excel File Using SQL (OLEDB Object)

[Anonymous]

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!

[John__Carter]

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.

[JiriPospisil]

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

[NiveditaDamle]

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.

[BelenRodriguez]

¡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
------------------------------

[pitchaya.yamprai]

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