An essential skill to know, how to Import SQL Server table to Excel. Using it for pivot table analysis and reporting within Excel. Every developer must be acquainted with this. Importantly, most customers and business users are comfortable using Microsoft Excel for data analysis. Without any doubt, Excel provides an array of rich features with ease of use.
Generally, a non-technical person uses Excel for data analysis and reporting once they have the data. Although we already have reporting services, Power BI and Tableau tools in Market. However, users want to play with data in their backyard and way within Excel.
Once the data exported from SQL Server to Excel. Using a Pivot table, users can generate reports and analyze as per their requirements.
In the following example, I have already created RPT.SalesCityEmployeeReport table in SQL Server using the following query in WideWorldImportersDW database.
select C.Country, C.Region, C.City, S.Profit, S.[Tax Amount], S.[Total Including Tax], S.[Total Excluding Tax], E.Employee, E.[Is Salesperson] INTO RPT.SalesCityEmployeeReport FROM Fact.Sale as S LEFT JOIN Dimension.City as C ON S.[City Key] = C.[City Key] LEFT JOIN Dimension.Employee AS E ON E.[Employee Key] = S.[Salesperson Key] AND E.[Is Salesperson] = 1;
Let’s import the RPT.SalesCityEmployeeReport table’s data into Excel as a Pivot table. As a final output, I have generated the following Pivot table report Profit by City & Employee by just dragging appropriate fields.
Import SQL Server Table Data To Excel-based Pivot Table
1. In Excel, Select Data > From Other Sources > From SQL Server.
2.Under Data Connection Wizard, specify SQL Server Name and the credentials.
3.Select the database and the appropriate table as applicable. You can import multiple tables also.
4.Under Save Data Connection File and Finish, check the option “Always attempt to use this file to refresh data”. Click Finish.
If the table is updated with any data, no need to repeat this importing process. Simply use the Refresh Data option in the Pivot table to see the latest data.
5. On Import Data, select PivotTable Report.
Now you will see PivotTable Fields option on the right side. You can drag and drop columns to rows/values/columns/filters to generate reports as per your need. We have created a sample pivot based report to show Profit by city and employees belong to that city in Figure 1.
With this approach, users can generate reports as per their necessity. We do not have to create a customized report for every field, filters using any other reporting tools. This can save a lot of efforts and time in developing standalone reports and then exporting data to Excel.
Summary & Read More
In summary, we have learned how to import SQL Server table data into Excel. Using this data we can use Pivot table for analysis and reporting purpose. We can generate various report on the fly, and also refresh the latest data whenever needed without re-creating the connection again.
Read More and browse through more posts related to SQL Server on Tech-Recipes.
1. Introduction To DATETIME Functions In SQL Server
2. SQL Server – Error Solution – String or Binary Data would be Truncated
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Index Computed Column In SQL Server
5. How To Use Computed Column In SQL Server
