22.4. Excel and Databases A great deal of the world's business information isn't stored in Excel or Word files. Instead, it sits inside databases . Databases provide the best way to store massive amounts of data, like a product catalog for an online store or a customer list for a mail order company. Databases break this data into separate tables and let you search it with unrivaled flexibility. Databases are particularly adept at answering questions like "what products did this customer order last week?" and "how many employees have completed sensitivity training?" There are two basic types of databases in the world: -
End- user databases are designed for small-scale or single-person use. They store data and provide people-friendly tools for searching and managing the information they contain. The best-known end-user database is Microsoft Access, which is included in some versions of Office. -
Server-side databases work on a larger scale by storing data and feeding it to other programs and Web sites. Ordinary mortals don't use server-side databases directly. Instead, they use applications that, in turn , rely on databases. For example, when you browse Amazon.com, the Amazon Web page code is hard at work getting product information and customer reviews from some of the most high- powered databases on the planet. One well-known server-side database is Microsoft SQL Server. In Excel, it's just as easy to extract information from a small-scale database like Access as it is to get data from a full-fledged database server running SQL Server. There are a few minor differencesfor example, if you want to use Access data, you need to supply the file name only, whereas you need to know the server name , user ID, and password to get into a typical SQL Server database. However, the process of searching and importing the information is the same in both cases. Fortunately, Excel is designed to let you analyze information, no matter where it comes from. Excel's database features are a natural extension of this philosophy. For example, your company might track orders using a custom application that dumps its results into a database. If you want to find out what all the numbers amount to, and make a prediction about the company's future, you need to query that information from within Excel and start charting away.
Note: Data querying is a one-way street. You can extract the data and analyze it, save a copy on your PC, and even modify the information in your worksheet. However, Excel doesn't provide any tools to let you route your changes back to the original source where the data came from.
22.4.1. Defining a Data Source Getting data from a database is a two-step process. The first step is to tell Excel where to find the information by creating what's known as a data source definition . A data source definition tells Excel how to connect to your database server (or database file) so that it can extract the information it needs. You only need to perform this step once for each data source you want to use. If you don't have a database handy, use the Northwind.mdb Access database file, which you can find on the "Missing CD" page at MissingManuals.com). The following steps walk you through the process of defining your data source. -
Choose Data  Import External Data New Database Query. The Choose Data Source dialog box appears (see Figure 22-10). -
Select <New Data Source>, and then click OK. The Create New Data Source dialog box appears. -
Start by giving your data source a descriptive name in text box 1. This is the name that will eventually appear in the Choose Data Source list. Use something descriptive, like Production Database or Remote SQL Server for Acme, Ltd . -
In text box 2, choose the kind of database you want to access. For example, if you want to use an Access database, choose Microsoft Access Driver. If you need to use a SQL Server database, choose SQL Server (at the bottom of the list). -
Click the Connect button to try connecting to the database. At this point, a new dialog box appears. The contents of this dialog box depend on the type of database you're using. If you're connecting to an Access database, you need to specify the database file you're using. Click the Select button (in the Database box) and browse to a file, like Northwind.mdb. Once you've found the file, click OK. (Keep in mind that this data source remains valid as long as you don't move the file. If you move the file, you'll need to change the data source, or just delete it and repeat these steps all over again). If you're connecting to a server-side database like SQL Server, you need to give Excel the name of the server where the database is running, and you need to supply a user name and password (unless the Windows account you're currently using has the required permission, in which case you should just turn on the Use Trusted Connection checkbox).  | Figure 22-10. The goal of this operation is to find and register the database you want to draw data from. Top : The Choose Data Source dialog box begins by suggesting a few data sources, but it doesn't include your database. Middle: You configure a new data source by specifying the type of database and the location of the server or database file.
Bottom : Once you're finished, you'll see your data source definition appearback in the Choose Data Source dialog boxready to use. | | -
Click OK. This action returns you to the Create New Data Source dialog box. -
Click OK again. This action creates the data source. You can now close the Choose Data Source window by clicking OK. Or, you can continue with the next set of steps that show how to build a new query (in which case you can skip step 1). 22.4.2. Querying a Data Source Once you've identified where the data is, you're free to query it. Querying is the process of performing a search in a database. Once the search is complete, Excel takes the results and inserts them into your worksheet. A typical database includes several tables. Each table contains a set of records that are structured in a particular way. For example, the Northwind database (which you can find on the "Missing CD" page at MissingManuals.com) includes a Customers table, a Products table, and several more tables. When you create a query, you choose the tables and columns that you're interested in. You also set filter conditions to limit the results to just those records you want to see. Here's how it works: -
Choose Data Import External Data New Database Query. The Choose Data Source dialog box appears. -
Select the data source you created in the previous section, and click OK. This action launches the first step of the Query Wizard (Figure 22-11). This step lists all the tables in your database. If you expand a table (by clicking the plus box next to the table name), you'll see a list of all the columns in that table.  | Figure 22-11. In this example, six columns have been chosen from the Customers table. The order that the columns are listed in is the order that they're inserted into your worksheet. In other words, the Region column will be inserted first, followed by the City column, and so on. If you want to change column order, just select a column in the list, and use the up and down arrow buttons on the right to change its position. | | -
Select all the columns you want to retrieve. To select a column, double-click it so it moves to the "Columns in your query" box on the right. Unless you're familiar with databases, you'll find that it's easiest to only select the columns that belong to one table. If you want to use columns from more than one table, you need to make sure that there's a relationship between the tables. For example, if your database defines a relationship between orders and customers, you can retrieve information about the customer that made each order. -
Click Next. The Query Wizard moves to the Filter Data step. -
If you want to limit the number of rows you'll receive, you can define a filter. A filter works in a database query in the same way that it works with data lists (as discussed in Chapter 13). You choose a field that you want to use, and define a condition that must be met. You can add multiple conditions using the And option (in which case every condition must be satisfied in order for a row to be selected) or the Or option (in which case only one condition must be satisfied). Figure 22-12 shows an example.  | Figure 22-12. In this example, one filter condition is defined. The only customers that the query will retrieve are those that have USA in the Country column. In a database, there's no practical limit to how many filter conditions you can create. In Excel's Query Wizard, however, you're restricted to three. | | -
Click Next. The Query Wizard moves to the Sort Order step. -
In the "Sort by" list, choose the column you want to use for sorting. You can choose to perform an ascending or descending sort, and you can also sort based on multiple values (which makes sense only if there might be duplicate values in the first column). Of course, you don't need to worry too much about sorting nowyou can always use Excel's built-in sorting smarts after you finish the query, as described on Section 13.3 in Chapter 13. Figure 22-13 shows a sample sort order. -
Click Next. The Query Wizard moves to the last step. This step gives you a few advanced options. First of all, you can save the query to reuse later by clicking Save. You can also use Microsoft Query to edit the query you've built by choosing the "View data or edit query in Microsoft Query" options. (Microsoft Query is a tool that's bundled with Microsoft Office. It helps you fine-tune your queries using a graphical interface.)  | Figure 22-13. In this example, two sort keys are used. First, the list is sorted by Region. However, more than one customer might appear in the same region (or state). If so, these region groups are further sorted by city. If you want, you can add a third sort key to sort groups of customers that live in the same city. If you don't, the search will retrieve these records in whatever order they're stored in the database. | | This procedure works because, as you make your selection in the Query Wizard, the wizard builds a special query command that defines the data you want, the filter conditions you plan to use, and the sort order you need. This command is called an SQL command (pronounced es-que-el or see-kwul, if you want to impress your geek friends ). SQL stands for Structured Query Language, and, if you're familiar with this language, you can edit your query in more detail to perform more advanced tasks . However, this SQL wizardry is outside the scope of this book.
Tip: If you want to start learning about the SQL language, which almost all database products use to query and update data, you can find a number of good resources on the Web. One good starting point is the tutorial at www.w3schools.com/sql.
-
Select "Return Data to Microsoft Excel", and click Finish to insert the information. Excel opens an Import Data dialog box that lets you choose where the queried data should go. -
Choose whether you want to put the data in a new worksheet or in the current worksheet. If you choose the current worksheet, you can specify a cell reference. This reference points to the first column of the first row. For example, if you use the reference C10 when importing a three-column list, Excel adds the column headers in cell C10, D10, and E10, and adds the actual information in the rows beneath these cells . -
Click OK. Excel contacts the database, performs the query, and inserts all the retrieved results into your worksheet. The only formatting it applies is a bold font to the column headers. Figure 22-14 shows an example of information imported from a database.  | Figure 22-14. Once you're finished defining the query, you can insert the results into an Excel worksheet. This example shows a portion of the results extracted from the Northwind database. The next step is to apply formulas or create charts . | |
One of the nicest aspects of Excel's querying features is its ability to refresh the data at any time. If you suspect that the data has changed in the database, just select Data Refresh Data. When you do, Excel performs the query again, and inserts the new results (without disturbing any other content that might be in your worksheet). This technique even works if you save the file and open it up weeks later because Excel stores the query information in your Excel file. You can even choose Data Import External Data Edit Query to change the choices you made in the Query Wizard.
Note: Databases aren't the only place you can find massive amounts of information. Excel also lets you work with information that's pulled from a Web page (as described in Chapter 24) and information that's stored in an XML-based data format (Chapter 23).
|
