In today’s data-driven world, SQL Server stands as the go-to database repository for organizations, housing their vast business data. Simultaneously, Excel remains the widely adopted versatile data analysis spreadsheet application across various departments and organizational levels.
However, getting data between these two apps can take time and effort. So, we’ll show you how to seamlessly import data from SQL Server to Excel using Power Query.
What is PowerQuery?
Microsoft Power Query is a data cleansing and transformation engine available in Microsoft Excel, Power BI, and Azure Data Factory. Power Query works in a similar way in all of these applications.
With Power Query, you can load data into your app from multiple sources and clean it up using M code or its built-in transformation functions. These functions include removing columns, changing a data type, combining multiple data sets in Microsoft Excel, or performing sentiment analysis on your data.
Although Power Query is an interesting tool, new users should make sure they are comfortable with Excel before starting to use Power Query. If you’re not sure of your Excel skills, check out these free training courses and sites to become an Excel master.
Exploring SQL Server Management Studio (SSMS)
SQL Server Management Studio, or SSMS as it’s called, is a SQL Server platform. It is called a platform because it comprises several components, such as the database engine, analytics services, integration services, and reporting services. With these services, you can have a go-to platform for all kinds of SQL-related work, from creating a table and writing queries to administrative and troubleshooting activities.
Today, we’ll explore its database engine and use a simple data table in the database.
Import SQL data directly into Excel using Power Query
Power Query allows you to import data from almost anywhere. It is one of the ten Excel add-ins that allows you to process, analyze and visualize data like a professional.
To import SSMS data, follow the steps below.
- Open SSMS and connect to your database engine.
- Choose a table you want to extract from SSMS to Excel using the drop down menu.
- We will use the ‘dbo.creditcard’ table for this example.
- Open Excel and go to Data.
- Under Data, select Get Data > From Database > Select From the SQL server database.
- Enter the name of your server and database. You can also write an SQL statement to get exactly what you want. This will avoid a lot of data transformation in Power Query.
You can get your server and database name by right clicking on the SSMS table and selecting properties.
- Click OK.
- You can use your Windows, database, or Microsoft account to authenticate. I will use my current credentials in Windows.
- Click Connect.
- Power Query Navigator opens and displays all the tables available in that database.
- Select the SQL table you want to import.
- You can select any Burden either transform data.
- Burden close power query and display the data in excel. It would be better if you only did this when your data is clean and therefore doesn’t need any transformation.
- transform data, on the other hand, ingests the data into Power Query. This allows you to perform data transformation techniques on your data.
always choose transform data. It’s a good practice to look at your data in Power Query before loading it into Excel. For example, you may need to change a data type before loading it. Inspecting your data before uploading it can save you time and effort.
- Select transform data. You have successfully imported your data into Power Query. As a result, you can perform data transformation techniques on your data.
- Once you are done with your data transformation, select load and close.
Do more with Power Query
With the Get Data from Excel functions in Power Query, you can import data from multiple sources, not just SQL servers. For example, you can pull data from Microsoft Dynamic, Sharepoint, Salesforce, Azure, databases like Oracle, JSON, the web, and social media platforms.
Plus, no matter how dirty and messy your data is, Power Query is there for all your data cleaning and transformation needs.
