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 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.
Create an Excel Connection to a SQL database
To Create an Excel Connection:
- Open Microsoft Excel
- Select the Data tab
- Click From other sources
- Select From Data Connection Wizard
- Select Microsoft SQL Server
- Click Next
- Enter the SQL Server Name
- Select credentials to use
- Click Next
- Select the database and primary table you would like to use
- Click Next
- Enter File Name
- Enter Friendly Name
- Click Finish
- Enter cell to start the data connection
- Select how to display the data (Table, Pivot Table, Pivot Chart and Table)
- Click Ok
- Enter your credentials
- Click Ok
Refresh an Excel Connection to a SQL database
To refresh the Excel Connection:
- Select the Data tab
- Select Refresh All
Update Excel Connection(s) to a SQL database
Update the connection:
- Update an Excel Connection:
- Select the Data tab
- Select Connections
- Select the Connection
- Select the properties button
- Modify the settings required
- Select the Definition tab
- Modify the Connection string to connect to a different database
- Modify the Command Type to SQL for Queries or Table for a table
- Modify the Command text to enter a SQL query to select particular data
