This article is made especially for those who regularly export large data volumes to Excel in order to analyze them or process some kind of regular report. Usually, we insert the data into Excel simply by copying it from some factory system. But data can also be inserted into excel in more suitable ways which will save your time. The most suitable one is PowerPivot or Power Query, but there are some other possibilities. If you know basics of SQL language, you can prepare SQL Query in Excel and create fully automated report.

SQL Query in Excel – Example

Task Assignment

The task is to connect into SQL Server SQL database and execute a sql query which contains sales in relation to months, products and product categories. After that, it is needed to create a connection to the database and prepare a simple dataset for the report.

Script:

SELECT
    [b].[CalendarYear],
    [b].[MonthNumberOfYear],
    [c].[EnglishProductName],
    [d].[EnglishProductSubcategoryName],
    SUM([a].[SalesAmount]) AS [SalesAmount]
FROM [AdventureWorksDW2014].[dbo].[FactInternetSales] [a]
       JOIN [AdventureWorksDW2014].[dbo].[DimDate] [b]
         ON [a].[OrderDateKey] = [b].[DateKey]
       JOIN [AdventureWorksDW2014].[dbo].[DimProduct] [c]
         ON [a].[ProductKey] = [c].[ProductKey]
       JOIN [AdventureWorksDW2014].[dbo].[DimProductSubcategory] [d]
         ON [c].[ProductSubcategoryKey] = [d].[ProductSubcategoryKey]
GROUP BY
    [b].[CalendarYear],
    [b].[MonthNumberOfYear],
    [c].[EnglishProductName],
    [d].[EnglishProductSubcategoryName];

Steplist

1 – Open excel and set up a sheet where the data will be connected to the database later on

2 – Lets go to card Data – from other sources – from SQL Server

3 – Write down name of the SQL Server instance where the data are stored. If it is needed to connect to the database, your Windows account should have appropriate user permissions (in case of Windows system verification). Database admin should provide you with these. Click Advanced options

4) Insert our script into the SQL statement section

5) Table will load into Excel after confirmation of following window as a result of SQL query or you can edit/transform data using power query if you want (Transform data button)

6) After confirmation, data has been successfully loaded into Excel sheet as result of our SQL script