Support Portal

for ProcessRobot and WinAutomation

Start a new topic
Answered

JOIN in SQL select from Excel

Let's say, I have Excel file with the following data:


  Sheet A

Key1,Data1
1,Andy
2,Bob
3,Claire

  Sheet B

Key2,Data2
2,Red
3,Blue
4,Green

 

Is it possible to get the following joined data table using "Execute SQL Statement"?

Key1,Data1,Key2,Data2
1,Andy,,
2,Bob,2,Red
3,Claire,3,Blue
,,4,Green


I've tried simple SQL join:

select a.Key1
     , a.Data1
     , b.Key2
     , b.Data2
  from [A$] a full join [B$] b on a.Key1 = b.Key2


But the following error raises: 

System.Data.OleDb.OleDbException (0x80004005): Сбой IErrorInfo.GetDescription с E_FAIL(0x80004005).
   в System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   в System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   в System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   в System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   в System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   в System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   в System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   в System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   в WinAutomation.Actions.Runtime.DatabaseActions.ExecuteSQLStatement(Variant connectionString, Variant sqlConnectionVariable, Variant sqlCommand, Variant& result, Int32 timeout, Int32 getConnection)

  


Best Answer

UPDATE


Found out that FULL JOIN is not supported, but LEFT JOIN and UNION works fine:


 

select iif(a.Key1 is null, b.Key2, a.Key1) as PKey
     , a.Key1, a.Data1, b.Key2, b.Data2
  from [A$] a left join [B$] b on a.Key1 = b.Key2
 union 
select iif(a.Key1 is null, b.Key2, a.Key1) as PKey
     , a.Key1, a.Data1, b.Key2, b.Data2
  from [B$] b left join [A$] a on a.Key1 = b.Key2
 order by 1

 


Answer

UPDATE


Found out that FULL JOIN is not supported, but LEFT JOIN and UNION works fine:


 

select iif(a.Key1 is null, b.Key2, a.Key1) as PKey
     , a.Key1, a.Data1, b.Key2, b.Data2
  from [A$] a left join [B$] b on a.Key1 = b.Key2
 union 
select iif(a.Key1 is null, b.Key2, a.Key1) as PKey
     , a.Key1, a.Data1, b.Key2, b.Data2
  from [B$] b left join [A$] a on a.Key1 = b.Key2
 order by 1

 

More info on SQL & Excel you can find here

Login or Signup to post a comment