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
- 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.
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.
-
Contact with assistance with all your and reporting needs. We're here to help!
As an alternative you could use a product called Skyvia to allow you to get the data from Excel to the SQL database.
a connection was successfully established but then the error occurred during the login process (Provider: SSL Provider, error 0 - the certificate chain was issued by an authority that is not trusted)
How can I make that other users can acces the SQL-views (read) with a global connection String.
Even those who have no access to the database itself, but still have the need to consult the xls-dataset I created
After refreshing excel am receiving error of ora-12170 tns timeout occurred oracle 11g please help
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.
This can't be done. Out of the box, it is not possible to write back to the SQL database from Excel.
Hi! How do I connect to a database using JDBC adapter using Excel?
how about from excel to database
Christopher, we will work on that soon!
That would be awesome! Because I'm currently loading from excel TO db line by line and it's killing me