Versatile Simplicity

The simple, yet versatile, text file. You have probably opened countless text files. They come in all shapes and sizes and are used for everything—instructions, notes (such as readme.txt files), application configuration, and last but not least, storing data.

Although flexibility is one of the strengths of a text file as a means of storing data, text files are generally structured like a list in Excel or a database table where each row in the list represents one item or record. The question is, how do you specify where one field ends in a given record and where the next begins? Without knowing the answer to this question, applications such as Excel have no way of parsing the file or breaking each record down into individual fields.

Parsing the data is the process of extracting the field information from each record. Prior to being parsed, each record looks like a single chunk of data. You have probably seen this occasionally when you are trying to copy data into Excel and the whole chunk ends up stuffed into a single cell or column of cells. After parsing the data, each record consists of a number of distinct fields or columns.

You can use one of two methods to separate fields in a text file. One method is to place a delimiter between each field. A delimiter is a token character that denotes the end of a field; for example, each field may be separated by a comma or a tab. An example of a delimited file is shown in Figure 15.1.

Figure 15.1

A comma-delimited text file fi wheat future data.txt - Notepad

Fife Edt FcrmaS Vew Help symbol,Date,open,High,Lew,Last,volume,open Interest IWZ03, 6/3/2002,346. 5,3 54,349,3 54,73 53,23 34 2 MW203,6/4/2002,352.152,351,351,3952,23549 MWZ 03,6/5/2002,3 51,3 51,349,349,3354,23283 MWZ03,6/6/2002,0,347,347,347,3614,23147 MWZ03,6/7/2002,0.33®.339,329,3911,23299

MWZ03,6/10/2 002,333,339,338,339,2036,212 74

MWZ03,6/11/2002,0,339,339,339,3064,2104 2

MWZ03,6/12/2002,340,340,337,333-5,4216,21287

MWZ03,6/13/2 002,33 7.5,338,336,333,48 51,2183 6

MWZ03,6/14/2002,331,331,326,326,4334,22284

MWZ03,6/17/2 002, 329,330,329,330,24 37,22034

MWZ03,6/18/2002,333.333,329.330.4527.22568

MWZ03,6/19/2002, 32 5. 5, 325. 5, 325. 5, 325. 5, 5346, 22837

MWZ03,6/20/2 002,0,326,326,326,1105 5,23727

MWZ03.6/24/2 002,0,329,329,329,7411,22195

MWZ03,6/25/2002,0,334,334,334,7006,21064

MW203,6/26/2002, 0, 339, 5,339. 5,339. 5,9374,20252

MWZ03,6/2 7/2002,340,347,340,347,10571,20 5 3 9

MWZ03,6/28/2002,343,344,341,344,3492,21623

MWZ03,7/3/2002,34 5,347. 5,343,347. 5, 52 50,2 2 93 6

MWZ03,7/5/2002,318,3 53.5,343,353.5,3375,23 76!

MWZ03,7/8/2002, 0,352. 5, 352. 5. 352. 5,4026, 22650

MWZ03,7/9/2002,351,352,351,352,5653,22317

MWZ03,7/10/2 002,3 51,3 54.5,351,354.5,3561,22374

MW203.7A1/2002,0, 3 55.355.355.6300.22616

Figure 15.2

A fixed-width text file

Fils Ed£ Format Vbp

Help

order#-salesperson—

-Date------

-Product-

--Amount -

1 Hansen

1O/1O/2003

XL-DLX

(155

50

2 Johnson

10/10/2003

XL-DLX

tl 5 5

50

3 Madison

10/11/2003

MT-FftS

$275

75

4 smith

10/12/2003

MT-FOD

(265

00

5 Thomas

10/14/2003

DD-FRS

1115

55

6 nones

10/14/2003

DD-DLX

$23 5

50

Delimited files are the most efficient way of storing data from a storage space standpoint because you do not need to pad fields with extra spaces as you need to do with the fixed-width method described next. When viewed in a text editor such as Notepad, however, delimited files do not look very attractive.

The second method is to assume that each field has a set amount of characters. This kind of file is referred to as a fixed-width file. The number of spaces allocated to each field is specified by the application that creates the file. For example, a field that contains last names may have 35 characters allocated to it. Any unused space in a field is filled with blank spaces, as if you pressed the spacebar until all 35 characters were used. So the last name "Davis" would have five characters for the actual name and 30 space characters. An example of a fixed-width text file is shown in Figure 15.2.

Fixed-width files have the advantage of being easier to read when viewed in a text editor such as Notepad. On the other hand, they take up more storage space and are not quite as easy to work with.

The mechanics of importing a text file differ slightly depending on the method you use to separate fields in a text file. You can incorporate data from a text file into Excel in three ways. In the next section,

I'll cover the most common way—opening the text file using File ^ Open. You can also import text data or copy/paste text data onto a worksheet. These two methods will be covered later in this chapter.

Was this article helpful?

0 0

Post a comment