1 / 51

Tutorial 11: Connecting to External Data

Tutorial 11: Connecting to External Data. Objectives. Import data from a text file Work with connections and external data ranges Define a trusted location Understand databases and queries Use the Query Wizard to import data from several tables Edit a query. Objectives.

ellery
Download Presentation

Tutorial 11: Connecting to External Data

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. Tutorial 11: Connecting to External Data

  2. Objectives Import data from a text file Work with connections and external data ranges Define a trusted location Understand databases and queries Use the Query Wizard to import data from several tables Edit a query New Perspectives on Microsoft Excel 2010

  3. Objectives • Import data into a PivotTable and PivotChart • Create a Web query • Retrieve data from the Web • Use hyperlinks in a workbook • Access data from an XML document • Work with XML data maps New Perspectives on Microsoft Excel 2010

  4. Visual Overview New Perspectives on Microsoft Excel 2010

  5. Retrieving Text Data New Perspectives on Microsoft Excel 2010

  6. Exploring External Data Sources • Historical data, long-term trends (text files) • Current trend information (databases) • Up-to-the-minute data (the Internet) New Perspectives on Microsoft Excel 2010

  7. Importing Data from Text Files • Text files • Simple, widely used format for storing raw, unformatted data (text and numbers) • Useful for sharing data across software programs and computer systems • Ways to structure file contents • Use a delimiter • Columns of data are separated by a symbol • Use fixed-width text file • Each column starts at the same location New Perspectives on Microsoft Excel 2010

  8. Delimited text Fixed-width text Understanding Text File Formats New Perspectives on Microsoft Excel 2010

  9. Importing Data from Text Files • Text Import Wizard determines whether data is fixed-width or delimited format • Steps of Text Import Wizard • Start the wizard • Specify starting row; edit column breaks • Delimited format: delimiter determines column breaks • Fixed-width format: wizard guesses locations of column breaks • Format data in each column New Perspectives on Microsoft Excel 2010

  10. Starting the Text Import Wizard New Perspectives on Microsoft Excel 2010

  11. Specifying the Starting Row New Perspectives on Microsoft Excel 2010

  12. Formatting and Trimming Incoming Data New Perspectives on Microsoft Excel 2010

  13. Exploring Connections • Defined process of retrieving data from an external file • Importing data creates a connection between the workbook and the text file New Perspectives on Microsoft Excel 2010

  14. Exploring Data Ranges External data range Each location in which a connection is applied Each external data range is given a name, which can be edited Excel can update (refresh) data ranges and connections manually or automatically New Perspectives on Microsoft Excel 2010

  15. Exploring Data Ranges New Perspectives on Microsoft Excel 2010

  16. Defining a Trusted Location • Once a trusted location is defined, Excel will access the connection to the data source without prompting for confirmation that the connection is secure New Perspectives on Microsoft Excel 2010

  17. Visual Overview New Perspectives on Microsoft Excel 2010

  18. Excel Databases and Queries New Perspectives on Microsoft Excel 2010

  19. Introducing Databases • Excel can retrieve data directly from most database programs • A common field can match information from two tables into a single table New Perspectives on Microsoft Excel 2010

  20. Introducing Queries • Create a query to look at only specific information from a database • A query can: • Contain criteria (conditions that limit the number of records in the results) • Specify how you want the data to appear New Perspectives on Microsoft Excel 2010

  21. Using Microsoft Query • Excel supports two ways of importing database data • Create a connection to the database and retrieve all the information from a single table • Use Microsoft Query to retrieve data from multiple tables New Perspectives on Microsoft Excel 2010

  22. Steps in the Microsoft Query Wizard • Create a data source • Choose table and fields (columns) to include • Determine whether to retrieve all records or to filter data to retrieve only records that satisfy particular criteria • Specify how to sort the data New Perspectives on Microsoft Excel 2010

  23. Creating a Data Source • Choose Data Source dialog box New Perspectives on Microsoft Excel 2010

  24. Choose Table and Fields (Columns) New Perspectives on Microsoft Excel 2010

  25. Choose Table and Fields (Columns) New Perspectives on Microsoft Excel 2010

  26. Filtering and Sorting Data • Filter Data dialog box • Sort Order dialog box New Perspectives on Microsoft Excel 2010

  27. Saving a Query • Can be run later without redefining it • Accessible to other network users • Appears on Queries tab of Choose Data Source dialog box (allows quick access) • Save to trusted locations so that Excel can update queries when workbook is opened New Perspectives on Microsoft Excel 2010

  28. Importing Data from Microsoft Query • Query Wizard – Finish dialog box options • Return (import) data into the Excel workbook • Display results in Microsoft Query for further editing and query definition • Import data in an Excel table, a PivotTable, a PivotTable and PivotChart, or create the connection without importing the data New Perspectives on Microsoft Excel 2010

  29. Importing Data from Microsoft Query • Formatted Portfolio worksheet New Perspectives on Microsoft Excel 2010

  30. Importing Data from Microsoft Query • Connection Properties dialog box New Perspectives on Microsoft Excel 2010

  31. Editing a Query • Edit a query to: • Add new columns to the worksheet • Change sort order options • Specify a filter • Edit a query by editing the definition of the connection • Excel recognizes that Query Wizard was used to define parameters of the connection and restarts the wizard New Perspectives on Microsoft Excel 2010

  32. Editing a Query New Perspectives on Microsoft Excel 2010

  33. Editing a Query • Revised portfolio table New Perspectives on Microsoft Excel 2010

  34. Importing Data into PivotTables andPivotCharts Can select and display values from only one area at a time, adding only a single table and chart to the workbook Data used in the table and chart can be stored in an external data source Initial table and chart are empty until you define where to place fields from data source New Perspectives on Microsoft Excel 2010

  35. Importing Data into PivotTables andPivotCharts • PivotTable and PivotChart added to worksheet New Perspectives on Microsoft Excel 2010

  36. Importing Data into PivotTables • Can set PivotTable layout, change labels, and format data values New Perspectives on Microsoft Excel 2010

  37. Importing Data into PivotCharts • Can format PivotCharts New Perspectives on Microsoft Excel 2010

  38. Visual Overview New Perspectives on Microsoft Excel 2010

  39. Web and XML Connections New Perspectives on Microsoft Excel 2010

  40. Creating a Web Query • Specify the URL and use Excel Web Query to select portions of the Web page to import • URL form if file is stored on a Web server • URL form if file is stored locally (not on Web server) New Perspectives on Microsoft Excel 2010

  41. Creating a Web Query • New Web Query dialog box New Perspectives on Microsoft Excel 2010

  42. Creating a Web Query • Text is imported into worksheet as unformatted text New Perspectives on Microsoft Excel 2010

  43. Creating a Web Query • To format a Web query, retrieve Web page format using: • None (default; imports text but not formatting) • Rich text formatting only • Full HTML formatting • To save a Web query • Save to a permanent file that is shared • Saved connection file can be loaded in any Office program New Perspectives on Microsoft Excel 2010

  44. Importing Data from the Web • Use a Web query to retrieve online data and import it into a workbook • Excel has several Web query files stored in a library of built-in Web queries • Some include parameters for specifying exact information to be imported New Perspectives on Microsoft Excel 2010

  45. Importing Data from XML • Office 2010 files are stored in an XML-based file format called Office Open XML format • An XML document is structured like a tree: elements are placed within one another, descending from a common root element New Perspectives on Microsoft Excel 2010

  46. Importing Data from XML • Data map of the Data.xml document New Perspectives on Microsoft Excel 2010

  47. Importing Data from XML • XML author can: • Define what makes up a valid document (which elements are required, which are optional) • Indicate the type of values each element can contain • Schemas, though not required in XML, ensure that data inserted into an XML document follows predefined rules for content and structure New Perspectives on Microsoft Excel 2010

  48. Loading an XML Data Map • Commands to access data map of an XML document are part of Excel Developer tools • Show the Developer tab on the Ribbon New Perspectives on Microsoft Excel 2010

  49. Binding XML Elements to a Worksheet • Using XML as a data source: • Allows you to attach (bind) elements to specific cells in the workbook • Provides greater freedom in designing worksheet layout New Perspectives on Microsoft Excel 2010

  50. Importing XML Data • To retrieve XML data, refresh the connection to the data source • Excel automatically places data in the correct worksheet cells • Can automatically update XML data in a workbook to reflect changes to source XML document • Can use a data map to export data from a worksheet to an XML document New Perspectives on Microsoft Excel 2010

More Related