How to Create an Excel Connection to a SQL database

Visit Website View Our Posts

Excel open on a laptop screen with a SQL server icon in blue overtop

Microsoft Excel can be a powerful tool to evaluate SQL data.  In Excel, a connection can be created to directly link to a particular database filtered according to your requirements. This allows you to report SQL data, attach a table of data into Excel, create a pivot table and have better manipulation of your SQL data. 

This connection must be validated by a SQL login to ensure the contents of your databases remain secure. The connection can be refreshed to update the content from the SQL database.

How to Create an Excel Connection

  • Open Microsoft Excel
  • Select the Data tab
    Select the data tab in Excel
  • Click on Get Data
      • Select “From Database”
  • Select From SQL Server database
  • Enter the SQL Server Name
      • Optionally, you can enter the database name here if you know it.
      • Otherwise, you will be able to select the database in a future step
        Selecting the SQL Server Database
  • Determine the credential you are going to use.
      • “Use my current credentials” is the default.  This will use your windows login that you used to connect to windows.
      • “Use Alternate Credentials” is using a different windows login than the current.
      • Select Database on the left-hand side to use SQL Database Credentials.
      • Select Microsoft Account on the left-hand side to use your Microsoft365 login
        selecting the SQL Account
  • Click Connect
  • Select the database and the table that you want to use for the query
      • You can use the search bar to search for a table
      • You can check the box for “Select Multiple Items” to pull in more than one table.
        Selecting Tables to import
  • Now you can choose 1) How you want to load the data; either Load which puts the data into an Excel table or “Load To” which allows you to load the data as a query instead of a table.  "Load To” is for more complex reporting. 2) You can “Transform Data”; this opens Microsoft Power Query Editor and allows you to add fields, remove fields, change data types, Etc.
  • After click on “Load” this loads the data into an Excel Table.
    SQL data that has been imported into Excel

How to Refresh an Excel Connection to a SQL Database

To refresh the Excel Connection:

  • Select the Data tab
  • Select Refresh
      • Refresh: Will refresh the current query that you are on.
      • Refresh All: Will refresh all queries in the Workbook.
        refresh SQL Data in Excel

Contact ArcherPoint with assistance with all your Microsoft Dynamics 365 Business Central and reporting needs. We're here to help!

 

4 thoughts on “How to Create an Excel Connection to a SQL database”

  1. Hi
    I have followed all of your steps but after giving the login credentials i receive the error 40 Could not open a connection to SQL Server.
    My database is an docker mysql image running on the host interface.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons