Click here for great value web hosting.

ALL ABOUT OFFICE

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT




Search for
 

help


EXCEL

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.

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

When 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 spreadsheet.

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

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.

webmaster@allaboutoffice.co.uk