O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

3.1. Importing Data from Text Files

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 → 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 → 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.

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.

Tip

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.[*]

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required