How to Import SQL Database Data into Excel [Example]

Details

Last Updated: 11 January 2021

In this tutorial, we are going to import data from a SQL external database. This exercise assumes you have a working instance of SQL Server and basics of SQL Server.

First we create SQL file to import in Excel. If you have already SQL exported file ready, then you can skip following two step and go to next step.

Create a new database named EmployeesDB
Run the following queary

USE EmployeeDB
GO

CREATE TABLE [dbo].[employees](
	[employee_id] [numeric](18, 0) NOT NULL,
	[full_name] [nvarchar](75) NULL,
	[gender] [nvarchar](50) NULL,
	[department] [nvarchar](25) NULL,
	[position] [nvarchar](50) NULL,
	[salary] [numeric](18, 0) NULL,
 CONSTRAINT [PK_employees] PRIMARY KEY CLUSTERED 
(
	[employee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO employees(employee_id,full_name,gender,department,position,salary)
VALUES 
('4','Prince Jones','Male','Sales','Sales Rep',2300)
,('5','Henry Banks','Male','Sales','Sales Rep',2000)
,('6','Sharon Burrock','Female','Finance','Finance Manager',3000);

GO

Importing Data to Excel using the Data Connection Wizard Dialog

Create a new workbook in MS Excel
Click on DATA tab

Select from Other sources button
Select from SQL Server as shown in the image above

Enter the server name/IP address. For this tutorial, am connecting to localhost 127.0.0.1
Choose the login type. Since am on a local machine and I have windows authentication enabled, I will not provide the user id and password. If you are connecting to a remote server, then you will need to provide these details.
Click on next button

Once you are connected to the database server. A window will open, you have to enter all the details as shown in screenshot

Select EmployeesDB from the drop down list
Click on employees table to select it
Click on next button.

It will open a data connection wizard to save data connection and finish the process of connecting to the employee's data.

You will get the following window