Forum Discussion

Dave7's avatar
Dave7
Copper Contributor
Oct 27, 2020

Running SQL Queries in Excel

I've used ODBC queries in Excel before but it was always with stored procedures on the server. Now I'd like to be able to define freeform SQL queries in Excel that pull data from a MS SQL Server. I have been playing around for 30 mins or so and Googling how to do it but can't seem to find anything. How do I build and debug SQL queries in Excel?

 

Thanks!

BI & Data Analysis
excel
  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Oct 28, 2020

    Hi Dave7 

     

    The best way to pull data from a SQL Server is to use Power Query in Excel, have you come across that yet?

     

    • Dave7's avatar
      Dave7
      Copper Contributor
      Oct 28, 2020

      Thanks, Wyn Hopkins. I should have said that yes, I got into the Power Query but couldn't figure out how to use it to write plain SQL. The Advanced Editor it has seems to use a different language (PowerQuery, apparently). Is it just not possible to write plain SQL? If that's the case, I'm sure I can figure out PowerQuery though if you have a good primer I'm all ears. 

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Oct 30, 2020

        Hi Dave7 

         

        When connecting to a SQL database you do have the option of writing plain SQL here...

         

        If you use the Power Query interface to do transformations then if that transformation can be converted into SQL it will be done automatically in the background...  You can often (but not always) see this happening by Right Clicking on a step and viewing the Native Query:

         

         

        Note:

        You may well get better performance by using the Power Query steps and allowing it to convert and "fold" much of the query back to the SQL Server.

         

        Writing your own SQL query from scratch breaks "query folding" and the data processing will be carried out on your computer.

         

        e.g. Filtering just for this year's records - if done via applying a filter in Power Query will push that filtering back to be performed quicker by the Server.

         

         

Resources