Let us assume, I have an Employee table in my SQL Server database. I want to connect to the database from Excel and retrieve (or import) data from the table. I can use VBA to import data in Excel. But, here I'll like to use "Power Query" to connect and import data from the table.
Note: I am assuming you have some knowledge in working with SQL Server database and you know how to create and query tables etc.
Follow these steps.
1) Open your Excel file.
2) Open Power Query editor: From the top menu (in the Ribbon) select the Data tab. (Microsoft 365)
Click Get Data, select From Database and click From SQL Server Database. It will open SQL Server Database dialog box.
Note: If you are using older version like 2010 or 2013, you'll have to download and install Power Query add-in into Excel.
Power Query in Excel 2016: Power Query in an inbuilt feature in 2016 and later versions.
3) In the "SQL Server Database" dialog box, enter the Server name and the Database name. Click Advanced options and enter the SQL Query to retrive Employee table details. Press the OK button.
👉 If you are note sure about the "Server" and "Database", then I would recommend opening SQL Server Management Studio and get the credentials.
4) Load or Transform data: After you have entered the Server, database and SQL statement correctly, it will connect and retrieve data from table and open a dialog box where it will give you various data transformation options like load, Transform etc.
Clicking the Load option will show the data (from Employee table) in your Excel worksheet.
Clicking the Transform Data option will open the "Power Query" editor, where you can not only see the data, but manipulate the data, add new data to SQL Server table etc. It has many other useful options that you can use to manipulate tables in SQL Server.
This is just the "tip of the ice berg".
There are plenty of features. Do you see the formula bar in the above image? You can modify the SQL statement to filter records like you do in SQL Server Management Studio. Simply change the statement and press the Enter key.
For example,
If there's any error in the SQL statement, it will display a detail message, even pin-pointing the exact cause of the error.
This tool reminds of a popular tool named Toad, that I used a few years back to communicate with databases like Oracle, SQL Server etc.
That's it. I am not going in detail into the various features that this tool offers. This article is about how to connect SQL Server database and import data from a table using "Power Query" in Excel.