Importing Data from a Database
If you have a dBase, FoxPro or
Access database then you can easily import data directly into Excel.
The spreadsheet can then be set up to refresh itself from the database on
a regular basis if you require.
To start importing data from a
database, click on the Data drop-down menu and choose Get External
Data and then New Database Query.
A window appears as shown below where you can choose the type of database you are going to import from.
Select the database type and then
click the OK button, we are going to import data from a
previously-created Microsoft Access database.
A window appears where you can
choose the database you want to import from.
Use the Drives box to find the disk drive containing the database
and then find your way to the folder containing the database in the Directories
box. When your database appears in
the list on the left of the window shown below, click on it once and then click OK.
will connect to your database and show you the tables you can import from.
Click on the +
sign beside the table name to see the fields you can import into your
spreadsheet. Click on the >
arrow to move the selected field over to the Columns in your query list
on the right as shown below. If you add a field that
you later want to remove you can highlight the field in the list on the right
and then click the < button to remove it.
Clicking the <<
button will remove all the fields from the list so you can start again.
You can also change the order of the imported fields by clicking on a
field in the list on the right and then clicking the up and down arrows to the
right of the window to adjust its position.
When you are happy with
the selected fields, click the Next button.
The next window in the Query Wizard gives you the opportunity to only
select records from your database that meet certain criteria.
Choose the field you
want to set criteria for from the list on the left and then use the arrow to the
right of the first box in the Only include rows where: section to choose
the type of comparison you want to make. In
the example shown above we are looking for cases where the Balance is greater than a value.
Type the value in the right hand box or use the drop-down arrow to choose
a value that Excel has found in the database.
If you want to add extra criteria you can click to place a bullet beside And
or Or and then enter another set of criteria.
When you are happy with your criteria click Next and you will be
able to set a sort order for your imported data as shown below.
you have set your sort order, click Next and you can choose what to do
with your data. If you choose Return
Data to Microsoft Excel you will be able to import the data to your
Click Finish and
you will be able to import the data to your worksheet, either the existing
worksheet or a new worksheet.
By clicking on the Properties
button shown above you can set the spreadsheet to refresh itself automatically
from your database at intervals which you set.
If you don’t click the Properties button you will just see a one-off
snapshot of your database in the spreadsheet.
When you click OK
the data will be imported to your spreadsheet.
If you change the data in your spreadsheet the corresponding database
will NOT be changed. However, if
someone makes changes to the database then the spreadsheet will be updated to
reflect these changes if the Properties button was used to enable the data to
If you have set your
spreadsheet to refresh from the data source you can change these options by
clicking on the Data drop-down menu and choosing Get External Data
and then Data Range Properties. The
window shown above will appear and you can change any settings to remove the data
refreshing or to make it happen more or less frequently.
Click here to
return to the Excel index.