Importing Text Data onto a Worksheet

Similar to how you opened a text file in the previous section, you can also import text into an existing worksheet. This process involves many of the steps that you go through to open a text file directly. However, importing data onto an existing worksheet provides you with an added capability that you don't have when you open a text file directly. When you import text data onto an existing worksheet, the range that contains the imported text data is considered a data range. Among other things, you can instruct a data range to refresh itself automatically at specific intervals or whenever you open the workbook; and you can do this without repeating all of the normal steps associated with opening or importing text files.

To demonstrate this process, I'll import the data shown in Figure 15.1 into a worksheet.

1. Select Data ^ Import External Data ^ Import Data____

2. Change the Files of Type drop-down list to Text Files, locate the text file to import, and click Open.

3. Perform the three steps of the Import Text Wizard (see Figures 15.3, 15.4, and 15.5).

4. Select a location for the data to be imported as shown in the following screen shot and click OK.

Impart Data

®

Where do you want to put the data?

1 OK 1

(*) Existing worksheet:

1 Cancel |

=5heetl!$A$i|

O Slew worksheet

Crcotc j r v-jT jj 1 •;• repj-t...

[Properties... | Parameters,.

Edit Query...

Figure 15.8 shows an example of text data that has been imported into a workbook. One of the first things you will notice after importing the data (assuming you are using all of the default option settings) is that a new toolbar appears—the External Data toolbar. The appearance of this toolbar is a sign that the data you just imported is associated with a data range.

Figure 15.7

Opening a fixed-width file—step 2

Figure 15.7

Opening a fixed-width file—step 2

Figure 15.8

When you import a text file, the data is associated with a data range.

Figure 15.8

When you import a text file, the data is associated with a data range.

□ Microsoft Excel - Book2

BE®

ni]

Flic Edt View insert Fermât Tods Datî

1 Window tfefc

Type a question for help

• . 9 X

: J

-J. ->. ia ;

2 -s.| §3 1 'RBi.lv will Ctlfriee:

ö i E •1 Hi

juy

E13

f'

A

B

c !

0 1

E

1 F

S

H

1

J T

1

Symbol

Date

Open Hljli

Low

Last

Volume

□ pen Iriteresl

2

MWZ03

6/3/2002

349.5

354

349

354

7353

23342

3

KWZ03

G/4/2CG2

352'

352'

351

351

3952

23549

4

MWZ03

6/5/2002

351

351

348

349

3654

23263

5

MWZ63

6/6/2002

0

hMlemal

* X

14

23147

6

MWZD3

6/7/2002

0

\3 Jf

■ !

Eft

11

23233

7

MW2B3

6/10(2002

333

1-T7TT

-33T

-Tt

be

21274

3

MWZ03

0

339

339

333

3064

21642

9

lrfWZ03

6/12/20G2

340

340

337

336.5

4216

21267

10

MWZ63

6/13/2002

337.5

336

336

338

4651

21636

11

MWZ03

6/14(2002

331 '

331

326

326

4634

22264

12

KÎWHJ3

6/17(2002

329

330

323

330

2437

22034

13

MWZ03

6/16(2002

333

3331

I 329

I 330

4527

22566

V

Ii <

t WiShuetl (Sires? /Sheeny

J<

m I

>r

1 Ready

A data range has a number of capabilities associated with it. To get an idea of these capabilities, look at Figure 15.9. One thing to point out is the name of the data range. As part of the process of importing data, Excel creates a named range that is equivalent to the name ofthe file imported. Well, almost equivalent. The named range has underscores where the spaces are. For example, the data range in Figure 15.9 is "wheat futures data" while the named range that refers to the data is named "wheat_futures_data".

Another useful capability of a data range is that you can set it up to automatically refresh the data. This is useful in many different scenarios. For example, many servers and applications produce log files that change frequently. If you were importing this data into Excel, you could set the data range to refresh itself automatically every few minutes.

Figure 15.9

Data ranges offer a number of useful capabilities.

External Data Range Properties

Name: ! wheat future ctata

Query dohrition

0 Save ot>ery definition ■ Save password Refresh :ortrd 0 Prcfrpl icr file narnB on fcFresh

□ itafresh dsla on frie open jr Ramove external data Frtift wor+shcet before saving Data Formatting and layout

F Indudc field flames C Waserve column sort/flftor/layout 0 fVeserve ce« fomiattlno

0 Adjust column bvidth

IF the nucber oF rows n the data lange chenges upon refresh: © Insert cells for new data, delete mused ccts O Insert entire rug For data, deaf unused tells O Overwrite easting cells with new data, deaf unused cells |_| f d down Formulas in columns adjacent to data

TIP f you are working with a large text file, you can reduce the size of your Excel file by selecting the Refresh Data on File Open and Remove External Data from Worksheet Before Saving options from within the External Data Range Properties dialog box. When you do this, the data from the text file is not saved with the Excel file; rather, it is auto matically imported every time you open the file.

0 0

Post a comment