Get external Data from Database like MS Access

Let’s learn how to get data into MS Excel from a database like MS access. Of course, you can import data into Excel from many other database types like MySQL.
If you wish to import or rather dump data into Excel from a database you can use the following procedure:

Click on the data tab
From the command group ‘Get External Data’ click on ‘From Access’
A new window called ‘Select Data Source’ opens
Select the database file. We have selected the sample database file ‘Northwind 2007.accdb’. You can select your own database file
Click Open
A new window called Select Table opens
Click on the item Employees
Click OK
A new window called ‘Import Data’ opens
In the window a cell $A$1 is highlihgted under ‘Where do you want to put the data?” and the option button ‘Existing worksheet’ is already checked
At the top under ‘Select how you want to view the data in your workbook?’ the option button Table is also selected automatically.
Since we have a header in cell A1 we click inside cell A4 which is displayed in the Import Data window and we click OK
All the dta of the employees is dumped into sheet1 starting with cell A4

We may not want the complete data but we may want specific data like first name, last name and email. How can we implement this procedure of importing specific data into Excel?

We select sheet2
Click on Data and then select ‘From Other Sources’ above the command group ‘Get External data’
From the drop down menu we’ll select ‘From Microsoft Query’
A new window opens with the title Choose Data Source
We’ll click on MS Access Database. You’ll notice that the checkbox right at the bottom of the window displaying ‘Use the query wizard to create/edit queries’ is selected
Click OK
The Select Database window opens
We’ll select ‘Northwind 2007.accdb’ in the My Documents folder
Click OK
The ‘Query Wizard – Choose Columns’ opens
Under ‘Available Tables and Columns:’ we’ll select customers and click on the + sign next to the customers
Well select the items ‘Last Name’, ‘First Name’ and Email one by one and click on ‘>’ every time to move the items under ‘Columns in your query’
We can rearrange the items to display: First Name, Last Name and Email in that order by clicking on the First Name and then clicking on the Move Up arrow to the right
Click on ‘Options…’ and check all the options Tables, View, System Tables and Synonyms, especially the System Tables option
We can click on the item First Name and then click Preview Now and see the data under ‘Preview of data in selected column’
Click on Next to come to the window Query Wizard – Filter Data. here well do nothing and click on Next to proceed to next window
In the new window Query Wizard – Sort Order we’ll select First Name and sort in Ascending order by selecting the option Ascending on the right
In the window Query Wizard – Finish we’ll accept the selected option: ‘Return Data to Microsoft Office Excel’. Other option available is ‘View data or edit query in Microsoft Query’ which can display the data in a grid. We can also select: ‘Save Query…’ to save our query to run it without going through each of the steps again.
Click OK
In the import data window click OK

We now observe that the selected columns First Name, Last Name and Email have been imported into our Excel Worksheet 2. Now we can request Data Administrators to give us specific data like this and not to dump the complete data tables so that we can concentrate on the job at hand.

P. S.: Have a look at how to import data from one Excel file into another

View the Excel training video below:


Further reading:
Connect external data to your workbook

One thought on “Get external Data from Database like MS Access

  1. Pingback: Get Data from Access Database using Excel VBA | Excel VBA Training Videos

Leave a Reply

Your email address will not be published. Required fields are marked *