It is often useful to access external database files when working with Excel. One of the advantages is that you can import the data into Excel and then manipulate it and format it by using tools that are most likely already familiar. Excel is capable of reading some database files directly, particularly those with a DBF extension. A small file can be downloaded into Excel in its entirety. However, in most cases, you do not need the entire file but are interested only in certain fields. You may want to import certain data and exclude other fields not necessary to your project. In that event you need to query the database and load into your worksheet only a subset of the external database.
On a Windows client, the first thing you must do is to install Microsoft Query. You will be prompted to install it when you select the the Create New Query command fron the Get External Data submenu on the Data menu. Rerun the Excel or Microsoft Office setup program and install Query.
To work with an external database file once Microsoft Query is installed, use the Create New Query command to launch the Query wizard and indicate the database you want to query. Specify the fields or records that you want, and indicate how you want the data returned: to a worksheet or as a pivot table. If you wish, save the query in a file for use later. You can refresh the file at some point in the future and it will update the values if there have been any changes since it was last used.
Run Excel simultaneously with Query and switch back and forth between the programs. As Query initiates, it will display the Choose Data Source dialog box, which contains three tabs. The first tab to the left is Databases, and it lists the data sources that are known to Query. It may be empty unless data sources are defined on your particular system. If you frequently work with external databases, these should be listed here. The second tab is named Queries and contains a list of stored queries that can be reused. This also may or may not be empty. The third tab is named OLAP cubes and is used to store a list of cubes that you want to access more than once. Online analytical processing or OLAP is a way to organize large databases to accommodate the way that you prefer to analyze and manage information. The data in an OLAP database is organized by the level of detail in a hierarchical structure.
For example, if you want to track a certain business product, you can track it by where, when, and how by accessing the OLAP dimensions. A location dimension might contain fields for country, region, city, etc., while a when dimension might include the month, date, day, and year fields. Dimensions in an OLAP database can be combined to provide information about intersecting points. Because you can combine several dimensions as you prefer, OLAP databases are called cubes. Excel can connect to an OLAP source by using either the Microsoft DSS Analysis server or other third-party OLAP product that provides data source drivers, compatible with OLD-DB for OLAP. You connect to an OLAP cube the same way you connect to other external data sources. Excel displays data that you retrieve from an OLAP cube as a PivotTable or PivotChart.
About the Author
Barrie Sosinsky (barries@killerapps.com) is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.
For More Information
