In this informative article, we will explore how to create an Excel connection to MS SQL database and load data from SQL Server to Microsoft Excel.
Microsoft Excel is a powerful tool to evaluate and analyze the data in various ways. Excel provides features to connect to various databases; for example - MS SQL Server database, Access database, etc. Using these database connections, you can import external data into Excel using SQL query or statements. You can then sort it, analyze it, and perform any calculation that you might need.
This database connection must be validated by a SQL login to ensure that the content of your databases remains secure and safe.
The main advantage of these database connections is - you update or refresh the Microsoft Excel data directly from the database on a real-time basis using the Excel refresh command.
How to create an Excel Connection to SQL database
To create an Excel connection, follow the below-mentioned steps.
Step 1
Open Microsoft Excel file and go to the Data tab on the Excel Ribbon (Under menu bar).
Step 2
Click "From other sources" icon in the "Get External Data" section and select "From SQL Server" on the dropdown menu.
Step 3
After the selection of "From SQL Server", the Data Connection Wizard window opens.
Enter the server name and click on "Use the following User Name and Password" radio button under the Log on credentials and enter the database username and password in appropriate boxes. Then, click the "Next" button.
- Server name - Server name box must contain the name of the computer where SQL Server is installed.
- User name - your SQL Server user name. Required only for SQL Server authentication.
- Password - your SQL Server password. Required only for SQL Server authentication.
Step 4
After clicking on the "Next >" button, a new popup window will open. Select the required database from the “select the database that contains the data you want” dropdown list. After selection of database, a list of tables appears, as in the below-given screenshot. Then, select the required table (which you want to load data) and click on the "Next>" button.
Database - The name of the SQL database to connect to Microsoft Excel.
Step 5
Click on the "Finish" button to load data from SQL Server Database to Microsoft Excel file.
Refresh/Update an Excel Connection to a SQL database
To refresh the Excel Connection follow the below-given steps.
- On your worksheet, click any cell that contains your SQL Server data.
- On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click "Refresh".
Note
Clicking the "Refresh All" button in the Connections group on the Data tab will instantly update the information from all data sources connected to the excel sheet.
Change the SQL query statement using external data properties
- Click any cell that contains your SQL Server data.
- In the Connections group, click the arrow next to Refresh All, and click Connection Properties.
Then Click on connection properties icon (marked in red) to open connection property window and go on the Definition tab,
Modify the Connection string to connect to a different database using a connection string.
Modify the Command Type to SQL for Queries or Table for a table. There are three options (Default, SQL and table).
Modify the Command text to enter a SQL query to select or load particular data.
Click the “OK” button to close the Connection Properties window. Edit other data in the spreadsheet and save the workbook as needed.
That is it for this article. I hope you have learned something new from this article. Please comment below for your feedback and queries.