Import Data from SQL Server into Excel

In Excel document, you can import data from SQL Server using Microsoft Query tool. Users import database table data, or dataset returned after execution of a SQL query or stored procedure easily. In this tutorial, I will show how to import data from SQL Server into Excel sheet step be step. The tutorial first creates sample data in a SQL Server database then in Excel using Data tools creates a connection to the target database as data source for import process. Then using Microsoft Query tool, Excel users can import and display contents of a SQL database table or display the outcome of a SQL query or a SQL statement like execution of a stored procedure.

Here Excel users can also watch a video on how to import SQL data in Excel with samples created in this tutorial.

Create Sample Data in SQL Server to Import from Excel

Let's create sample data in SQL Server in our tutorial to import into Microsoft Excel document from SQL Server.
First, launch SQL Server Management Studio (SSMS) and then connect to target SQL Server instance as I did by connecting to localhost (default instance of SQL Server running on local computer)

You can execute following SQL script commands one by one to create a sample database, sample table and populate the sample database table with SQL data.
SQL developers can realize that I've also created a SQL stored procedure to import SQL stored procedure data into Excel document in the following steps in this tutorial.


create database kodyaz

use kodyaz



create table SQLData2Excel (

 id int identity(1,1),

 SQLServerDataTools varchar(255)

)



insert into SQLData2Excel select 'SQL Server Import and Export Wizard'

insert into SQLData2Excel select 'Import and Export using SQL Server BCP Utility'

insert into SQLData2Excel select 'SQL Server Integration Services (SSIS) for Data Import/Export'

insert into SQLData2Excel select 'Office Data Connection file for MS Office to SQL Server Connection'

insert into SQLData2Excel select 'Import Data using Microsoft Query Wizard'

insert into SQLData2Excel select 'Third Party Tools from SQL Vendors'



create procedure ExportData2Excel

as

select * from SQLData2Excel

go



exec ExportData2Excel

Import SQL Server data to Excel

After our sample data is prepared, now we are ready to open Excel document to import data from SQL Server inside one of the Excel sheets.

How to Import SQL Server Table to Excel

As an Excel user, if you want to import all table data from a SQL Server database table or filter on the same table data using column values of the same table, you can apply the method described in this section step by step. On important task fullfilled in this section is the definition of the data source. For every task related with importing data in Excel from SQL Server requires the data source definition step which is illustrated in this part of the tutorial.

Let's start. On the Excel document, from top menu choose:
Data > From Other Sources > From Microsoft SQL

import data into Excel from other sources using Microsoft Query

When "Choose data source" dialog screen is displayed you can select "New data source" option to define a new SQL Server database connection. Then press OK.

SQL

In "Create New Data Source" configuration screen, type a descriptive name in first textbox to the data source being created now.
In the second textbox, select the driver "ODBC Driver 11 for SQL Server" to connect to SQL Server

SQL

Now click Connect to select SQL Server database instance and configure connection properties.

SQL

Provide the SQL Server instance name, I typed localhost as the SQL Server instance name which means the default SQL Server database instance installed on the local computer.
If you have more than one SQL Server instances running on the server and want to connect to the named instance, or you have a named instance as default on your computer, type the instance name.

If you want to login to SQL Server database using Windows authentication you can mark "Use Trusted Connection" checkbox.
In case that you will not be using Windows authentication to connect to SQL Server database, you will be configuring the SQL login name and the password for a successfull connection.

Press on Options button and choose the database from the list displayed in the Database combobox.
Then press OK

SQL

If you want to display table data directly from SQL Server database in Excel sheet, you can choose the database table in the fourth entry. As you see I have already selected our source table "SQLData2Excel"

If you want to execute SQL query or stored procedure and return result set of that SQL script from the SQL Server and import it into Excel, you can bypass selection of the database table and then continue with following steps.

SQL

When you press OK, the SQL Server datasource will be created with the name we have choosen at the beginning.

SQL

KodyazSQL data source is already highlighted, click OK

Microsoft Query tool is launched directly. Since we have already selected a database table, it will be displayed.

SQL

Choose table for all columns, or columns one by one from left to right. All these selected columns will be imported on Excel sheet.
Then click Next.

Following configuration screen on Query Wizard is Filter Data.
By pressing Next, Sort Order screen is displayed.
The last screen of the Query Wizard is:

SQL

Choose Return Data to Microsoft Excel and click Finish to display SQL Server database table contents on Excel sheet immediately.

SQL

Import SQL Query Data from SQL Server in Excel

If you want to import data returned from the execution of a SQL query or execution of a stored procedure into Excel documents, instead of simply importing database table data you can follow instructions illustrated in this tutorial part.

First of all, in previous part we have declared a data source for the Excel import. We have to define the same data source configuration without one settings which is the table selection. Since we will not import data from database table, but we seek for data from the execution of a SQL query or SQL Server stored procedure we don't need to predefine a table name in our data source configuration.

After SQL Server data source is created using the Excel menu "Data > From Other Sources > From Microsoft SQL", click OK button when it is selected

choose data source for Excel import for SQL Server database

When the Query Wizard is displayed to choose columns for import, press Cancel for next step

Query Wizard for Excel import from SQL Server

A dialog screen will be displayed asking if you want to continue editing this query in Microsoft Query.

continue Excel data import with Microsoft Query tool

Since we want to edit our own query, we need Microsoft Query tool. So click Yes to continue editing the import query in Microsoft Query tool in Excel.

When Add Tables dialog is displayed, you can either choose tables for the query editor, or if you already know tables and columns or stored procedures to use, you can skip this step by pressing Close.

switch to Microsot Query SQL editor on Excel

You can switch to SQL view by pressing the above icon.

Now on the SQL window, you are free to run any SQL statement that will return a data set.
For example, you can simply run a SQL Select statement like;


select * from SQLData2Excel

Or you can even execute a SQL Server stored procedure and import the output rows of the SP in Microsoft Excel document.


execute ExportData2Excel

execute SQL statement on Microsoft Query

After you type the SQL statement, click OK button. You might be warned in cases if the SQL Query cannot be represented graphically. It is not so important, continue anyway. We deal with data rows to import to Excel, so graphical representation does not matter.

As you see in below screenshot, the execution of the SQL statement (SQL query or SQL Server stored procedure) is returning data that we want to import to Excel document.

Excel Microsoft Query with data from SQL Server database

If you press the Return Data icon on the top iconbar of the Microsoft Query tool, the returned data set will be displayed in your Microsoft Exceel sheet.

import SQL Server data in Excel sheet

I hope you will benefit from the Excel tools to import data from SQL Server database, or from other external data sources after reading this tutorial.