• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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?

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

Connecting Microsoft Excel to External Data Sources

  • You will get the dialogue window shown below

Connecting Microsoft Excel to External Data Sources

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

Connecting Microsoft Excel to External Data Sources

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

Connecting Microsoft Excel to External Data Sources

 

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

Connecting Microsoft Excel to External Data Sources

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

You will get the following options dialogue window

Connecting Microsoft Excel to External Data Sources

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

Connecting Microsoft Excel to External Data Sources

 

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

Connecting Microsoft Excel to External Data Sources

  • 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

Connecting Microsoft Excel to External Data Sources

  • Click on Next button

Connecting Microsoft Excel to External Data Sources

  • Select Comma on the Delimiters panel
  • Click on Next button

Connecting Microsoft Excel to External Data Sources

  • Click on Finish button

Connecting Microsoft Excel to External Data Sources

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

Connecting Microsoft Excel to External Data Sources

SQL Server external data source example

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

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

Connecting Microsoft Excel to External Data Sources

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

Connecting Microsoft Excel to External Data Sources

  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

Connecting Microsoft Excel to External Data Sources

  • 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.

Connecting Microsoft Excel to External Data Sources

  • You will get the following window

Connecting Microsoft Excel to External Data Sources

  • Click on OK button

Connecting Microsoft Excel to External Data Sources

Download the above Excel Code

Summary

Excel has powerful features that allow us to analyse numeric data and create visual reports such as charts. You can take advantage of external data importation to create your own custom reports that meet your business reporting requirements.

 

YOU MIGHT LIKE: