1 / 19

Excel Chapter 3

Excel Chapter 3. objectives. Import data from external sources Create a Web query Manage connections Convert text to columns Import XML data into Excel. Import Data From External Sources. Importing is the process of inserting data from another application

fathia
Download Presentation

Excel Chapter 3

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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.

  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 PivotTable Report • As a PivotChart • 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.1keydata.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 • 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.

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

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

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

More Related