Opening Text Files in Excel

Even if you are already familiar with the mechanics of opening a text file in Excel, it is a good idea to refresh your knowledge here because the process of opening these files programmatically depends on your familiarity with the manual process.

I will demonstrate the mechanics of opening up a delimited file using the file shown in Figure 15.1. For any text file, you begin the process by selecting File ^ Open in Excel and then you change the Files of Text drop-down box to Text Files (*prn., *.txt, *.csv) as shown in the following graphic.

Look in:

lJ Chapter 15

v X

_J * Tools '

□1

Warne A

Size Type

Date Modified

i yff My Recent Documents

17 KB Text Document

10/24/2003 1:07 PM

Desktop

ê My Documents

My Computer

My Network Places

Filename:

v 1 Open -I

My Site

Files of tips: Tea Files [*.pm;

v 1 Cancel |

Once you pick a file and click Open, the first step of the Text Import Wizard is displayed. This step looks the same whether the file is delimited or fixed-width.

Figure 15.3

Opening a delimited file—step 1

Text Import Wizard - Step 1 of 3

The Text Wzard has determined that your data is DeSmited,

If this is correct, choose ftext, or choose the data type that best descrbes your data.

Origjnai data type

Choose the fie type that best describes your data: QiB^imifedl - Characters such as commas or tabs separate each field, O Fixed width - Feads are aligned in returns with spaces between each field,

Start import at row:

0 Fie origin:

437 : OEM United States

Preview of file C:\Documents and Settingslshansen\My Oocun.-Awheat Futire data.txt.

Symbol,Date,Open,High,Low,LastrVolume,Open Interest ÏWZ03,6/3/2002,349.S,354,349,354,7353,23342 TKZ03,6/4/2002,352,352,351,351,3952,23549 4.W203,6/5/2002,351, 351, 349, 349, 3354,23283 TVZQ3,6/6/2002,0,347,347,347,3614,23147

Usually you can tell by looking at the preview in the bottom half of Figure 15.3 whether the file's type is delimited or fixed-width. This can be tricky with tab-delimited files, however, as they can look a bit like a fixed-width file. If in doubt, you can always select delimited and move on to the next step. If you select the tab delimiter and the file does not get parsed (the field separation lines do not appear), then go back to step 1 and choose fixed-width. Most of the time you don't need to do anything, however, because Excel determines which type of file you are opening and makes the correct choice to begin with. The file in Figure 15.1 is a comma-delimited text file.

NOTE CSV stands for comma-separated values. Files with a .csv extension are comma-delimited text files. Excel automatically opens files with a .csv extension without requiring the use of the Text Import Wizard.

TIP You can bypass the Text Import Wizard when opening text files by holding down the Shift key when you open the file. This is helpful for tab-delimited files that will be parsed by default.

Notice that you can specify which row to begin importing. This allows you to skip any header rows if you do not want them in the resulting worksheet. Regarding the file origin, most of the time you never need to change this. If the text file was generated on a different platform (operating system), however, you may need to make an adjustment here. The reason for this is that different operating systems have different conventions for representing the line-feed character. This is significant because the line-feed character signifies the end of a record. You'll know when you haven't set this correctly because Excel interprets the entire contents of the file as a single record.

The main task in step 2 (shown in Figure 15.4) is to specify the character that is used to delimit the file. When you check a delimiter, Excel attempts to parse the file, and then shows the results in the data preview. Excel places vertical lines in the preview window to indicate how the data will be lined up in columns. You can also tell Excel how to handle consecutive delimiters and indicate how text is qualified. For example, if your file is space delimited and one of the fields contained text data, you would not want Excel to interpret the spaces in the text field as delimiters. To prevent this, the system that created your text file usually wraps text data in a qualifier such as single or double quotes.

Figure 15.4

Opening a delimited file—step 2

Te*t Import Wizard - Step 2 of 3

This screen lets you sat the delimiters your data contains. You can si how your text is affected m the preview below,

Delimiters

Data preview

Q Treat consecutive delimiters as on© Text qualifier;

Data preview

ïynbol

>ace

Dpan

Ii gl j

Low

Läse

Uoluiie

Dp en interesc

IUZ03

5/3/2002

343. 5

354

349

354

73S3

'3342

IUS03

5/4/2002

352

3S2

¡351

3 SI

39S2

23549

tUEOS

S/S/2002

3S1

3S1

349

349

33 54

£3233

TU203

Î/Ê/2002

J

34?

347

347

3514

Î3147

0 0

Post a comment