MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

SQL Query in Excel 2007

with 43 comments

Over a year ago, I put out a blog page on how to query the contents of a table from an Oracle database through Excel. I meant to get back to provide much more about this. Specifically, I want to cover the XML mechanism used to accomplish the task. However, a blog reader augmented my page with a solution comment. I’m thrilled he did because it tells me to get a basic thing out rather than hang folks up halfway. My thanks go to Vernon.

Here’s a quick addendum to the original post with screen shots because folks tell me they’re very useful. It demonstrates how you write queries in Microsoft Excel against the Oracle database. While I plan a full tutorial on a different URL, this should help everybody in the meantime. This content is dependent on the generic mechanism, which I covered in this older post.

It starts with an alternative to Step #9 in the older blog page, and therefore, I’ve started the numbering as 9 there.

  1. The Import Data dialog asks you how and where you want to import it. When you don’t want the contents of a simple table, click the Properties button.


  1. Clicking the prior dialog’s Property button brings you to the Connection Properties dialog. The Usage tab is first for a reason but the Definition tab is where you need to go to enter a free form query. Click the Definition tab highlighted in yellow below.


  1. In this dialog, the most important boxes are the Command type (as you may guess, Table is the default value) and Command text boxes. The first thing to do, is click on the Command type multiple select widget and choose SQL.


Now, you can write any query that you’d like against the selected Oracle schema. You may reference any tables or views for which your user (schema) has SELECT permissions. You don’t need to limit yourself to a single table because ANSI SQL:89 (comma delimited tables), and ANSI SQL:92 (INNER JOIN, et cetera) are both supported. Don’t forget that Oracle uses a different concatenation method than Microsoft Access or SQL Server, you can find that here. After you’ve entered your query, click the OK button to proceed.


  1. When you clicked OK, you’ve instructed Microsoft Excel 2007 to change the connection, it raises this dialog box (click on it if you want to read it). You click Yes to continue and save your query.


  1. You’re back to where you began, more or less, this it the Import Data dialog. This is Step #9 but this time it’ll run your query not return a simple table content. Click OK to run the query. Don’t be surprised if you get a message saying no password was provided. It just means you’ll be prompted to enter one. You should never store that password because it’s stored in plain text inside an XML file.


You can find those data source files in this directory:

C:\Document and Settings\<some_user>\My Documents\My Data Sources

Written by maclochlainn

November 30th, 2009 at 11:52 pm

43 Responses to 'SQL Query in Excel 2007'

