none
Use of Excel Cells as parameters in a native SQL Query in Oracle DB RRS feed

  • Question

  • Hello !

    How do I use data in Excel cells as parameter for my query ...

    For example, I want to use data in A1 and B1 in the following query

    select * from TABLE1 where FIELD1 = 'Data in A1' and FIELD2 = 'DATA in B1'

    Thanks

    Frank

    Thursday, December 5, 2013 2:30 PM

Answers

  • This is certainly on a (long) list of things we'd like to do. Right now, the biggest blocker is probably around some security implications we need to work out; the feature is otherwise fairly simple to implement.
    Sunday, December 8, 2013 3:48 AM

All replies

  • The only way to get data out of the current Excel workbook is by putting it into a table and using Excel.CurrentWorkbook(). You could then manually build a query string using the values in that table, but this is going to be a resoundingly dissatisfying experience in that each new combination of parameters will prompt you again to approve the query. A better approach would be to use Power Query filters or joins to establish the relationship between the two. Equijoins can be gotten by building two separate queries with Power Query and then using the "Merge" function in the UI. More complicated relationships will require more M-language-level coding.

    Thursday, December 5, 2013 5:26 PM
  • thanks ... :)
    Thursday, December 5, 2013 9:28 PM
  • Please consider this another vote to have a less cumbersome means of parameterization which doesn't involve table merges....
    Saturday, December 7, 2013 7:28 PM
  • This is certainly on a (long) list of things we'd like to do. Right now, the biggest blocker is probably around some security implications we need to work out; the feature is otherwise fairly simple to implement.
    Sunday, December 8, 2013 3:48 AM
  • I also would like to pass parameters from Excel to a native SQL query. Has any progress been made on this? Can we expect this issue to be addressed in a future update? 
    Thursday, July 30, 2015 10:35 PM
  • Why not just put a block on commands like this:

    If "Drop Table" is not for a temporary table

    or "INSERT INTO" is not for a temporary table

    or "SELECT INTO" is not for a temporary table

    or "UPDATE" is not for a temporary table

    or etc

    then stop the query?

    The "Native Query" dialog in Excel 2016 is annoying, and I can't figure out how to turn it off.  All I'm doing is adding filters to my queries for others who have read permission only to be able to run.

    Wednesday, August 1, 2018 10:48 PM