How to create an Excel connection to a SQL database

Microsoft Excel can be a powerful tool for evaluating SQL data. In Excel, a connection can be created for direct linking to a specific database that is filtered according to your requirements. This allows you to report SQL data, attach a spreadsheet in Excel, create a pivot table, and better work with your SQL data.

This connection must be validated by SQL login to ensure that 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 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
      
      

• Select the credentials you will use.
  • • The default option is “Use my credentials”. This will use the Windows login you used to connect to windows.
    • Use Alternate Credentials uses a different Windows login than your current login.
    • Select Database on the left side to use SQL database credentials.
    • Select Microsoft account on the left side to use Sign in to Microsoft 365
      
      

• Click Connect
• Select the database and table you want to use for the query
  • • You can use the search bar to search for a table
    • You can check the Select Multiple Items box to check out more than one table.
      
      

• You can now choose 1) how you want to load the data; Either load which places the data in 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 the Microsoft Power Query Editor and allows you to add fields, remove fields, change data types, etc.
• After clicking Load, the data is loaded into the Excel table.
  
  

Update Excel Connection to SQL Database

To update an Excel connection:

• Select the Data tab
• Select Update
  • • Update: The current query you are using will be updated.
    • Update All: All queries in the workbook will be updated.
      
      

Call Archer Point With the help of all your Microsoft Dynamics 365 Business Central and reporting needs. We are here to help!