Importing External Data

To analyze data with Excel, you must first provide the data in a format that Excel recognizes. You can do this in two ways: you can open a data source in a format that Excel knows how to work with, or you can import the data into an Excel worksheet.

Tip

When you import data into Excel, you generally are creating a copy of the data, separate from the original data source. As a rule of thumb, if you want to see updates to the original data, you should open the data in Excel instead of importing the data into Excel.

The types of data that you can open in Excel (other than Excel files) include

• Web pages

• XML files (Excel 2002 only)

• Text files

• Microsoft Access database files

• Lotus 1-2-3 files

• Quattro Pro/DOS files

• Microsoft Works 2.0 files

• dBase files

• Any other ODBC or OLE DB source for which you have a corresponding driver (data source connection software), including Microsoft SQL Server 2000 databases and Microsoft SQL Server 2000 Analysis Server databases.

  Note

  The following procedure does not work in Excel 2000. You can open a file or import data from a text file in Excel 2000, but you cannot import data from formats other than text files.

To import external data into a spreadsheet:

1. Click the cell in which you want the first item of the external data to appear.

2. On the Data menu, point to Import External Data and then click Import Data. The Select Data Source dialog box appears.

3. If you know that the data file or data source connection file already exists, locate and click the file, click Open, and then follow the directions Excel provides to finish importing the data. For example, if your data source is an Access database, you are asked to select a specific data table or data view.

   To create a new data source connection file, click the New Source button. The Data Connection Wizard appears and displays the Welcome To The Data Connection Wizard page.

4. On the wizard’s first screen, select a data source and then click Next.

5. Depending on the type of data source you want to connect to, follow the steps to specify connection properties. For example, if you are connecting to a Microsoft SQL Server 2000 database, you are asked to provide a database logon name and password, a database name, and a data table or data view name.

6. After you have specified the connection properties, click Finish. The Select Data Source dialog box reappears.

7. Click the data source connection file, click Open, and then follow the on-screen directions to finish importing the data.

Filtering Imported Data

Excel allows some types of data sources to be filtered. To filter imported data after you import the data, point to Import External Data on the Data menu and then click Edit Query. One of the Query Wizard screens appears; which one depends on your specific data source. Figure 3-14 shows an example of the Choose Columns page.

3-14 : The Query Wizard - Choose Columns page.

After you import data into Excel, you can begin analyzing this data as you would analyze data on any other Excel worksheet. Note that this data is a copy of the external data, and therefore changes cannot be made to the external data source from the Excel worksheet.