How to Create an Excel Connection to a SQL database

Visit Website View Our Posts

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.

To Create an Excel Connection:

  • Open Microsoft Excel
  • Select the Data tab

 

  • 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

 

  • 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

 

  • 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.

 

  • 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.

 

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.

 

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
150