Home >  Exploring Excel  > 

An Excel Tutorial:

Use MS Query with Text Files
For Excel PivotTable Analysis

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. This could be on your computer or on a shared network drive. Deselect the checkbox for Use Current Directory. This will enable the Select Directory button, which you should click next.

Navigate to the folder where your text files are located. After you do so, you should see the names of your text files listed in the left pane.

Choose OK. After you do so, you will be returned to the ODBC Text Setup dialog.

Click the Options>> button in the lower-right corner. This will expand the dialog, as shown here.

Click on the Define Format button at the bottom of the dialog. This displays the Define Text Format dialog, shown below.

In the left pane of the dialog, select the first text file you will use and then make three changes to the dialog's settings:

1. If the text file begins with a line of headers (column names), place a check in the box next to Column Name Header.
    
2. Select the type of delimiter or "Fixed Length" from the Format dropdown list box.
    
3. Enter a number, if necessary, for the Rows to Scan. This specifies the number of rows scanned when you preview data. At times, Excel sets this number to 25 by default, which is fine.

The setting for ANSI or OEM seldom matters. You can ignore it.

Click the Guess button in the upper right of the dialog. You should see the headers from your text file, if it has them. Otherwise, you will see "F1", "F2", "F3", etc. for Field One, Field Two, Field Three, and so forth.

In this dialog you often must correct the Data Types generated by Excel's guessing logic.

One reason for this is that many number codes have leading zeros, like "000123", "000234", etc. Excel typically guesses that fields with these codes contain integer data. But you must change them to Char to preserve these zeros when you export to Excel.

Second, Excel often guesses that dates are integers or characters. You must change them to dates.

After you modify any field in the right page, you must click the Modify button to force Excel to remember that change.

After you define each field associated with each file in the left panel, click OK until you return to the Choose Data Source dialog.

Modify the Schema, If Necessary

If Excel didn't have a certain bug, you could create your query immediately. However, you might need to take one more step before you create the query. Therefore, choose Cancel to dismiss the Choose Data Source dialog.

When you create a connection, Excel saves that information in the schema.ini file, which is saved in the folder that contains your text files. Unfortunately, when Excel creates this file, it fails to label the Char (character) fields on some computers.

To illustrate, on Charley's computer, Excel does not include the Char label in the ini file. But on Marty's computer, Excel does include the Chart label.

Therefore, before you can continue, you must label the Char fields manually, if necessary. To do so, use Notepad or some other text editor to open schema.ini. The file will look something like the Before column shown below.

Here, we've formatted the column definitions you don't need to modify with a green font. Those that you do need to modify have a brown font. You can see that the green items in the left pane include a data type and the brown items don't.

Therefore, as shown in the right pane, simply add a space plus "Char" after each field that should be a character field, if necessary. Also, if you notice any corrections that you need to make to the other data types for the other columns, you can make those corrections here.

Once you make these simple corrections, save schema.ini. 

previous 1 2 3 4 5 next