How to Import Data into Excel from Access, XML, Text file, SQL Server

Data is the bloodstream of any business entity. Businesses use different programs and formats to save the data depending on the business data storage requirements. You could have a payroll program powered by a database engine, you could have data in a CSV file or even from a website that you would like to analyse in Excel. This article shows you how you can achieve the above.

We will cover the following topics.

What is external data source?
MS Access external data source example
Web external data source example
Text file external data source example
SQL Server external data source example

What is external data source?

External data is data that you link/import into excel from a source that resides outside excel.

Examples of external include the following

Data stored in a Microsoft Access database. This could the information from a custom application i.e. Payroll, Point of Sale, Inventory, etc.
Data from SQL Server or other database engines i.e. MySQL, Oracle, etc. – This could be information from a custom application
From a web site/web service – this could be information from a Web services i.e. currency exchange rates from the internet, stock prices, etc.
Text file i.e. CSV, tab separated, etc. – this could be information from a third party application that does not provide direct links. Such data could include bank payments exported to comma separated file CSV, etc.
Other types i.e. HTML data, Windows Azure Market Place, etc.

MS Access external data source example

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

Download the above Database File

Create 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

Website(XML data) external data source example

In this example, we will assume we are trading the Euro currency and would like to get the exchange rates from the European Central Bank web service. The currency exchange rate API link is http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

Create a new workbook
Click on the DATA tab on the ribbon bar
Click on from Web button
You will get the following window

Enter http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml in the address
Click on Go button, you will get the XML data preview
Click on Import button when done

You will get the following options dialogue window

Click on OK button
You will get the following data

Text file external data source example

We will import data from a simple CSV file containing customer payments. You can download the CSV file for this exercise.

Download the above CSV File

Create a new workbook
Click on DATA tab on the ribbon
Click on From Text button
You will get the following window

Browse to the folder where you downloaded the CSV file
Select da.csv file
Click on Import button
You will get the following import text file wizard

Click on Next button

Select Comma on the Delimiters panel
Click on Next button

Click on Finish button

Click on OK button
You will get the following data

SQL Server external data source example

This exercise assumes you have a working instance of SQL Server and basics of SQL Server.

Create a new database named EmployeesDB
Run the following script

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

Create a new workbook in 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