How to Import SQL Database Data into Excel File [Example]

Import SQL Data into Excel File

In this tutorial, we are going to import data from a external SQL 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.

1. Create a new database named EmployeesDB
2. 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

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

1. Enter the server name/IP address. For this tutorial, am connecting to localhost 127.0.0.1
2. 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.
3. 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

• Click on OK button

Download the SQL and Excel File

How to Import MS Access Data into Excel with Example

Here, we are going to import data from a simple external database powered by Microsoft Access database. We will import the products table into excel. You can download the Microsoft Access database.

• Open a new workbook
• Click on the DATA tab
• Click on from Access button as shown below

• You will get the dialogue window shown below

• Browse to the database that you downloaded and
• Click on Open button

• Click on OK button
• You will get the following data

Download the Database and Excel File

Refreshing and Managing the Database Connection

The advantage of importing over pasting is that Excel keeps a live connection to the database, so a single refresh brings in the latest rows without repeating the wizard. Managing that connection keeps the report both current and secure.

1. Refresh the data: Click any cell in the imported table, open the DATA tab, and choose Refresh, or Refresh All to update every connection.
2. Refresh on open: In Connection Properties, tick “Refresh data when opening the file” so the report is up to date every time it is opened.
3. Manage connections: Use Queries & Connections to rename, edit, or delete a connection, and to check the server and database it points to.
4. Protect credentials: Prefer Windows authentication where possible, and never save a database password in a shared workbook.

⚠️ Warning: A workbook that carries a live database connection can expose the server name and the query. Remove the connection with Queries & Connections before sharing the file outside the organisation, or paste the values as static data first.

FAQs