1 / 72

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.

oren-brock
Télécharger la présentation

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 2013

  3. Objectives • Import tables from Access for use with a PivotTable • Manage table relationships with PowerPivot • Create a Web query • Retrieve financial data using an Office app • Access data from an XML document • Work with XML data maps New Perspectives on Microsoft Excel 2013

  4. Visual Overview:Retrieving Text Data New Perspectives on Microsoft Excel 2013

  5. Visual Overview:Retrieving Text Data New Perspectives on Microsoft Excel 2013

  6. Exploring External Data Sources • Data that is stored in locations other than the Excel workbook are known as data sources • Data source files can be stored in a variety of formats, for example: • Historical data, long-term trends (text files) • Current trend information (databases) • Up-to-the-minute data (the Internet) New Perspectives on Microsoft Excel 2013

  7. Exploring External Data Sources New Perspectives on Microsoft Excel 2013

  8. 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 • Understanding Text File Formats • Use a delimiter (space, comma, or tab) to separate columns of data • Use fixed-width text file to start each column at the same location New Perspectives on Microsoft Excel 2013

  9. Importing Data from Text Files New Perspectives on Microsoft Excel 2013

  10. Importing Data from Text Files • Starting the Text Import Wizard • Determines if data is fixed-width or delimited format and, if delimited, what delimiter is used • Steps of Text Import Wizard • On the DATA tab, click From Text in the Get External Data group • In the Import Text File dialog box, select the file and click Import; the Text Import Wizard – Step 1 of 3 opens New Perspectives on Microsoft Excel 2013

  11. Importing Data from Text Files New Perspectives on Microsoft Excel 2013

  12. Importing Data from Text Files • Specifying the Starting Row • Can specify the file type: • Delimited – characters separate each field • Fixed-width – fields are aligned in columns with spaces between each field • Can specify starting row other than first row • Indicate it data has headers • Click Next button to go to Text Import Wizard – Step 2 of 3 New Perspectives on Microsoft Excel 2013

  13. Importing Data from Text Files New Perspectives on Microsoft Excel 2013

  14. Importing Data from Text Files • Editing Column Breaks • Text Import Wizard needs to know where each column begins and ends • The point at which one column ends and another begins is the column break • Delimited format: delimiter determines column breaks • Fixed-width format: wizard guesses locations of column breaks New Perspectives on Microsoft Excel 2013

  15. Importing Data from Text Files New Perspectives on Microsoft Excel 2013

  16. Importing Data from Text Files • Formatting and Trimming Incoming Data • In Step 3 of 3, format the data in each column • General format is applied by default • To specify a format: • Select a column in the Data preview box • Click the appropriate option button in the Column Data format section • Indicate if a column should not be imported New Perspectives on Microsoft Excel 2013

  17. Importing Data from Text Files New Perspectives on Microsoft Excel 2013

  18. Importing Data from Text Files • Click Finish to import the selected data • Data can then be used to create a chart New Perspectives on Microsoft Excel 2013

  19. Exploring External Data Ranges and Connections • When data is imported into a worksheet, it is stored within an external data range • Each external data range is given a defined name based on the data source filename • Assigned name can be edited • Excel can update (refresh) data ranges and connections manually or automatically • Importing data creates a connection between the workbook and the text file New Perspectives on Microsoft Excel 2013

  20. Exploring External Data Ranges and Connections New Perspectives on Microsoft Excel 2013

  21. Exploring External Data Ranges and Connections • Excel supports other properties for external data ranges, which include: • Keep the contents of the external data range current by reimporting or refreshing the data • Require the user to enter a password before data is refreshed • Define whether the refreshed external data range retains or replaces the formatting and layout • Define whether Excel inserts or overwrites cells when new rows are added to the data range New Perspectives on Microsoft Excel 2013

  22. Exploring External Data Ranges and Connections • Viewing a Data Connection • A data connection is like a pipeline from a data source to one or more locations in the workbook • The same data connection can link multiple data ranges, PivotTables, or PivotCharts • When you import external content Excelestablishes a connection between a data range and that data source New Perspectives on Microsoft Excel 2013

  23. Exploring External Data Ranges and Connections • Viewing a Data Connection (con’t.) • To see where a data connection is being used, you can view that connection’s properties New Perspectives on Microsoft Excel 2013

  24. Exploring External Data Ranges and Connections • Modifying Data Connection Properties • You can modify data connection properties New Perspectives on Microsoft Excel 2013

  25. 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 2013

  26. Visual Overview:Excel Databases and Queries New Perspectives on Microsoft Excel 2013

  27. Visual Overview:Excel Databases and Queries New Perspectives on Microsoft Excel 2013

  28. An Introduction to Databases • A database is a structured collection of data • Databases are commonly used as the data sources for Excel workbooks • A database is divided into separate tables • Each table is arranged in columns and rows; also referred to as fields and records • A field stores information about a specific characteristic of a person, place, or thing • A record is a collection of fields New Perspectives on Microsoft Excel 2013

  29. An Introduction to Databases • Excel can retrieve data directly from most database programs New Perspectives on Microsoft Excel 2013

  30. An Introduction to Databases • Different tables are connected through database relationships; fields common to each table are used to match records in different table • A one-to-one relationship is one in which one record in a table is matched to exactly one record from a second table • A one-to-many relationship is one in which one record is matched to one or more records in a second table • Relational databases are ones in which tables can be joined through the use of common fields New Perspectives on Microsoft Excel 2013

  31. An Introduction to Databases New Perspectives on Microsoft Excel 2013

  32. Retrieving Data with Microsoft Query • Create a query to extract specific information from a database • A query contains a set of criteria that specify what values to retrieve and in what order • Queries are written in a language called SQL or Structured Query Language • Microsoft Query uses a wizard containing a collection of dialog boxes that guide you through the entire query process New Perspectives on Microsoft Excel 2013

  33. Retrieving Data with Microsoft Query • To start Microsoft Query Wizard: • Click the From Other Sources button in the Get External Data group on the DATA tab • Click From Microsoft Query New Perspectives on Microsoft Excel 2013

  34. Retrieving Data with Microsoft Query • Connecting to a Data Source • There are several types of data sources from which you can retrieve data • You can define your own data source by clicking <New Data Source> in the list of databases • Once connected to the data source, the Query Wizard – Choose Columns dialog box opens New Perspectives on Microsoft Excel 2013

  35. Retrieving Data with Microsoft Query New Perspectives on Microsoft Excel 2013

  36. Retrieving Data with Microsoft Query • Choosing Tables and Fields • Choose table and fields to include in the query New Perspectives on Microsoft Excel 2013

  37. Retrieving Data with Microsoft Query • Filtering and Sorting Data • Determine whether to retrieve all records or to filter data to retrieve only records that satisfy particular criteria New Perspectives on Microsoft Excel 2013

  38. Retrieving Data with Microsoft Query • Filtering and Sorting Data (con’t) • Specify how to sort the data by values in fields New Perspectives on Microsoft Excel 2013

  39. Retrieving Data with Microsoft Query • The Query Wizard – Finish dialog box provides three options: • Return (import) the data into the Excel workbook • Display the results of the query in Microsoft Query • Save the query to a file New Perspectives on Microsoft Excel 2013

  40. Retrieving Data with Microsoft Query • Editing a Query • Add new columns to a worksheet • Change the sort order options • Revise any filters • Edit a query by editing the definition of the connection; Query Wizard will restart • Modify the query definition as you walk through the steps of the wizard New Perspectives on Microsoft Excel 2013

  41. Importing Data from Multiple Tables into a PivotTable • Import multiple tables from a database directly into Excel without using Microsoft Query • If tables are related through a common field, Excel will automatically include the relationship • The entire table will be imported because the query is not defined New Perspectives on Microsoft Excel 2013

  42. Importing Data from Multiple Tables into a PivotTable New Perspectives on Microsoft Excel 2013

  43. Importing Data from Multiple Table Into a PivotTable • To import a table: • Click the From Access button in the Get External Data group on the DATA tab • Select from the Select Data Source dialog box New Perspectives on Microsoft Excel 2013

  44. Importing Data from Multiple Table Into a PivotTable • Set up and format the PivotTable and slicer New Perspectives on Microsoft Excel 2013

  45. Exploring the Data Model and PowerPivot • The data model is a database built into Excel that provides database tools • Data model database contents are immediately available to PivotTables, PivotCharts, and other Excel features • The data model is constructed from different tables related by common fields • Installing the PowerPivot Add-In • Interact with the data model using PowerPivot – an add-in for Excel 2013 that provides tools for performing advanced data analysis and modeling New Perspectives on Microsoft Excel 2013

  46. Exploring the Data Model and PowerPivot • Installing the PowerPivotAdd-In (con’t) • With PowerPivot, you can: • Apply filters to tables stored in the data model • Rename tables and fields within the data model • Define and manage the relationships among data tables joined by common fields • Format data values that will be used in PivotTables and other Excel reports • Create calculated fields based on data fields from multiple data sources • Create advanced data structures and models New Perspectives on Microsoft Excel 2013

  47. Exploring the Data Model and PowerPivot • Adding a Table to the Data Model • The POWERPIVOT tab contains the commands from working with the data model • Use the Add to Data Model command to add Excel tables to the data model • PowerPivot displays the contents of the data model in a separate window from the Excel workbook window New Perspectives on Microsoft Excel 2013

  48. Exploring the Data Model and PowerPivot New Perspectives on Microsoft Excel 2013

  49. Exploring the Data Model and PowerPivot • Viewing the Data Model • View the contents of a data model in Data view and in Diagram view • Data view shows the contents of each database table in the data model on a separate tab • Diagram view shows each table as an icon and relationships between tables are indicated by connecting arrows • To see which field joins two tables, click the arrow connecting the tables New Perspectives on Microsoft Excel 2013

  50. Exploring the Data Model and PowerPivot New Perspectives on Microsoft Excel 2013

More Related