Importing from Text Files

There are three basic scenarios when reading from text files. If the file contains less than 65,536 records, it is easy to import the file using the Workbooks.OpenText method. If the file contains more than 65,536 records and less than 98,304 records, then it is possible to open the text file using two calls to Workbooks.OpenText. If the file contains more than 98,304, then you have to read the file one record at a time.

Importing Text Files with Less Than 65,536 Rows

Text files typically come in one of two formats. In one format, the fields in each record are separated by some delimiter, such as a comma, pipe, or tab. In the second format, each field takes a particular number of character positions. This is called a fixed-width file and was very popular in the days of COBOL.

Excel can import either type of file easily. You can open both types using the OpenText method. In both cases, it is best to record the process of opening the file and use the recorded snippet of code.

Importing from Text Files 387

Writing Text Files 398

Next Steps 399

Opening a Fixed-Width File

Figure 18.1 shows a text file where each field takes up a certain amount of space in the record. Writing the code to open this type of file is slightly arduous, because you need to specify the length of each field. In my antique drawer, I still have the metal ruler used by COBOL programmers to measure the number of characters in a field printed on a greenbar printer. You could, in theory, change the font of your file to a monospace font and use this same method. However, using the macro recorder is a slightly more up-to-date method.

Figure 18.1

This file is space-delimited or fixed width. Opening this file is fairly involved, as you need to specify the exact length of each field in the file.

E' sdlcs.prn - Notepad

fit, Edt

Format View

Kefp

0 0

Post a comment