Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query with Text Files
For Excel PivotTable Analysis

(continued)

Create a Query

In Excel, activate a new workbook. Choose Data, Import External Data, New Database Query, which displays the Choose Data Source dialog, shown below. Make sure the checkbox at its bottom, labeled Use the Query Wizard to create/edit queries, is not checked. Select the name you assigned to your connection. Then click OK.

Excel displays the Microsoft Query graphical interface, where you can begin to build your query. Excel also displays the Add Tables dialog, as shown below.

Click on each table you want to add, and then click on the Add button. Repeat this process for each table -- text file -- you want to add. (You also can double-click on each table you want to add.) The trick of holding down the CTRL key to select multiple files doesn't work here.

For our example, we'll add all three of these tables. After doing so, close the Add Tables dialog.

The tables you added will be displayed in the top pane of the MS Query interface, the Table Pane. In each table box, there will be a list of fields.

If you've selected more than one table for your report, as in this example, you'll need to “join” the tables. To do so, you'll need to know which fields each pair of tables have in common. Often -- but by no means always -- the two common fields will have the same name. What is much more important is that they both have the same data format (including width) and that they contain the same “kind” of information.

In our case, we'll join CUSNUM (Customer Number) in the CUST (Customer) table to the CUSNUM field in the ORD (Orders) table. The easiest way to do this is to click on one of them and drag it to the other. This will draw a line between the tables.

previous 1 2 3 4 5 next