This displays the Define Text Format dialog, shown below.

This will expand the dialog, as shown here.Ĭlick on the Define Format button at the bottom of the dialog. After you do so, you will be returned to the ODBC Text Setup dialog.Ĭlick the Options> button in the lower-right corner. After you do so, you should see the names of your text files listed in the left pane.Ĭhoose OK. Navigate to the folder where your text files are located.

This will enable the Select Directory button, which you should click next. Deselect the checkbox for Use Current Directory. This could be on your computer or on a shared network drive. The default behavior is to use the current directory to store your text files, but in practice, we almost always will want to navigate to the directory where the text file resides. When you click on it, Excel launches the ODBC Text Setup dialog. Since we intend to connect to a text file, choose the Microsoft Text Driver (*.txt, *.csv).Īfter you select the driver, Excel enables the Connect button. This is the name that will appear in the Choose Data Source list later on.Īfter you enter a name, Excel enables the second box where you will use the drop-down list to select a driver. In the first box, enter a name for your connection. Then select the item, and click OK.Įxcel displays the Create New Data Source dialog. Choose Data, Import External Data, New Database Query, which displays the Choose Data Source dialog. The first step is to create a new connection - a “data source” - to add to the list in the Choose Data Source dialog below. Therefore, we need to work around the bug. Second, the MS Query Engine is based on Access version 1.0, and it has a bug that has never been corrected. With a text file, you will need to tell Excel how to interpret each field. This is more involved than the connection to an Excel workbook, for two reasons.įirst, an Excel workbook is capable of storing all of the information about the data that you’re connecting to. The first order of business is to create a connection to your text file database. For this example, they’re tab-delimited text files, but they could just as easily be comma-delimited or fixed length. This time, let’s look at the same three tables, only this time they’ll be stored as text files. In the article, Use MS Query to Treat Excel As a Relational Data Source, Marty Ryerson introduced a mini-database of three tables, and used Excel as the database container. (Charley’s Note: This article is very out of date.

Using the text import wizard in excel