About Microsoft Excel

Get external Data from a Database like MS-Access

Home Back Next


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 databe types like MySQL.
If you wish to import or rather dump data into Excel from a database you can use the following procedure:
  1. Click on the data tab
  2. From the command group 'Get External Data' click on 'From Access'
  3. A new window called 'Select Data Source' opens
  4. Select the databse file. We have slected the sample database file 'Northwind 2007.accdb'. You can select your own database file
  5. Click Open
  6. A new window called Select Table opens
  7. Click on the item Employees
  8. Click OK
  9. A new window called 'Import Data' opens
  10. 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
  11. At the top under 'Select how you want to view the data in your workbook?' the option button Table is also selected automatically.
  12. 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
  13. 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 > evertime 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


Home Back Next