Importing Data from Access Databases | Collecting and Cleaning Up Data

Problem

You have data in a Microsoft Access database that you'd like to import into Excel for analysis.

Solution

Use File images/U2192.jpg border=0> Open... to open the Access database file.

Discussion

When you select File Open..., youll get the familiar Open dialog box. In the "Files of type" drop-down listbox, look for and select the Access Databases file type, as shown in Figure 3-8. Access database files have the file extension .mdb.

Locate and select the database file you'd like to open and then press the Open button. Upon doing so, you'll see the Select Table dialog box shown in Figure 3-9.

The example database file I'm using here contains standard structural material properties and standard structural shape data. It includes two distinct tables: Materials and Sections. In this case, I've selected the Materials table for import into Excel. When I make the table selection and press the OK button, the data contained in the table is imported into Excel, as shown in Figure 3-10.

That's all there is to it. You can now perform operations on the imported data as you wish.

I should point out that the data imported into Excel is linked to the original Access database. This means that when data is changed in the original database, it can automatically be updated in your spreadsheet. You have some control over how the data is updated. Notice the External Data toolbar , shown in Figure 3-10, to the right of the imported data. This toolbar appears automatically when the data is imported. (You can also show this toolbar from the main menu bar via View images/U2192.jpg border=0> Toolbars images/U2192.jpg border=0> External Data.)

Figure 3-8. Opening an Access database

Figure 3-9. Select Table dialog box

The second button from the left of the External Data toolbar is the Data Range Properties button . Pressing this button opens the External Data Range Properties dialog box , which allows you to specify how often you want Excel to automatically refresh the imported data. You can also specify that you want the data automatically refreshed every time you open your spreadsheet. The button with the exclamation point on it forces Excel to refresh the data instantly.

Figure 3-10. Imported Access table

You can import data from other databases as well. For example, Excel allows you to create new database queries facilitating access to such databases as Oracle, FoxPro, and dBase. Select Data Import External Data images/U2192.jpg border=0> New Database Query... to create a new database query or use an existing data source. See the help topics "About importing data," "Create an OLAP query file to connect to an OLAP database," and "Import or connect to data in Excels online help" for more information.