Importing Data Into Excel
There are multiple ways to get data to Excel.
The Export facility. Page 128, 131
The Excel Data Import Facility
The Excel Data Query Facility
The Metro 2 Export Facility
The last two of these require Microsoft Open Database Connectivity drivers ro be installed on
your machine. They probably already are. For detailed information see, ODBC Configuration,
The Excel Data Import Facility
Data Query From a Single Table
From the Excel main menu, select “ -> Import External Data -> New Database Query”
You will see the following dialog, asking you to choose a data source. Select the data souce
LOANMASTERXL and click OK. If this data source does not appear your LOANMASTER
Excel link is not properly configured. See ???.
When the LOANMASTERXL datasource is opened, you will see the “ dialog.
This is a little misleading at first since what is displayed is a list of the tables in the database. You
must first select the table from which you want to import data. For this example, select the table
“ . Yuo
tranfile” will have to scroll almost al the way to the bottmo of the list to find it. The list
does a smart seek if you press the ‘ it will advance to the “ table
before the tranfile table.
Click on the + in the little box to the left of the “ table
tranfile” and show you the columns it contains. In this dialog the
The dialog will expand the “ table
term columns means fields on the table. Column is the official database name for fields.
Select the column loan_no in the left hand box and then click on the “ button. The name of the
loan_no column will be transferred to the right hand box.
One by one, select the other columns named tran_date, tran_code, tran_amt, and comment and
after each selection, click the “ button to transfer all these columns to the right hand box. This
Columns in your query” the data from these columns will be imported.
box is titled “ . Only
You can also transfer columns by double clicking on them.
When you have selected the columns you want to retrive, click on the “ button. You will
see the “ Data” dialog. ( the list in the center of the box will not yet be opened). Select
“ in the
tran_date” left box, the one labeled “ . The
Column to filter” top center pull down will turn
white. Click on the down arrow to the right of the top center pull down, the white one. And select
“ . Now the next pull down turns white. Enter a valid loan number from your loan system.
The example uses ‘ DW0009" a number from the LOANMASTER demo data. Notice that this
entry IS case sensitive and alpha characters Must be UPPER CASE.
Set the filter as follows:
At this time, be careful to filter ONLY on one column. We will get to two columns later.
After the filter is set, select the sort order. You can sort on any column in the query. The only
order that makes sense for the data we have selected is tran_date. Set the sort order to tran-date
as shown below.
The final step is to “ and import the data. If this is a query you will be doiong ofter, you
DW0009" . The graphics are not shown since this is
can save it in a query file. Save this query as “
just like saving any other file.
Next click “
Tell Excel where to save the data. It will default to the cell that was current when you started the
query. You can change it if you like.
OK” DW0009" will appear in the spread
Click on “ . After a few seconds the transactions for “
sheet. Do not attempt the “ Query” That button will take you back to the wizard you
used to create the query and allow you to change it.
Now dispose of everything in Excel by clicking on “ -> Close” do not save it. Next seelct
a new worksheet by clicking on the “ (blank white sheet) icon at the left side ot the Excel
icon tool bar. You sould have a new worksheet set to “ 1". From the Excel main menu,
Data . You
select “ -> Import External Data -> Import Data” will see the Data Sources dialog
again but this time it will contain the query you just saved. Select the data source “
Import dialog OK”
the “ Data” will appear. You can then either click “ to reimoprt the data, or you
can click “ Query” to change it, perhaps for a different loan number.
Data Query From Two Tables
This example is significantly more complicated than the single table query and builds on what you
learned in that example. Do not attempt this example until you have mastered the single table
Connecting the data from two or more tables requires the program MSQUERY.EXE. This
program comes with MS Office and with most of the office components. If it is not on your
machine, take a look at your installation CD’ your MS Office software.
From the Excel main menu, select “ -> Import External Data -> New Database Query.
Select the LOANMASTERXL data source.
From the “ loan
dialog, expand the “ file table and select the columns loan_no,
cust_name, and cur_bal
Scroll back up in the left hand box to the cicust table. Expand it and select the columns lastname,
firstname and company name. These will be added to the list of previously selected column names
in the right hand box.
When you click on “ you will receive an error message telling you that Excel cannot
combine these two tables and that you must do in MS Query. When yuo click “ there will be
a short delay while MSQuery loads. (If your machine cannot find MSQuery, you cannot continue
with this example until yuo install MSQuery.
When MSQuery loads you will see the following screen. This is only the upper right hand corner.
The screen is much larger. Note the two boxes side by side. These boxes contain a list of the
columns in the tables cicust and loanfile.
Using the scroll bars in each of these windows, scroll both windows until you can see the column
named custno in the cicust table and and cust_no in the loanfile table. Notice that the name
custno in the cicust list is printed in bold font. This means it is a key field in that table and the
table has an index on that column. You can only used indexed columns to Joint tables. Joining
tables is the process of combining them in such a way that the data retrieved from each table
corresponds to the data in the other table. This means:
“ the loan data from the loanfile and for each loan in the loanfile, look up the customer
data for the customer associated with that loan through the customer number”
“ all the loan data from the loanfile, and for each loan in the loanfile, look up the
customer record for the customer whose customer number is found in the loan record and
display that data with the loan data.”
This next step is very easy to do but it is very difficult to describe. Don’ too much about it,
just do it. If you do it wrong, it will not matter, you can just re-do it.
Place your mouse pointer on the column name “ in the
cust_no” loanfile box. Press and hold the
left mouse button. Drag the column name cust_no over and drop it on the column name custno in
the cicust box. While you are dragging, the cursor will change to al little rectangular box. When
you have droped it an arrow will appear linking the custno on the left to the cust_no on the right.
The shape of the arrow will depend on how the column names are aligned vertically. It will point
from one to the other. Below:
You are now ready to import the data. In the Microsoft Query main menu, select “ -> Return
Data to Microsoft Excel” you like, you can save this query at before you return to Excel.)
MSQuery will close and after a short pause, Excel will contain the data.
Advanced Data Queries
The two examples represent about one tenth of one percent of the features of Microsoft Excel and
Microsoft Query. You can import any data in the system, filtered by any selection criteria you can
imagine and sorted in any order. For example
Get me the name and phone number and current balance of all borrowers who live in zip
code 77123 or 77456 and who have a balance between 10,000 and 20,000 and made a
payment greater the 375.00 between the dates of 02/02/2003 and 03/15/2003. Sort the
results by zip code, then by date paid.
You can combine as many tables as you can keep up with. The computer can keep up with all of
For information about queries, look at books on Microsoft Excel. You will not find any books on
Microsoft Query, but there is a great deal of information available on the Microsoft web site. The
query wizard in MSQuery is very similar to the query wizard in Access. Looking at books on
Microsoft Access Queries would also be helpful.
The language used to produce queries is called SQL, an acronym for Structured Query Language.
There are hundreds of books on the subject. If you purchase a book on SQL, scan the first few
chapters to see if they are written at a level you can understand. If they are not, you are wasting
Microsoft Query 101, A Brief Intoduction
This is an introduction to Microsoft query. It is not intended to teach you “ to use”
Query, only how to start.
The Microsoft Query program is called msqry32.exe. It is usually installed at
C:\Program Files\Microsoft Office\Office10\msqry32.exe
You will probably not find it on the start menu. If it is not found at the above location, use the
Windows file search program to find it. If you don’ it at all. You will have to install it. It
comes with virtually all of the Microsoft Office products. You are on your own here. Bottom Line
Software cannot help you find and install Microsoft Office components.
Create a shortcut to msqry32.exe and optionally put it on the start menu.
When you have msqry32.exe installed and the icon (shortcut) on your desk top. Start up
Help” About You
MSQuery and select “ -> “ Microsoft Query” should have version information
Microsoft Query 2002 (10.2600.xxxxxx) SP-1. If your number is less that 10.26...., the
program will still work but the instructions may not match the program.
Starting A New Query
Choose Data Source”
From the MSQuery main menu, select File->New. You will see the “ dialog.
Select the data source LOANMASTERXL. And click OK.
The next dialog will look familiar.
Select the loanfile table and transfer the fields cust_no, loan_no, cut_name, and cur_bal to the
“Columns in Query” window.
Next select the cicust table and transfer the fields company, firstname, and lastname to the
“Columns in Query” window.
Again, you will have to tell the Query program how to “ the tables. This is exactly the same
as the operation you performed in the original two table example.
Put the cursor on the cust_name text in the loanfile box. Drag the text cust_no to the left and
drop it on the bold custno text in the cicust box. You will have to scroll the contents of both
boxes to make them line up like those in the example. This alignment is not required. All that is
required is that you can see the fields to be connected. You will not always drag right to left.
Depending on the order in which the tables are selected, you might drag left to right or right to
left. Not all fields can be used to join tables. If the fields cannot be used, the program will not let
you drop it.
Now for the third table. Select Table -> Add Table. (There is also an “ Table” button).
From the Add Tables dialog, select the table ciaddr and click on “ then “
The new table has been added to the query and note that the arrow from custno in ciaddr to
custno in cicust is already drawn. In this case, the program was smart enough to figure out how to
join the tables because the fields had the same name. It could not join the loan file and the cicust
table because the fields were not named the same.
Now drag the text “ addrslilne1" from the ciaddr table box and drop it on the heading of the blank
column to the right of the data grid. That sill be the empty column with the empty header to the
right of the lastname column.
You now have the addresses for all those customers in your grid. The data in the grid is called the
“ Set”the “ or . It
Cursor”is the same word as the mouse cursor but not the same meaning.
Cursor actually means something like “ . The mouse cursor runs across your screen and the
data cursor allows you to run through the data.
You have done a considerable amount of work by now. This is how you save it. From the File
Query-101". Note that the program addw the
Menu, select save and give it a name, perhaps “
extension .dqy. Do not change it.
Now click on the SQL button. You will see the actual Structured Query Language statement used
to produce the result set in your query. If you are inclined, you can edit this text to modify the
query or you can write queries from scratch and run them.
Notice that this is NOT something you can pick up in a few minutes. Unless you are ready and
able to spend many hours learning the SQL language, do not mess with this feature.
Close the SQL dialog
Click on the heading of the cur_bal column. The entire column will turn black to indicate it is
selected. Now click on the tool button labeled A-Z with the arrow pointing down. The data in the
grid will be sorted to place the rows in order according to the value of the current balance from
lowest to highest.
With the cur_bal column still selected click on the tool button labeled Z-A with the arrow pointing
The grid will be resorted according to balance from high to low ( Z to A )
Now click on the lastname column header and then click on the A to Z button. The grid will
resort according to last name.
Display the Criteria dialog by selecting View -> Critera from the main menu.
Locate the criteria column at the left of the criteria dialog next to the prompt “
Click on the empty criteria box with the active pull down button by clicking on the down pointing
arrow and selecting lastname as the fieldname. ( No graphic but seel below )
After the Criteria Field has been set to lastname, double click on the blank box below the criteria
field next to the prompt Value:. Set the Edit Criteria dialog to Operator = Begins with and Value
= G.. (You might have to use a different value if you are not using the demo database.
Click on OK and note that only rows with last names beginning with “are visible.
Use the backspace to delete the Like‘ from in the location highlighted in the graphic above.
Now there is no criteria and all the rows are again visible.
Saving the Query Result Data
You cannot directly save this data in any format you can use. To save the data extracted by
Microsoft Query, you must use The Excel function Data -> New Database Query to edit the
Return . Note
query in MSQuery then “ the data to Excel” that the New Database Query menu
selection has a Queries tab that allows you to open saved queries.