Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query with Text Files
For Dynamic Excel Reporting

You can treat text files of data in a folder as relational tables of
data in a database. You can join them by their common fields.
Query them using SQL. And use the queries with PivotTables.
Here's how.

by Marty Ryerson
and Charley Kyd
December, 2005

In the article, Use MS Query to Treat Excel As a Relational Data Source, Marty introduced a mini-database of three tables, and used Excel as the database container.

This time, let’s look at the same three tables, only this time they'll be stored as text files. For this example, they're tab-delimited text files, but they could just as easily be comma-delimited or fixed length.

As in Marty's first article, we rely heavily on the book that Timothy Zapawa published this year, Excel Advanced Report Development.

If you want to follow along with this description, you can download the sample text files using this link.

Create a Connection

The first order of business is to create a connection to your text file database. This is more involved than the connection to an Excel workbook, for two reasons.

First, an Excel workbook is capable of storing all of the information about the data that you're connecting to. With a text file, you will need to tell Excel how to interpret each field.

Second, the MS Query Engine is based on Access version 1.0, and it has a bug that has never been corrected. Therefore, we need to work around the bug.

The first step is to create a new connection -- a "data source" -- to add to the list in the Choose Data Source dialog below.

In Excel, open a new workbook. Choose Data, Import External Data, New Database Query, which displays the Choose Data Source dialog. Then select the <New Data Source> item, and click OK.

Excel displays the Create New Data Source dialog. In the first box, enter a name for your connection. This is the name that will appear in the Choose Data Source list later on.

After you enter a name, Excel enables the second box where you will use the drop-down list to select a driver.  Since we intend to connect to a text file, choose the Microsoft Text Driver (*.txt, *.csv).

After you select the driver, Excel enables the Connect button. When you click on it, Excel launches the ODBC Text Setup dialog.

1 2 3 4 5 next