Perform Full Outer Join on Excel Sheets

(0) Share

Report

Posted on by Ghiridhar_Iyer

Hello everyone,

I am trying to perform a full outer join on two Excel Sheets using SQL Connection in Power Automate Desktop.

SELECT * FROM
[Sheet1$] FULL OUTER JOIN [Sheet2$]
ON ( [Sheet1$].[Col1] = [Sheet2$].[col1] AND [Sheet1$].[Col2] = [Sheet2$].[col2] )

But it throws an error: IErrorInfo.GetDescription failed with E_FAIL(0x80004005)

I enclosed the column names within brackets.

Can anyone guide me what is the issue?

Thanks

Ghiridhar

Categories:

Power Automate Desktop

I have the same question (0)

All responses (9)

Answers (0)

Nived_Nambiar 18,129 Super User 2025 Season 2 on at

Like (0)

Report

Hi @Ghiridhar_Iyer

Does error description contains any additional details which would help ?

Thanks & Regards,
Nived N 🚀

LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel

🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌

Was this reply helpful? Yes No
Ghiridhar_Iyer 17 on at

Like (0)

Report
I have around 10 columns in sheet 1 and 3 columns in sheet 2. I want to do a full outer join between column 2 and 3 of both sheets.

This is the error stack I got:
Correlation Id: f8a32552-5a49-49b4-879b-f6adb974c1e6 IErrorInfo.GetDescription failed with E_FAIL(0x80004005).: Microsoft.PowerPlatform.PowerAutomate.Desktop.Actions.SDK.ActionException: Error in SQL statement IErrorInfo.GetDescription failed with E_FAIL(0x80004005). ---> System.Data.OleDb.OleDbException: IErrorInfo.GetDescription failed with E_FAIL(0x80004005). at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.DatabaseActions.ExecuteSQLStatement(Variant connectionString, Variant sqlConnectionVariable, Variant sqlCommand, Variant& result, Int32 timeout, Int32 getConnection) --- End of inner exception stack trace --- at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.ExecuteSqlStatement.Execute(ActionContext context) at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

Thanks
Ghiridhar

Was this reply helpful? Yes No
UshaJyothiKasibhotla 203 Moderator on at

Like (0)

Report

Select * from column name you need to write
Please find below for reference
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

Hope this helps
Usha

Was this reply helpful? Yes No
UshaJyothiKasibhotla 203 Moderator on at

Like (0)

Report

Try to write column names you required
Instead of *

Select empid, ename, deptid, deptname
from employee full outer join department
on employee.empdept = department.deptname;
Please try this

Hope this helps
Usha

Was this reply helpful? Yes No
Ghiridhar_Iyer 17 on at

Like (0)

Report

Sorry for the delay in the response. This is the query I am using. In SQL Server I used the same dataset and query and it works. I had to update the column values by removing circular Brackets. Don't know is that affecting Power AUtomate as well. Will try and see.

Was this reply helpful? Yes No
Ghiridhar_Iyer 17 on at

Like (0)

Report

I tried in SQL Server and it is working. But in Power Automate Desktop it is giving this error. I am not sure what the issue is. There is no explanation for the exception.

Was this reply helpful? Yes No
Ghiridhar_Iyer 17 on at

Like (0)

Report

Hey Nived,

I am trying to join two Excel Sheets based on two columns. I ran the query in SQL Server on the same Data and it works but gives an error in PAD. Is there an example of Inner and Outer (all types) in PAD? I believe the query is the same as in SQL but unaware of why there is an error.

Thanks
Ghiridhar

Was this reply helpful? Yes No
yoko2020 495 on at

Like (0)

Report
FULL OUTER JOIN is not directly supported in standard SQL for Excel

Try this.

SELECT * FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].[Col1] = [Sheet2$].[col1] AND [Sheet1$].[Col2] = [Sheet2$].[col2] UNION SELECT * FROM [Sheet1$] RIGHT JOIN [Sheet2$] ON [Sheet1$].[Col1] = [Sheet2$].[col1] AND [Sheet1$].[Col2] = [Sheet2$].[col2];

Was this reply helpful? Yes No
Ghiridhar_Iyer 17 on at

Like (0)

Report
Hey @yoko2020 ,
Thanks for your reply. I have a new error which I consider as a step closer to the solution 😁.

My query was:
SELECT [Sheet1$].[Col1], [Sheet1$].[Col2], [Sheet1$].[Col3], [Sheet1$].[Col4], [Sheet2$].[Col1], [Sheet2$].[Col2], [Sheet2$].[Col3] FROM [Sheet1$] LEFT JOIN [Sheet2$] ON ( [Sheet1$].[Col4] = [Sheet2$].[Col3] AND [Sheet1$].[Col3] = [Sheet2$].[Col2] ) -- Brackets for ON clause SELECT [Sheet1$].[Col1], [Sheet1$].[Col2], [Sheet1$].[Col3], [Sheet1$].[Col4], [Sheet2$].[Col1], [Sheet2$].[Col2], [Sheet2$].[Col3] FROM [Sheet1$] LEFT JOIN [Sheet2$] ON [Sheet1$].[Col4] = [Sheet2$].[Col3] AND [Sheet1$].[Col3] = [Sheet2$].[Col2] --Without Brackets for ON Clause SELECT * FROM [Sheet1$] LEFT JOIN [Sheet2$] ON ( [Sheet1$].[Col4] = [Sheet2$].[Col3] AND [Sheet1$].[Col3] = [Sheet2$].[Col2] ) -- Select *

Now the error which I get is:
No value given for one or more required parameters.

Stack Trace as below:
Correlation Id: b27beb00-d575-4c08-8864-a4a26e58c98c No value given for one or more required parameters.: Microsoft.PowerPlatform.PowerAutomate.Desktop.Actions.SDK.ActionException: Error in SQL statement No value given for one or more required parameters. ---> System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.DatabaseActions.ExecuteSQLStatement(Variant connectionString, Variant sqlConnectionVariable, Variant sqlCommand, Variant& result, Int32 timeout, Int32 getConnection) --- End of inner exception stack trace --- at Microsoft.Flow.RPA.Desktop.Modules.Database.Actions.ExecuteSqlStatement.Execute(ActionContext context) at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

Seems that one of the column Names of my sheet is a keyword in PAD. I have enclosed it in Brackets. Hope that is not the issue here.

Regards
Ghiridhar

Was this reply helpful? Yes No