Excel Database Query (Single Table)

In this lesson you will lean how to use Microsoft Query to import data into your Excel workbook. In the exercise below you will practice each of the steps involved in importing a dataset from within a text file.

In the downloadable tutorial on Microsoft Query you will learn how to work with other types of files with other Excel files or with data residing in any kind of database (Acces, Oracle, SQL Server and others). You will also learn how to combine data from two tables or two files within Microsoft Query.

Open a new workbook and select cell "A5" of the sheet you are on.  If you are working with the "Tutorial on Microsoft Query" you save the workbook as "data-import-test-001" where you stored the components of the tutorial

Excel before 2007: Go to the menu bar "Data/Import External Data/New Database Query".

Excel since 2007: Go to the menu bar and select the "Data" ribbon. Click on the "From Other Sources" icon and select "From Microsoft Query".

You will then see the dialog window below. You have just started a process that will accomplish two things:

- it will import the a set of data into your Excel workbook
- it will create a query that you will be able to use in other workbooks.

Before you begin you might want to refresh your knowledge about databases. See database 101

What appears in the list are the names of queries that have been previously created. For this lesson you will click on "New Data Source". The following dialog window appears:

In the first text box you will give a name to the new query that you are about to create. For the purpose of this exercise we will call it query-test-001.

In the second text box you will select a driver. A driver is a small program that remains invisible while translating data in any format so that Excel can read it. For this example we will select "Microsoft Text Driver (*.txt, *.csv)".

We will click on the "Connect" button to establish an ODBC connection. An ODBC connection is another invisible program that allows Excel to talk with the external data source. In the next dialog window you will select directory containing the text file from which you will want to import data. If you are working with the "Tutorial on Microsoft Query" you will leave the check mark besides "Use Current Directory" and click "OK". Otherwise remove the check mark and select another directory. Remember that you are selecting a directory and not a selected file. You will select the test file containing the data in the next step.

Learn more on Excel
See the list of small downloads

There are  five sections in this website

Section 1: About Databases: 4 lessons
In this section we review the basic notions on databases and evaluate Excel as a database. You will then see how Excel can be used downstream from most centralized databases and data management programs. Finally we will discuss the "upgrade" from Excel to Access..
Section 2: Excel and External Data: 5 Lessons
There are many ways to get data into your Excel Workbooks and there are many ways to easily extract data from all the databases in your organization and the Internet. This section is centered on querying data
Section 3: Analyzing Data with Excel: 8 lessons
Once you have the data in the right formats within your Excel workbook it is time to use the powerful analytical tools of Excel including the database functionalities (sort, filter, subtotals), the integrated calculator, the comparative analysis tools and the mysterious and not so complicated pivot tables. 
Section 4: Reporting with Excel: 7 lessons
The report is a communication tool and you can not accept to compromise on its presentation. Excel is the best reporting tool allowing you to easily develop popular dashboards and to share these reports with multiple colleagues.
Section 5: Other Functionalities in Excel: 3 lessons
In this fifth section you will review the useful CTRL and ALT keystrokes, the conditional formatting, the drop-down lists and other interesting functionalities.