EXCEL CHAPTER 3 OBJECTIVES Import data from external

EXCEL CHAPTER 3

OBJECTIVES • • • Import data from external sources Create a Web query Manage connections Convert text to columns Import XML data into Excel 2

IMPORT DATA FROM EXTERNAL SOURCES • Importing is the process of inserting data from another application • Two common file types imported into Excel: – Text files – Access database files • Two ways to import data into Excel: - Embed data - Link data 3

EMBED DATA • Insert directly by opening the file using the Open dialog box. • When you import, but do not want to maintain a link. • When you want to edit the data within Excel. 4

LINK DATA • By creating a link to the original data source. • When you want to create a link. • When you want imported data updated if changes are made to the original data source. 5

IMPORT FROM TEXT FILES • Text file: –. txt extension – Comprised of ordinary characters such as letters, numbers, and symbols – Imports easily • Comma-separated value (CSV) files: – Commas separate columns – Newline character separates rows 6

TO IMPORT DATA FROM A TEXT FILE: 1. Start Excel, click the File tab, and then click Open. 2. Click the File Type arrow that currently displays All Excel Files, and then select Text Files. 3. Navigate to the folder that contains the text file, select it, and then click Open. a. If the file is a. csv file, Excel automatically converts the file and opens it. b. If the file is a. txt file, the Text Import Wizard appears, prompting you for information about the external data and then converts the data into an Excel workbook. 7

8

IMPORT DATA FROM EXTERNAL SOURCES • Often necessary to import an Access database into Excel to analyze the data in more detail • Access databases may be imported in three ways: – As a table – As a Pivot. Table Report – As a Pivot. Chart • When importing an Access database into Excel, maintain a live connection to the data 9

IMPORT DATA FROM ACCESS 10

CREATE A WEB QUERY • A Web query enables Excel to go to a particular Web site to obtain information • Web queries let you extract data from tables on a Web page • If the Web address changes, you must change the Web query and URL to ensure you have the most accurate information and citation • If you have to log in to the Web site, the query generally will not work 11

EXAMPLE • Please import the list of 50 states in USA from the website http: //state. 1 keydata. com/ 12

MANAGE CONNECTIONS • When you import data using the options in the Get External Data group, Excel creates a link to the original data source • Refresh connections periodically to ensure you have the most up-to-date and accurate information • View or remove existing connections through the Workbook Connections dialog box 13

CONVERT TEXT TO COLUMNS • If data is displayed in one column, use the Text to Columns command to separate the data into multiple columns • Convert Text to Columns Wizard guides you through the process of separating data and choosing a delimiting character 14

EXAMPLE 15

TO OPEN AN XML FILE 1. Display the Open dialog box. 2. Click the File Type arrow, and then select XML Files. 3. Select the XML file you want, and then click Open. 4. Select the option you want to open the file, and then click OK. 16

TO LINK AN XML FILE: 1. Click the Data tab, click From Other Sources in the Get External Data group, and then select From XML Data Import. 2. Select the XML document you want to import in the Select Data Source dialog box, and then click Open. 3. Select the desired options in the Import Data dialog box, and then click OK. 17

EXAMPLE • Import a xml file from CS 101 website to excel. 18

• Homework#1 is posted in CS 101 website. • It is due on September 14, 2012. 19