Subscribe to comments with RSS or TrackBack to 'SQL Query in Excel 2007'.

  1. Great post! Thanks for this useful tutorial. Have you checked out the Excel conversation on Facebook yet? If not, head to

    MSFT Office Outreach Team


    1 Dec 09 at 4:51 pm

  2. Hi,

    Your tips really helped me in my job. However, what if I want to have the WHERE clause parameterized?

    For example, instead of your item_id:

    item_id BETWEEN 1021 AND 1024

    I want it to be just like when we use PLSQL in TOAD or PLSQL Developer.

    item_id BETWEEN &ID1 AND &ID2

    Any idea? Thank you so much for the answer.


    19 Mar 10 at 2:53 am

  3. Great share man Thanks

    Sueann Kut

    28 Jul 10 at 6:12 am

  4. Great post. Thanks a million


    20 Aug 10 at 4:17 pm

  5. the answer is
    SELECT ….
    FROM ….
    WHERE item_id BETWEEN ? AND ?

    then click on Parameters..


    14 Jan 11 at 9:28 am

  6. Excellent doco! This was exactly what I needed. You rock!

    Unca Lar

    14 Apr 11 at 12:08 pm

  7. >4000 characters can’t be entered into command text, right? I’ve hit that wall. any way around it? thanks.


    14 Oct 11 at 6:46 am

  8. Rodrigo, If you hold the key to making the Parameters button active with an Oracle driver, please share it. It appears that the ODBC driver must support specified criteria to enable parameters, which aren’t qualified in the Microsoft Support page.

    My understanding is that you must first use Microsoft Query, which works with MySQL’s driver but not Oracle’s 11gR1 driver. It does work with Oracle’s 11gR2 driver. Once you define the parameters in Microsoft Query it enables the Parameters button and you save the query as a *.dqy file. Unless you want to unhide the AppData and Microsoft subdirectories, you’ll have to find these queries by opening a cmd.exe (Command-Line shell). They’re in the C:\Users\UserName\AppData\Roaming\Microsoft\Queries directory. If I get a chance, I’ll put together screen shots on that.

    However, this is only possible if you load the SQORA32.dll and SQRESUS.dll from the Oracle Data Access Components for Oracle Client You need to put them in the %ORACLE_HOME%\bin directory. After you sort through getting Parameters button working, you may encounter Microsoft Bug 692357, which they say isn’t reproducible. This appears to occur when the edited copy of the query isn’t supported with the syntax of Microsoft Query and it has been edited on the fly. You can manually save the query file to avoid the error (work around). I suspect that I’ll need to build a test case to see any action on the bug. Unfortunately, with my schedule it’ll have to wait.

    The preceding query supports the way bind variables work for Microsoft Excel but isn’t supported in the scope of Microsoft Query’s syntax (not supported by the parser). It’s tough force fitting things in Excel because query syntax follows more or less the limited set provided by Microsoft Access.


    15 Oct 11 at 5:42 pm

  9. Steve, It’s a guess on my part but that’s probably the case because a VARCHAR2 has a maximum size in SQL of 4,000 characters. However, there may be a newer driver that uses a CLOB. I’m looking for it too. If I find it, I’ll update the post.


    15 Oct 11 at 5:46 pm

  10. I can’t believe this is so easy to do, without VBA even. Just hard to find. I bet this would be utilized a lot more if people knew about it. Thanks for the info.


    24 Apr 12 at 5:57 am

  11. Excellent post great !!
    I have another question concerning security.
    In the Excel connection string are the sql-user and sql-password specified.
    Now I want that a end user can’t see these items and therefore can’t abuse it.
    How can I tackle this the best?


    8 May 12 at 4:56 am

  12. Create a view only schema, and only put the views in it that you want to allow the user to see.


    9 May 12 at 10:57 pm

  13. SELECT *



    13 May 12 at 7:39 pm

  14. The problem is that a NULL value in the column you’re comparing results against raises an error. You should be able to rewrite the WHERE clause to include an NULLIF function call like this:

    WHERE (NULLIF(column_lookup, expression) BETWEEN ? AND ?)


    14 May 12 at 12:29 am

  15. This was indeed helpful. Good work. Thanks for sharing.


    29 Aug 12 at 9:13 am

  16. Thanks for sharing! Saved me some fumbling with my old cutting and pasting technique that is too horrible to describe!

    Bill Jackson

    16 Sep 12 at 8:31 am

  17. I am trying to pass list of text value into Oracle query. For example, select subset of postal codes with selection list managed in Excel file.
    Between or Like would not work in this case.. I tried concatenating postal codes to create the string to pass as a parameter: ‘L6M3X5′,’L8W8K6’ but MS query ignores it. Anyone has any other ideas how to do it?


    23 Nov 12 at 4:58 pm

  18. Thanks for the nice post.

    I have the following use case. I built a query to connect to one Oracle DB on my laptop and then I built a pivot table and chart upon it. Now I need to use this workbook exactly when going to different clients without the need to create a new connections and build new sheets.I just want to refresh my sheets & charts at the client, that simple. So, Is there a simple way to achieve this?.


    Ahmed Abdel Fattah

    13 Apr 13 at 4:09 am

  19. Ahmed, You would like to have a publish and subscribe model? Unfortunately, the connection to the database is not portable like other links.

    While it is not ideal, you can use one worksheet to import the data and have another worksheet that references the data by simple direct links, like =Import!A1.


    13 Apr 13 at 9:04 am

  20. Is it possible to use values from cells as variables in “Where” section of sql-query?
    I should create report for different people, who use different filters. Actually all comes to two dates and string.


    17 Apr 13 at 5:15 am

  21. Daniel, Yes, but I’ve not written an example.


    17 Apr 13 at 8:40 am

  22. Awesome site! thanks..
    I have managed to create a query with fixed values however
    I tried using the MS query for a simple query into excel however for multiple values with a comma but doesn’t seem to work?
    any suggestions here??

    Lisa Pereira

    12 May 13 at 11:20 am

  23. Hi,

    It was really helpful.

    I want to use multiple queries to extract data in my excel sheet. Is it possible to have multiple queries display data in same excel sheet.


    23 May 13 at 10:40 am

  24. No, you need to use different sheets and then group the data in another. Treat them as source work sheets.


    23 May 13 at 11:23 am

  25. HI, Thanks for the response. I wanted to know if we can write a PL/SQL query also in this?

    In my reponse I am getting more than row of is working fine in the case when I am running the query through sql developer but while doing through excel I am getting an error.


    24 May 13 at 2:54 am

  26. Hi Guys,
    I know this is an awesome site, but can someone please provide an working example of code for multiple query parameters? I am finding it difficult to get this.
    I have a command string with

    SELECT COMP_ID, DELIVERY_DATE,  BETWEEN :"Delivery Date Parameter 1" AND :"Delivery Date Parameter 2" ) AND ( COMP_ID = :"Dlv Comp Id Parameter 1" )
    I am trying TO query multiple COMP_ID i.e(1234,2345,4526)

    how do I do this>?

    Lisa Pereira

    11 Jun 13 at 10:35 am

  27. I have a couple of questions –
    a) how to plug an excel cell value in the SQL statements WHERE clause? (I’m using a DB2 connection and ‘?’ does not work)
    b) how to get rid of the column header that query returns?

    Please help.

    this post is great. Thanks maclochlainn!


    19 Jul 13 at 12:23 pm

  28. Every thing works fine till step 6 and the test connection is successful, however the excel never come back it keeps processing something for every and it has “Not responding”. how long this should normally take to move from step 6 to 7


    8 Aug 13 at 11:00 am

  29. Mariam, only a few seconds. Can you enter the same credentials as arguments to sqlplus and connect to the database?


    8 Aug 13 at 9:16 pm

  30. Thanks maclochlainn, it actually took couple of minutes, you must have patiency and faith 🙂
    but it worked at the end

    Now, i’m trying to add to add the Query in the “Command Text” box but hte paramter is dimmed! i can’t use it


    23 Aug 13 at 11:08 am

  31. It is very help full post.
    But can you tell me how to select multiple table in the wizard and how to write a query using multiple tables(like join)?


    18 Sep 13 at 1:51 am

  32. Veer,

    There’s no SQL editor but you can write the script in one, and then copy it into the command text window. It’s in the post above, but here’s the image again.


    18 Sep 13 at 10:49 pm

  33. […] SQL Query using Excel […]

  34. I write a SQL query the first time and execute the query.

    After going over the result set, If I want to edit the query, how do I perform the edit?


    22 Jul 14 at 2:15 pm

  35. RK, You have to use the same navigation that let you put it in, and then to re-write it. I haven’t found a way to use VBA to edit it.


    22 Jul 14 at 10:00 pm

  36. First, a huge thank-you. This is exactly what I needed for drill through from an OLAP tool to Oracle relational tables.

    Also, I found a way to parameterize my queries in VBA. I start by constructing the SQL as a text string and then assign that to the query string to the .CommandText property of the query object. Then it’s just a question of calling .Refresh.

    I’ve pasted an example of the code below. Most of it is just about building up the query string. The code to update and refresh the query object is at the end. Hope that helps.

    Many thanks again to Maclochlainn! Your post was huge!!!!

    Sub drillToAP(ByVal headerID As Long, lineNbr As Long)
    'Drill through to AP transactions
    Dim sqlStrAP As String
    Dim qtAP As Variant
      sqlStrAP = "select ap.invoice_id, ap.vendor_id, v.vendor_name, ap.invoice_num, ida.amount, ida.total_dist_amount, " &amp; _
               "ap.payment_currency_code,ap.invoice_date, ap.gl_date, ap.description as hdr_desc, ida.description as line_desc, ap.source, " &amp; _
               "ida.invoice_distribution_id , ida.created_by, u.user_name, ap.org_id, ap.payment_method_code " &amp; _
               "from apps.ap_invoices_all ap inner join apps.ap_invoice_distributions_all ida " &amp; _
               "on ap.invoice_id = ida.invoice_id " &amp; _
               "inner join apps.xla_distribution_links xdl " &amp; _
               "on xdl.source_distribution_id_num_1 = ida.invoice_distribution_id " &amp; _
               "inner join apps.xla_ae_lines xal " &amp; _
               "on xal.ae_header_id = xdl.ae_header_id and xal.ae_line_num = xdl.ae_line_num " &amp; _
               "inner join apps.gl_je_lines gll " &amp; _
               "on gll.gl_sl_link_id = xal.gl_sl_link_id " &amp; _
               "inner join apps.ap_suppliers v " &amp; _
               "on v.vendor_id = ap.vendor_id " &amp; _
               "inner join apps.fnd_user u " &amp; _
               "on u.user_id = ida.created_by " &amp; _
               "where gll.je_header_id = " &amp; headerID &amp; " and gll.je_line_num = " &amp; lineNbr
       Set qtAP = Selection.ListObject.QueryTable
       With qtAP
         .CommandType = xlCmdSql
         .CommandText = sqlStrAP
       End With
    End Sub 'drillToAP

    Rich Wallach

    8 Aug 14 at 3:43 pm

  37. Rich, Thanks for the comment and example.


    13 Aug 14 at 9:45 am

  38. First of all Thank you for this informative article.
    But I don’t understand the item i mentioned in th equery.

    I am able to retrieve data from table with few rows, i.e using the default CommandType as Table.
    But I need to query a very large table having huge volume of data.

    I am not sure how to write the select statement here.
    Please let me know what is “i” in the screenshot which you have provided.

    Query used:

    SELECT ValueA, ValueB 
    FROM   Table1 
    WHERE  Value3='ABC'
    AND    ValueDate BETWEEN TO_DATE('11/12/2014:10:00:00', 'MM/DD/YYYY:HH24:MI:SS')
                     AND     TO_DATE('11/12/2014:10:40:00', 'MM/DD/YYYY:HH24:MI:SS') 
    ORDER BY Value3, ValueDate DESC;


    12 Nov 14 at 4:30 am

  39. Laks, Are you referring to the i that is the table alias following the item table?


    12 Nov 14 at 10:17 pm

  40. Hello, I would like to get number of rows from oracle schema and update in MS Excel sheet.

    column1                      column2
    TABLE NAME                 ROW COUNT
    EMP                              100
    DEPT                              10



    30 Jan 15 at 3:32 pm

  41. You do that by writing an object table view, which you can read about in this earlier post. Then, you cut and paste the query into Excel following the instructions in this prior post. You would use dynamic SQL to generate statements against a list of tables returned from a query to the user_tables view.


    2 Feb 15 at 1:31 am

  42. With MS SQL Server, something like the following would work fine when importing data via ODBC from MS SQL Server:

    SELECT * 
    INTO #temp_table
    FROM my_table;
    SELECT *
    FROM #temp_table;

    However, this does not work via ODBC from AWS Redshift, it only seems to support a single query block, does not even recognize the SET NOCOUNT ON part.

    Any suggestions?


    20 Oct 16 at 6:10 pm

  43. Nisheeth, My understanding is it only supports a single query block. You need to write it in VBA to do more complex things.


    22 Oct 16 at 9:21 am

Leave a Reply