Excel Chapter 3

1. Excel Chapter 3

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

3. 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

4. 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.

5. 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.

6. 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

7. To import data from a text file: • Start Excel, click the File tab, and then click Open. • Click the File Type arrow that currently displays All Excel Files, and then select Text Files. • Navigate to the folder that contains the text file, select it, and then click Open. • If the file is a .csv file, Excel automatically converts the file and opens it. • 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.

9. 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 PivotTable Report • As a PivotChart • When importing an Access database into Excel, maintain a live connection to the data

10. Import data from access

11. 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

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

13. 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

14. 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

15. Example

16. To open an XML file • Display the Open dialog box. • Click the File Type arrow, and then select XML Files. • Select the XML file you want, and then click Open. • Select the option you want to open the file, and then click OK.

17. To link an XML file: • Click the Data tab, click From Other Sources in the Get External Data group, and then select From XML Data Import. • Select the XML document you want to import in the Select Data Source dialog box, and then click Open. • Select the desired options in the Import Data dialog box, and then click OK.

18. Example • Import a xml file from CS101 website to excel.

19. Homework#1 is posted in CS101 website. • It is due on January 31st, 2014