Import SQL Database into Excel

May 15, 2023

Import SQL Data into Excel File

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

In this Excel tutorial, you will learn –

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

Create a new database named EmployeesDB
Run the following query

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

How to Import Data to Excel using Wizard Dialog

Create a new workbook in MS Excel
Click on DATA tab

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

Connecting Microsoft Excel to External Data Sources

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 the next button

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

Connecting Microsoft Excel to External Data Sources