Importing Data from Text Files | Collecting and Cleaning Up Data

Problem

You have some data you'd like to analyze in Excel but it's located in a separate text file.

Solution

Open the text file in Excel using File images/U2192.jpg border=0> Open....

Discussion

Excel is capable of reading many different file formats, not just Excel spreadsheet files. Opening a text file is very much like opening a spreadsheet file: simply use File images/U2192.jpg border=0> Open... (or use the shortcut Ctrl-O) to pull up the Open dialog box and select the file you wish to open. Figure 3-1 illustrates the Open dialog box.

Figure 3-1. Open dialog box

The Open dialog box defaults to listing only Microsoft Excel files (with extensions .xls, .xla, and so on). To list text files (with the .txt extension), you'll have to select Text Files in the "Files of type" drop-down listbox, as shown in Figure 3-1. Press the Open button after selecting the file you wish to open.

The sample file I'm using here, Filip.txt , was obtained from the National Institute of Standards and Technology (NIST) online statistical reference dataset archive. This particular dataset was prepared by A. Filippelli of NIST for polynomial least-squares regression.[*]

[*] You can find all sorts of useful data, a lot of which is freely available, on the NIST web site at http://www.nist.gov.

Upon pressing the Open button, you'll see the Text Import Wizard dialog box shown in Figure 3-2. Since text files aren't necessarily in a format consistent with tidy rows and columns, Excel provides this import wizard to assist with parsing text, so the data you're after ends up in rows and columns in the newly formed spreadsheet. The wizard treats every line in the text file as a row of data. It then allows you to specify where column breaks should go so your data ends up in rows and columns. If you skip the column specification part, you could end up with rows of data in a single column, with each cell in the column potentially containing long strings of text.

Figure 3-2. Text Import Wizard: Step 1

Specifying column breaks is fairly painless. The first thing you need to do is specify whether your data is in delimited or fixed-width form. Delimited means the data on each row is separated by characters such as commas, spaces, tabs, or semicolons. Fixed width means the sets of data on each row are already aligned in columns, with spaces separating the columns. The data I'm using in this example is fixed width, as shown in Figure 3-2. See Recipe 3.2 to learn how to work with delimited data .

Notice the row numbers to the left of the data previewed in Figure 3-2. The text file actually contains some descriptive information at the beginning of the file that, while useful, is not what I'm really interested in importing. Therefore, I've scrolled down to row 60 in the preview window so I can see the actual data. I've also set "Start import at row" to 60 so all the lines above the actual data will be skipped.

At this point, you should press the Next button to advance to the next step in the import wizard. Doing so opens the dialog box for step 2 shown in Figure 3-3.

In this step you actually specify your desired column breaks. The import wizard usually makes a pretty good guess for you, and often you don't have to do anything else. In some cases, a few tweaks are in order. Specifying column breaks is easy and the dialog box shown in Figure 3-3 actually includes instructions. Simply click at a point along the ruler just above the data preview window to set a column break. Double click a break to delete it. Or, click and drag to reposition a break. It's that simple. Click Next to proceed to the third step once your breaks are set. Figure 3-4 shows the dialog box for step 3.

Figure 3-3. Text Import Wizard: Step 2

Figure 3-4. Text Import Wizard: Step 3

The third step allows you to specify the data type, or format, of the data contained in each column you specified in the previous step. Simply click any column in the data preview window to select the column. Next, specify the data format for that column by pressing one of the "Column data format" radio buttons. To be honest, I usually skip this step and use the defaults set by the wizard. The wizard usually does a fine job recognizing text versus numbers. I find this step most useful when I want to skip a column. For example, the first column in Figure 3-4 does not contain any data; it contains only one row of text. Therefore, I select it and specify "Do not import column."

At this point you should press Finish to exit the wizard. Excel will then import the text per your specifications. The results for this example are shown in Figure 3-5.

Figure 3-5. Imported text

As you can see, Excel imported only the data I specified, placing it in columns A and B of a new spreadsheet. Now you can perform any of the usual spreadsheet operations on this data and save the resulting spreadsheet as you normally would. Before going too far, I recommend you do a Save As (File images/U2192.jpg border=0> Save As...) and save the new spreadsheet as a spreadsheet file with the .xls extension. This way, you can be sure to avoid overwriting your original text file. (Excel will warn you anyway if you attempt to overwrite it.)