Figure

Opening a delimited file—step 3

Te*t Import Wizard - Step 3 of 3

This screen lets you select each column and set the Data Format.

'General' converts numeric values to numbers, date vctfues to dates* and all remaining values to te*t.

Column data format O General Olext

Symbol TOS OS 15203 rrr-i: :-: TIT203

ffigjjifj

□pen 343. S 352 351

ligh 354 3 52 3S1 347

jow 349 3-51 349 347

Lasc 354 3 SI 349 347

[To lune 7353 3952 33 54 3614

Dp en interesc

Î3147

Figure 15.6

An imported text file in Excel

iii]

File Edt

Vieiw insert Fermât

Tools Data

Winrfcw

Type a question for help

. 3 X

1 -j

a u st -1 :

¿1 [¡[J 1 P.'.■[.! ^ '. 'l'.l 1 Jll j ¡'J'.".. .

b ; E -1 sa - I

lidä

A1

»

fa Symbol

A

I B

c

D .

E 1

F

e

H

I -

1

ISvmbol I

bate

1 ippr

High Low

Last

Volume

□ pen Iriteresl

2

MWZ03

B3/2002

349.5

m

349

354

7353

23342

3

KWZ03

E'4/2002

352

352

351

351

3052

2354S

4

MWZ03

55/2002

351

351

349

349

3BS4

23283

5

MWZ03

&B/2W2

0

"347

347

347

3614

23147

6

MWZ03

&7/2002

0

339

339

339

3911

23299

7

MWID3

6/10/2002

330

339

333

339

2036

21274

3

MWZ03

511/2002

0

339

339

339

3064

21042

9

lrfWZ03

6/12/2D02

340

340

337

338.5

4216

21287

10

MWZ03

6/13/2002

337.5

338

338

330

4651

21836

11

MWZ03

5/14/2002

331

331 r

323

326

4634

22284

12

MWZ03

6/17/2002

325 '

33q

329'

330

2137

22034

13

MWZ03

6/1S/2002

333

a 33

329

330

4527

2X68

Ii <

- M \wheat Future data/

l<

h 1

1 Ready

The final step is to identify the data format of each field as shown in Figure 15.5. This step is particularly important when you have fields containing numeric data that should be treated as text. For example, maybe your company uses a numeric code to identify products and one particular product line uses three leading zeros as part of its product code (e.g., 0005700). If you leave the column's data format set at general, when you import the data into Excel, the leading zeroes will be stripped off (e.g., 5700). In order to prevent this, select the column and change the column data format to Text.

In Figure 15.5, I have changed the column data format to Date for the field containing dates. Figure 15.6 shows the text data that results after I click Finish.

The main difference when opening a fixed-width file is that you have to specify where one field ends and the next begins. To demonstrate, I will use the file shown in Figure 15.2. To open a fixed-width text file, you begin the same way as with a delimited text file—by selecting File ^ Open in Excel and then changing the Files of Text drop-down box to Text Files (*prn., *.txt, *.csv). In step 1 of the Import Text Wizard (Figure 15.3) select fixed-width rather than delimited. It is not until step 2 (Figure 15.7) that things look a bit different. Because Excel has no way of knowing where one column ends and the next begins, it is up to you to place markers between the columns. Excel refers to these markers as break lines. Click once at the desired location to place a break line. Double-click a break line to remove it. Finally, you can move a break line by dragging and dropping it.

After you have set all your break lines, you will find yourself back in familiar territory. The remainder of the process is the same as it was with a delimited file.

0 0

Post a comment